Jump to content

Archived

This topic is now archived and is closed to further replies.

Azsen

Copy entire MySQL result set into an array that can be re-used

Recommended Posts

Hi,

Sorry if this has been asked before, but I haven't found it in the search. If you know the answer, please point me in the right direction. :) What I want to do is run a query, and instead of looping through the rows returned line by line and processing them, I want to store all the rows in another array, in exactly the same format so I can re-use that array instead of re-querying the database all the time to get exactly the same result set back. It's more to optimise the code, at the moment it works fine.

Here's a code snippet:
[code]
$result = select("select * from games");

while ($row = mysql_fetch_array($result))
{
$gameId = $row['GameID'];
$roundNum = $row['RoundNum'];
$gameFieldId = $row['FieldID'];
$gameDateTime = $row['DateTime'];

$resultB = select("select * from field order by FieldName");

echo "<tr><td><select name='field$count'>";

while ($rowB = mysql_fetch_array($resultB)) {
$fieldId = $rowB['FieldID'];
$fieldName = $rowB['FieldName'];

if ($fieldId == $gameFieldId)
{
echo "<option selected value='$fieldId'>$fieldName</option>";
}
else {
echo "<option value='$fieldId'>$fieldName</option>";
}
}
echo "</select></td></tr>";
}
[/code]

So as you can see the first result set could be returning a lot of games, and the second query is populating a combo box with a list of fields that the games can be played at. It's like an edit page for a sports draw so for every game, you can change the field where the game is being played and store the change back in the database.

Now the list of fields isn't going to change every time I loop through each game and it must be pretty bad to keep hitting the database to return the same result set. So what I want to do is store that entire result set into a array up the top of the page and re-use the data in that instead of hitting the database each time I loop through. I would like the array to be still usable like the result set, like I can get the data out by using the Key of the database field.

How would I do this?

Thanks :)

Share this post


Link to post
Share on other sites
try
[code]<?php
include 'db.php';
$data = array();
$res = mysql_query("SELECT * FROM tablename");
while ($data[] = mysql_fetch_assoc($res));

//check the array
echo '<pre>', print_r($data, true), '</pre>';
?>[/code]

Share this post


Link to post
Share on other sites
Sure Azsen, and it should make your page load faster, especially if the outer loop repeats alot.

Why not just rip it out of there?  I also tried to make the output more w3c compliant.

[code]
/*
** extracted from loop for efficiency
*/
$fields    = array();
$resultB = select("select * from field order by FieldName");

while ($fields[] = mysql_fetch_array($resultB)) ;

/*
** regular loop
*/
$result = select("select * from games");

while ($row = mysql_fetch_array($result))
{
    $gameId        = $row['GameID'];
    $roundNum      = $row['RoundNum'];
    $gameFieldId    = $row['FieldID'];
    $gameDateTime = $row['DateTime'];

    echo "<tr><td><select name=\"field$count\">";

    foreach ($fields as $filedId => $field)
    {
        $selected = ($fieldID == $gameFieldId) ? ' selected="selected"' : '';

        echo "<option$selected value=\"$fieldId\">{$field['FieldName']}</option>";
    }
    echo "</select></td></tr>";
}
[/code]


... oh, Barand beat me too it, but since I typed it in...

Jeff

Share this post


Link to post
Share on other sites
Awesome. Thanks Barand and Jeff. :D

It was good you posted your code as well Jeff because I was unsure how to loop through the array and get the data out. Works pretty well now. Only thing is the code seems to output an empty line inside the select box right after all the data is finished. E.g.

[code]
<tr>
<td>
<select name="field1">
<option value="6">Place1</option>
<option selected="selected" value="5">Place2</option>
<option value=""></option>
</select>
</td>
</tr>
[/code]
I don't know where the empty option came from, but its not in the table. Something to do with the foreach perhaps? Might be in the [color=red]while ($fields[] = mysql_fetch_array($resultB));[/color] actually because there's an empty array in index being put in at the end. How do I get rid of it? :) I can fix with a simple if statement, but its weird that it's in there in the first place?

Also for the w3c standards I'm supposed to use [color=red]selected="selected"[/color] in the select box and only "" and not '' in the HTML code is that right?

Share this post


Link to post
Share on other sites
Woops!

Okay, my bad then, add:

array_pop($fields); 

right after the first while.

That should kill the naughty extra line. :)

Jeff

Share this post


Link to post
Share on other sites
Oops - mea culpa too.

Change
[code]while ($fields[] = mysql_fetch_array($resultB));[/code]
to
[code]while ($row = mysql_fetch_array($resultB)) {
      $fields[] = $row;
}[/code]

Share this post


Link to post
Share on other sites
Just noticed something..

use mysql_fetch_assoc() and not mysql_fetch_array().

By default, fetch_array() gets the values twice (numeric key and string key) so you get something like this
[code]
Array
(
    [0] => Array
        (
            [0] => 1
            [ID] => 1
            [1] => Africa
            [continent] => Africa
            [2] => Kenya
            [countryname] => Kenya
        )[/code]

whereas fetch_assoc just gives

[code]Array
(
    [0] => Array
        (
            [ID] => 1
            [continent] => Africa
            [countryname] => Kenya
        )

)

[/code]

)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.