How do I re-use query results?

[eluser]CJL01[/eluser]
I'm trying to take one db query result set and use it to both display as a table, and to draw graphs from. I've created my own custom datatable library to format up query results into a table as I need them. I've also created a graph library which takes a query result and formats it into a Google chart URL for display. The first argument in my new_line_chart() function is the column of the $query result set that should be graphed.

Code:

$query = $this->new_model->daily_summary();
        $table = $this->datatable->generate($query);
        $data['output'] = '<h3>Daily Summary</h3>'.$table;

        $graph = $this->graph->new_line_chart(1, 'N', $query);
        $data['output'] .= '<br />'.$graph;

        $graph = $this->graph->new_line_chart(4, 'N', $query);
        $data['output'] .= '<br />'.$graph;
        $query->free_result();

I've tested these libraries and functions to ensure they all work, but the problem is that I can't seem to re-use the $query result set from my first query to feed the graph - it seems to have gone out of scope by the time I use it for the graphs. If I re-query each time then it works perfectly;

Code:

$query = $this->new_model->daily_summary();
        $table = $this->datatable->generate($query);
        $data['output'] = '<h3>Daily Summary</h3>'.$table;

        $query = $this->new_model->daily_summary();
        $graph = $this->graph->new_line_chart(1, 'N', $query);
        $data['output'] .= '<br />'.$graph;

        $query = $this->new_model->daily_summary();
        $graph = $this->graph->new_line_chart(4, 'N', $query);
        $data['output'] .= '<br />'.$graph;
        $query->free_result();

It seems really inefficient to re-run exactly the same query three times here, so I want to re-use the same $query object for all three. My datatable and graph functions use field_data(), so I can't use query caching unfortunately.

Anybody have any other ideas?

[eluser]Dam1an[/eluser]
I'm guessing its because after the first time you use it, you iterate all its contents, so the internal pointer is at the end of the results, so there's nothing left for the second time
I just looked through the DB method, and couldn't see one to reset the internal pointer 9although I'm sure there's a native php one to reset it

That failing, you could make 3 copies of the query result?

[eluser]TheFuzzy0ne[/eluser]
Please can you post the code in those three methods, where you need to re-use the result?

[eluser]CJL01[/eluser]
I am certainly iterating through the result set on each one of these calls, so you may be right and my pointer is at the end. Is there any way of resetting the pointer perhaps?

I guess I can't create a copy by setting say $query2 = $query, because I'm just creating two references to the same object. I tried $query2 = clone $query, but that doesn't seem to work either...

[eluser]CJL01[/eluser]
[quote author="TheFuzzy0ne" date="1243203752"]Please can you post the code in those three methods, where you need to re-use the result?[/quote]

Here's the table generation function. I can provide the graph one too, but I think the problem must be here, because the $query is empty by the time we even get to the graph function...

Code:

function generate($query)
    {
        $table = "<table><tr>";
        $fields = $query->field_data();

        //header
        foreach ($fields as $field)
        {
            $fieldtitle = ucwords(strtolower(str_replace("_", " ", $field->name)));
            $table .= '<th align="center">'.$fieldtitle.'</th>';
        }
        $table .= "</tr>";

        //data
        foreach ($query->result_array() as $row)
        {
            $table .= "<tr>";
            foreach ($fields as $field)
            {
                $format = $this->get_format($field->name);
                $table .= "<td";
                if (@$format['style'] != '')
                    $table .= ' style="'.$format['style'].'"';
                if (@$format['align'] != '')
                    $table .= ' align="'.$format['align'].'"';
                $table .= ">";
            if (@$format['link'] != '') {
                if ($field->name == 'Date') $table .= '<a >name])).'">';
                else $table .= '<a >name]).'">';                
            }
            $table .= $row[$field->name];
        if (@$format['link'] != '')
            $table .= '</a>';
        $table .= "</td>";
    }
$table .= "</tr>";
}

[eluser]Dam1an[/eluser]
I thought you need the & to do it by reference? (at least thats the case with get_instance())

And I just checked, the function to reset the internal pointer is

Code:

mysql_data_seek($query, 0);

Where $query is the result of mysql_result_assoc, not the CI result, so you'll need to modify the Db class to add that functionality (maybe it could be a feature request for the next version?)

[eluser]TheFuzzy0ne[/eluser]
What's wrong with copying the result array/object to a variable, and re-using that?

You are using the result array anonymously. Really, result_array/result are both single-use methods. If you want to re-use the result array, you'll need to store it somewhere.

Hopefully this makes sense?

[eluser]CJL01[/eluser]
[quote author="TheFuzzy0ne" date="1243206011"]What's wrong with copying the result array/object to a variable, and re-using that?

You are using the result array anonymously. Really, result_array/result are both single-use methods. If you want to re-use the result array, you'll need to store it somewhere.
[/quote]

Got it - the result_array is single use so I can't keep re-using it. All my library functions need is result_array() and field_data(), so I've stored these into variables and then pass them in as two separate arguments into each of my subsequent calls. This works perfectly, and allows me to re-use the data again and again.

Thanks very much TheFuzzyOne!!

Code:

$query = $this->new_model->daily_summary();
        $fields = $query->field_data();
        $results = $query->result_array();
        $query->free_result();
        
        $table = $this->datatable->generate($fields, $results);
        $data['output'] = '<h3>Trading Daily Summary</h3>'.$table;

        $graph = $this->graph->new_line_chart(1, 'N', $fields, $results);
        $data['output'] .= '<br />'.$graph;

        $graph = $this->graph->new_line_chart(4, 'N', $fields, $results);
        $data['output'] .= '<br /><br />'.$graph;