shadowayex Posted September 21, 2009 Share Posted September 21, 2009 I'm working on a project to help keep track of money accounts for a local band I'm in. I ran into an odd problem when using the same query in two different array fetches. I worked around it, but I'd like to know what happened and why. Here's an example of what the page looks like (scaled down of course) and an explanation of what was supposed to happen. <?php//connection stuffmysql_query("SELECT * FROM accounts"); //query for all accounts?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html lang="en-US" xml:lang="en-US" xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Deposit Money</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body> <table border="1"> <tr> <th>Account</th> <th>Balance</th> </tr> <?php //Create a table with all the accounts and balances while($account = mysql_fetch_array($accouts)) { ?> <tr> <td><?php echo $account['name']; ?></td> <td><?php echo $account['balance']; ?></td> </tr> <?php } ?> </table> //From for depositing <form action="deposit.php" method="post"> //Create a drop-down with all account names <select name="account"> <option value=""></option> <?php while($account = mysql_fetch_array($accounts)) { ?> <option value="<?php echo $account['name']; ?>"><?php echo $account['name']; ?></option> <?php } ?> </select> <!--- Rest of form ---> </form> </body></html> Obviously, what is supposed to happen in a table with the current balances is supposed to be create (and is), and a drop-down menu with all the account names is supposed to be create (but is not). But, adding a second query before the drop-down menu fixes the problem: <form action="deposit.php" method="post"> //Create a drop-down with all account names <select name="account"> <option value=""></option> <?php $accounts = mysql_query("SELECT * FROM accounts"); //Once again, grab all accounts while($account = mysql_fetch_array($accounts)) { ?> <option value="<?php echo $account['name']; ?>"><?php echo $account['name']; ?></option> <?php } ?> </select> <!--- Rest of form ---></form> My question is what causes me to have to query again? Does mysql_fetch_array() do something to the original query or what? Link to comment Share on other sites More sharing options...
chibineku Posted September 21, 2009 Share Posted September 21, 2009 To use the same variable to hold the fetched array, you have to use mysqli_free_result($account) when you're finished with it the first time, otherwise the pointer will be at the last (empty) position of the array. Link to comment Share on other sites More sharing options...
boen_robot Posted September 21, 2009 Share Posted September 21, 2009 Yes. The pointer to the current result row is stored in the outputed resource from mysql_query() (stored in the $account variable). mysql_fetch_*() only processes that content, after which it moves the pointer down the result set.You can manually move the pointer with mysql_data_seek(), which BTW is quicker than doing a new query.So, instead of $accounts = mysql_query("SELECT * FROM accounts"); //Once again, grab all accounts do mysql_data_seek($accounts, 0); Link to comment Share on other sites More sharing options...
jeffman Posted September 21, 2009 Share Posted September 21, 2009 EDIT. Robot beat me. Link to comment Share on other sites More sharing options...
shadowayex Posted September 21, 2009 Author Share Posted September 21, 2009 Oh, well that makes sense I suppose. I don't know much about pointers besides a little work in C++ (very little), but I know enough to understand what you're talking about, ish. Anyway, the mysql_data_seek() worked and whatnot. I'll have to look up more on the functions to understand exactly what technically happened, but it's fixed now, and more efficiently than my fix. Thanks =D. Link to comment Share on other sites More sharing options...
Synook Posted September 21, 2009 Share Posted September 21, 2009 The "pointer" boen_robot was talking about isn't a memory pointer (as in C), it is just a number (e.g. 1, 2, 3) that represents the current row being read in the DB. So, when you first query the database, the pointer points to row 0. But then, you call mysql_fetch_array(), the row the pointer points at is read from the DB, and the pointer is incremented so it then points at the next row (so the MySQL engine knows which row to read next). However, if you don't actually want to read that row, you can manually move the pointer (using mysql_data_seek()) so it points where you want it to (i.e. the first row again). Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.