Azsen Posted September 9, 2006 Share Posted September 9, 2006 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 :) Quote Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2006 Share Posted September 10, 2006 try[code]<?phpinclude 'db.php';$data = array();$res = mysql_query("SELECT * FROM tablename");while ($data[] = mysql_fetch_assoc($res));//check the arrayecho '<pre>', print_r($data, true), '</pre>';?>[/code] Quote Link to comment Share on other sites More sharing options...
jefkin Posted September 10, 2006 Share Posted September 10, 2006 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 Quote Link to comment Share on other sites More sharing options...
Azsen Posted September 10, 2006 Author Share Posted September 10, 2006 Awesome. Thanks Barand and Jeff. :DIt 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? Quote Link to comment Share on other sites More sharing options...
jefkin Posted September 10, 2006 Share Posted September 10, 2006 Woops!Okay, my bad then, add:array_pop($fields); right after the first while.That should kill the naughty extra line. :)Jeff Quote Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2006 Share Posted September 10, 2006 Oops - mea culpa too.Change[code]while ($fields[] = mysql_fetch_array($resultB));[/code]to[code]while ($row = mysql_fetch_array($resultB)) { $fields[] = $row;}[/code] Quote Link to comment Share on other sites More sharing options...
Azsen Posted September 10, 2006 Author Share Posted September 10, 2006 Faaantastic. Works great. Thanks again. ;D Quote Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2006 Share Posted September 10, 2006 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]) Quote Link to comment Share on other sites More sharing options...
Azsen Posted September 10, 2006 Author Share Posted September 10, 2006 Cool, thanks for that tip. Got some good optimised code now. :D Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.