Jump to content


Photo

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


  • Please log in to reply
8 replies to this topic

#1 Azsen

Azsen
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 09 September 2006 - 11:55 PM

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:
$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>";	
}

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 :)

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 10 September 2006 - 12:58 AM

try
<?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>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 10 September 2006 - 01:11 AM

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.

/*
** 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>";
}


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

Jeff

#4 Azsen

Azsen
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 10 September 2006 - 02:15 AM

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.

<tr>
	<td>
		<select name="field1">
			<option value="6">Place1</option>
			<option selected="selected" value="5">Place2</option>
			<option value=""></option>
		</select>
	</td>
</tr>
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 while ($fields[] = mysql_fetch_array($resultB)); 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 selected="selected" in the select box and only "" and not '' in the HTML code is that right?

#5 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 10 September 2006 - 03:13 AM

Woops!

Okay, my bad then, add:

array_pop($fields); 

right after the first while.

That should kill the naughty extra line. :)

Jeff

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 10 September 2006 - 08:20 AM

Oops - mea culpa too.

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 Azsen

Azsen
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 10 September 2006 - 08:49 AM

Faaantastic. Works great. Thanks again. ;D

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 10 September 2006 - 09:11 AM

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
Array
(
    [0] => Array
        (
            [0] => 1
            [ID] => 1
            [1] => Africa
            [continent] => Africa
            [2] => Kenya
            [countryname] => Kenya
        )

whereas fetch_assoc just gives

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

)


)


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 Azsen

Azsen
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 10 September 2006 - 11:01 AM

Cool, thanks for that tip. Got some good optimised code now.  :D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users