How to read next row in a mySql Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Bootstrap
Forum Newbie
Posts: 3
Joined: Wed Jan 11, 2012 8:16 pm

How to read next row in a mySql Query

Post by Bootstrap »

Hi!

I have this php code which looks like this:

Code: Select all

$sql = ......
$result = mysql_query($sql,$connection)
or die("Couldn't execute SELECT query");
 
while ($row = mysql_fetch_array($result)) {
......
}
 
What i want to do is be able to read the next row in the query. So for example:

Code: Select all

while ($row = mysql_fetch_array($result)) {
......
if (ID for next row/record != ID for this row/record) {
do this
}
}
I need an output result like this:

[syntax]
Product name 1
Product name 1
<break line or something else>
Product name 2
Product name 2
Product name 2
Product name 2
<break line or something else>
Product name 3
...
[/syntax]

I almost got the solution , but the very last record for my query is always missing using this code:

Code: Select all

$current_row=mysql_fetch_array($result); //read $current_row
while ($next_row = mysql_fetch_array($result)) { //read $next_row
......
if ($current_row['id']!=$next_row['id']) { //compare it
.....
.....
}
$current_row=$next_row; //$next_row become current_row on next step 
}
Any help would be appreciated
Thank You!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to read next row in a mySql Query

Post by Christopher »

Either the first or last row will be missing when checking prev/next rows. You need to add a check for isset($row['id']) to check if the row exists.
(#10850)
Bootstrap
Forum Newbie
Posts: 3
Joined: Wed Jan 11, 2012 8:16 pm

Re: How to read next row in a mySql Query

Post by Bootstrap »

Christopher wrote:Either the first or last row will be missing when checking prev/next rows. You need to add a check for isset($row['id']) to check if the row exists.
Thanks for help, Christopher.

Please, how this check with 'isset' works? Is it samething like this:

Code: Select all

$current_row=mysql_fetch_array($result); //read $current_row
while ($next_row = mysql_fetch_array($result)) { //read $next_row
......
if (isset($current_row['id'])!=isset($next_row['id'])) { //compare it
.....
.....
}
$current_row=$next_row; //$next_row become current_row on next step
}
I tried this way, but with no success.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to read next row in a mySql Query

Post by mikosiko »

The way to output a new column, heading, category or section once, each time it changes, is to remember the last value (initialized to a value that will never exist in the data), detect when it changes and start a new column, heading, category, section, and remember the new value...

this is a pseudo-code (replace variables according to your situation) of one way to do it:

Code: Select all

<?php

  // Example
  $heading_column = '<whatever is the name of your heading column>';
  $last_heading = null;
  
  while($row = your_fetch_assoc_statement){
  	// detect a change in the heading value and output the new heading
  	if($last_heading != $row[$heading_column]){
  		// detect if it is not the first heading - close out the previous section
  		if($last_heading != null){
  			// your code to close the previous section (table, div, etc)...
  			echo "close section<br />";
  		}
  		// output the new heading here...
  		echo "new section title - {$row[$heading_column]}<br />";
  
  		// save the new heading as the last_heading
  		$last_heading = $row[$heading_column];
  	}
  	// output the actual data here...
  	echo "data - {$row['your_data']}<br />";
  }
  // if there was any output - close out the last section
  if($last_heading != null){
  	// your code to close the previous section (table, div, etc)...
  	echo "close section<br ?>";
  }
?>
Bootstrap
Forum Newbie
Posts: 3
Joined: Wed Jan 11, 2012 8:16 pm

Re: How to read next row in a mySql Query

Post by Bootstrap »

mikosiko wrote:The way to output a new column, heading, category or section once, each time it changes, is to remember the last value (initialized to a value that will never exist in the data), detect when it changes and start a new column, heading, category, section, and remember the new value...

this is a pseudo-code (replace variables according to your situation) of one way to do it:

Code: Select all

<?php

  // Example
  $heading_column = '<whatever is the name of your heading column>';
  $last_heading = null;
  
  while($row = your_fetch_assoc_statement){
  	// detect a change in the heading value and output the new heading
  	if($last_heading != $row[$heading_column]){
  		// detect if it is not the first heading - close out the previous section
  		if($last_heading != null){
  			// your code to close the previous section (table, div, etc)...
  			echo "close section<br />";
  		}
  		// output the new heading here...
  		echo "new section title - {$row[$heading_column]}<br />";
  
  		// save the new heading as the last_heading
  		$last_heading = $row[$heading_column];
  	}
  	// output the actual data here...
  	echo "data - {$row['your_data']}<br />";
  }
  // if there was any output - close out the last section
  if($last_heading != null){
  	// your code to close the previous section (table, div, etc)...
  	echo "close section<br ?>";
  }
?>
mikosiko, Thank you VERY MUCH for your great help!
Your suggestion worked like a glove! :D
Post Reply