[RESOLVED] Get value from next row while reading fields from current row

SundariDevi · Jan 17, 2012

Hello,

I am reading data from a table and I first create a summary page of all the query results, then the use can click on a result to see the detailed page (with all fields displayed). From that detailed page I want to have two possible navigation buttons: "back" - goes back to the summary
"next" - goes to the next detailed page from the summary list

When the user clicks the linked result, he will go to mysite.com/details.php?id=001&next=002 so the variables are passed in the url like that (this is an ssl secure intranet page).

My code looks like this

$result = mysql_query($query);

if (!$result)
		{
		echo "data read error.";
		echo mysql_errno() . " : " . mysql_error();
		exit;
		}
$num_results = mysql_num_rows($result);


// print summary page 

if ($num_results >0)
	{
	for ($i=0; $i < $num_results; $i++)
		{
		$row = mysql_fetch_array($result);
		echo '<tr>';
		// create link to detail page
		// need to pass parameter 'next' for clicking to next page from details page, so get next booking ref and pass as parameter
		if ($i < $num_results)
			{
			$next = mysql_result($result,$i+1,"bookingref");
			echo '<td><a href="detail.php?id='.mysql_real_escape_string($row[bookingref]).'&next='.$next.'">'.$row[bookingref].'</a></td>';
			}
			else
		echo '<td><a href="detail.php?id='.mysql_real_escape_string($row[bookingref]).'">'.$row[bookingref].'</a></td>';

	echo '<td>Unconfirmed</td>';
	...echo more fields

The above code doesn't work, when I loop through the array of results, the last row is always out of bounds. It seems like maybe the mysql_result() function is advancing the pointer.

What is the correct way to do this? The code works like this if I don't use the "next" parameter:

$result = mysql_query($query);

if (!$result)
		{
		echo "data read error.";
		echo mysql_errno() . " : " . mysql_error();
		exit;
		}
$num_results = mysql_num_rows($result);


// print summary page 

if ($num_results >0)
	{
	for ($i=0; $i < $num_results; $i++)
		{
		$row = mysql_fetch_array($result);
		echo '<tr>';
		// create link to detail page
		// need to pass parameter 'next' for clicking to next page from details page, so get next booking ref and pass as parameter
		echo '<td><a href="detail.php?id='.mysql_real_escape_string($row[bookingref]).'">'.$row[bookingref].'</a></td>';

	echo '<td>Unconfirmed</td>';
	...echo more fields

Weedpacket · Jan 17, 2012

    for ($i=0; $i < $num_results; $i++) 
//...
  if($i < $num_results)

$i is always going to be less than $num_results.
Since there are $num_results rows, and the first one is numbered 0, the last one is numbered $num_results - 1/

SundariDevi · Jan 17, 2012

Yes, I had already fixed that one, but that's not the problem.

If I write it like this

for ($i=0; $i < $num_results; $i++)
		{
		$row = mysql_fetch_array($result);
		echo '<tr>';
		// create link to detail page
		// need to pass parameter 'next' for clicking to next page from details page, so get next booking ref and pass as parameter
		$j=$i+1;
		if ($j < $num_results)
			{
			$next = mysql_result($result,$j,$row[bookingref]);
			echo '<td><a href="detail.php?id='.mysql_real_escape_string($row[bookingref]).'&next='.$next.'">'.$row[bookingref].'</a></td>';
			}
			else
		echo '<td><a href="detail.php?id='.mysql_real_escape_string($row[bookingref]).'">'.$row[bookingref].'</a></td>';

I get a warning printed out for each iteration (e.g. the first value it finds for $i=0 for bookingref is 0000142) and then it outputs the page with $next=""

Warning: mysql_result() [function.mysql-result]: 0000142 not found in MySQL result index 4 in [path]confirm.php on line 122

Warning: mysql_result() [function.mysql-result]: 0000144 not found in MySQL result index 4 in [path]confirm.php on line 122

Warning: mysql_result() [function.mysql-result]: 0000145 not found in MySQL result index 4 in [path]confirm.php on line 122

Warning: mysql_result() [function.mysql-result]: 0000146 not found in MySQL result index 4 in [path]confirm.php on line 122

Where line 122 is :

$next = mysql_result($result,$j,$row[bookingref]);

I'm not sure what it means by index 4. Is that the 5th field in the results array? "bookingref" should be field 0

SundariDevi · Jan 17, 2012

Also:

$result = mysql_query($query);

SundariDevi · Jan 17, 2012

Reading the manual, it says mysql_result() shouldn't be used with any other fetching functions. So I changed the code to call it in a separate loop, store the next values in array $next() then populate the variables from that array.

When I first did that, all the subsequent calls to mysql_fetch_array() sent back NULL. So then I reinitialized $result with

$result=mysql_query($query)

Now it works perfectly. But I'm not very happy with this code. Seems very inefficient. Here's what I have:

$result = mysql_query($query);

if (!$result)
		{
		echo "data read error.";
		echo mysql_errno() . " : " . mysql_error();
		exit;
		}
$num_results = mysql_num_rows($result);

	// need to pass parameter 'next' for clicking to next page from details page, so get next booking ref and store in $next array
	//$j=$i+1;
if ($num_results >0)
	{
	for ($i=0; $i < $num_results-1; $i++)
		{
		$next[$i] = mysql_result($result,$i+1,$row["booking.bookingref"]);
		}
	}

// reinitialize $result
$result = mysql_query($query);

if ($num_results >0)
	{
	for ($i=0; $i < $num_results; $i++)
		{
		$row = mysql_fetch_array($result);
		echo '<tr>';
		// create link to detail page
		// get next booking ref from $next array and pass as parameter
		$j=$i+1;
		if ($j < $num_results)
			{
			echo '<td><a href="detail.php?id='.mysql_real_escape_string($row[bookingref]).'&next='.$next[$i].'">'.$row[bookingref].'</a></td>';
			}
			else
		echo '<td><a href="detail.php?id='.mysql_real_escape_string($row[bookingref]).'">'.$row[bookingref].'</a></td>';
...echo more stuff ...

Derokorian · Jan 17, 2012

This may seem like a simple question, but why not simply add the next link in the next iteration of the loop?

SundariDevi · Jan 17, 2012

the "next" parameter is part of the url that is written in the current iteration. So the only way I could add next in the following iteration would be to save everything to some array and then write it out later. In that case I still would have two loops.

Is there some pointer in the mysql_fetch_array() function that can be reset? So I don't have to do the query again?

SundariDevi · Jan 17, 2012

Now I found the function to reset the pointer. So I replaced the second query with that.

//$result = mysql_query($query);

mysql_data_seek($result, 0);

Is there some better way to do get the $next value??

Derokorian · Jan 17, 2012

I think I'm missing something why do you need the next ID within the link? It seems like that's unneccessary because when you view item with ID=123456789 You can simply do

$sql =  'SELECT `id` FROM `table` WHERE `id` > 123456789';
$result = mysql_query($sql);
if( $result === FALSE ) myError('Database Error;'.mysql_error() .' - '.$sql);
if( mysql_num_rows($result) == 0 ) {
   echo 'Last result';
} else {
   $row = mysql_fetch_assoc($result);
   echo '<a href="http://url.com/to/file?id='.$row['id'].'">Next</a>';
}

bradgrafelman · Jan 17, 2012

One way might be to simply loop through the result set and do nothing but store the data in an array, that way you'd be able subsequently loop through that array and access the next index when outputting the 'next' link.

Another way might be to not even calculate/determine the next ID value at all and simply let MySQL take care of it. For example, rather than having the 'next' link explicitly point to the next ID, simply pass on the current ID and the fact that you want the 'next' result. Then your SQL would look something like:

WHERE id > 123 ORDER BY id ASC LIMIT 1

EDIT: Ah, looks like I took too long to post. But still note that Derokorian's example above is missing the 'ORDER BY' (and 'LIMIT' if you only care about the first result).

SundariDevi · Jan 17, 2012

I considered fetching the next id from the detail page but thought it would be faster to just do it all at once on the summary page, but it was not as easy as I expected.

@: Yes that store and then write in next loop was what I meant above.

I guess if these are the only three possibilities, I'll stick with what I've got for the moment. Maybe it's cleaner to calculate the value of $next on the detail page and not pass the value in the url as a parameter, then I have an extra query on every page. For this application that won't matter much, but it was worth a try.

Derokorian · Jan 17, 2012

Also note on the detail page you could fetch both the next id and the current item at once (note using because I don't know your schema, you should list the columns you expect):

SELECT * FROM table WHERE id >= $id ORDER BY id ASC LIMIT 2

Where the first row would be the item you're looking at and the second row would be the next item.

SundariDevi · Jan 17, 2012

Thanks, in fact on the detail page i do fetch all fields for the current id because I need to display all the data from a join of three tables. What you suggest could be the best way but I'll probably need to get all data not just "greater than current id" because there is no guarantee that the required data will be in sequence.

I was just trying to get it on the previous page because I thought I would need a separate query to get it on the next page. But right now it works and I'm behind schedule, so I'll try to optimize it later.

This is the first time i've worked with a database bigger than 3 tables. It's got about 80 variables.

Derokorian · Jan 17, 2012

BTW what I meant about doing it on the next iteration was this:

$i = 1;
$lastname = '';
$rows = mysql_num_rows($result);
while( $row = mysql_fetch_assoc($result) ) {
   if( $i > 1 ) {
      echo $row['id'].'">'.$lastname.'</a></td>';
   }
   // Print current record as needed
   if( $i < $rows ) {
      echo '<a href="details.php?id='.$row['id'].'&next=';
   } else {
      echo '<a href="details.php?id='.$row['id'].'">'.$row['name'].'</a>';
   }
   $lastname = $row['name'];
   $i++;
}

SundariDevi · Jan 18, 2012

@: OK, that's a good solution! Thanks for your help

[RESOLVED] Get value from next row while reading fields from current row

SSundariDevi

Weedpacket

SSundariDevi

SSundariDevi

SSundariDevi

DDerokorian

SSundariDevi

SSundariDevi

DDerokorian

Bbradgrafelman

SSundariDevi

DDerokorian

SSundariDevi

DDerokorian

SSundariDevi