Jump to content

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


Azsen

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 :)
Link to comment
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
Link to comment
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?
Link to comment
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]

)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.