Jump to content

Mysql_fetch_array()


shadowayex
 Share

Recommended Posts

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?

Edited by ragae
Link to comment
Share on other sites

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

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

Edited by boen_robot
Link to comment
Share on other sites

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

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

Edited by Synook
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...