Creating a Loop and incrementing

[lndlyb4]

Hello, I am trying to write some php code and I'm kind of new at this.
In the code below, I am attempting to print out an index of records for a price list that has 25 records per page. (determined by the variable $display =25)
The index should indicate on which page each record is found.
I've written the code below that first extracts the row number for each record, then determines which range it falls in with if statements.
I'm sure this can be done in a much more compact way with loops and incrementing and such, but that's a bit over my head.
If you have the time to guide me through this, I would appreciate it. (I already have the code that paginates the records based on $display = 25, this code is just for creating an index that indicates which page number a record is on.)

You can see a test page here. http://www.blackoliveeastnursery.net/zenCart_boe2/price_list_binder/active/test_row_number.php
My code is below:

//set display number
$display = 25;


//Count the number of records.

$q = "SELECT COUNT(products_id) FROM zen_products";

$r = @mysqli_query ($dbc, $q);

$row = @mysqli_fetch_array ($r, MYSQLI_NUM);

$records = $row[0];

print '<table><tr><td>Records: ' . $records . '</td></tr></table>';

//Get the row number
$query='SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, zen_products t';

//make sure the query worked
if ($result = @mysqli_query($dbc, $query)) {

//print "It looks good from here.";
} else {


print "The query failed because ". mysql_error() .". <br/><br/>
The query was $query";


}

//print the table
print "<table width=500px border=1>";
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
extract($row);

?>

<?php

print 
'<tr >

<th align="center">Common Name</th>
<th width=100>Row #</th>
<th width=100>Page #</th>
</tr>
<tr>
<td align="center">'. $row['products_common_name'] . '</td>
<td align="center">&nbsp;' . $rownum . '</td>
<td align="center">';
?>

<?php  

print 'display' . $display . '<br>';
print 'row number' . $rownum . '<br>';

//if the record is 1-25, the record is on page 1
if ($rownum >= 1 && $rownum <= $display) {

$page_num = 1;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 26-50, the record is on page 2
if ($rownum >= ($display+1) && $rownum <= ($display*2)) {

$page_num = 2;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 51-75, the record is on page 3
if ($rownum >= (($display*2)+1) && $rownum <= ($display*2 + 25)) {

$page_num = 3;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 76-100, the record is on page 4
if ($rownum >= (($display*3)+1) && $rownum <= ($display*3 + 25)) {

$page_num = 4;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 101-125, the record is on page 5
if ($rownum >= (($display*4)+1) && $rownum <= ($display*4 + 25)) {

$page_num = 5;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 126-150, the record is on page 6
if ($rownum >= (($display*5)+1) && $rownum <= ($display*5 + 25)) {

$page_num = 6;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 151-175, the record is on page 7
if ($rownum >=  (($display*6)+1) && $rownum <= ($display*6 + 25)) {

$page_num = 7;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 176-200, the record is on page 8
if ($rownum >= (($display*7)+1) && $rownum <= ($display*7 + 25)) {

$page_num = 8;
print 'page ' . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 201-225, the record is on page 9
if ($rownum >= (($display*8)+1) && $rownum <= ($display*8 + 25)) {

$page_num = 9;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 226-250, the record is on page 10
if ($rownum >= (($display*9)+1) && $rownum <= ($display*9 + 25)) {

$page_num = 10;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}



print '</td></tr>';

		}
print "</table>";
?>



</div><!--end main container-->
</body>
</html>

[laserlight]

This chunk:

//if the record is 1-25, the record is on page 1
if ($rownum >= 1 && $rownum <= $display) {

$page_num = 1;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 26-50, the record is on page 2
if ($rownum >= ($display+1) && $rownum <= ($display*2)) {

$page_num = 2;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 51-75, the record is on page 3
if ($rownum >= (($display*2)+1) && $rownum <= ($display*2 + 25)) {

$page_num = 3;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 76-100, the record is on page 4
if ($rownum >= (($display*3)+1) && $rownum <= ($display*3 + 25)) {

$page_num = 4;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 101-125, the record is on page 5
if ($rownum >= (($display*4)+1) && $rownum <= ($display*4 + 25)) {

$page_num = 5;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
}

//if the record is 126-150, the record is on page 6
if ($rownum >= (($display*5)+1) && $rownum <= ($display*5 + 25)) {

$page_num = 6;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 151-175, the record is on page 7
if ($rownum >=  (($display*6)+1) && $rownum <= ($display*6 + 25)) {

$page_num = 7;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 176-200, the record is on page 8
if ($rownum >= (($display*7)+1) && $rownum <= ($display*7 + 25)) {

$page_num = 8;
print 'page ' . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 201-225, the record is on page 9
if ($rownum >= (($display*8)+1) && $rownum <= ($display*8 + 25)) {

$page_num = 9;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}

//if the record is 226-250, the record is on page 10
if ($rownum >= (($display*9)+1) && $rownum <= ($display*9 + 25)) {

$page_num = 10;
print 'page '  . $page_num . '<br>';//inserts the page number the record is on
						}

Can be simplified to:

$page_num = (int)(($rownum - 1) / $display) + 1;
echo 'page ' . $page_num . '<br>'; //inserts the page number the record is on

[lndlyb4]

laserlight,

Thank you so much for responding. Your suggestion works perfectly in my test page when I remove all extra code, but when I try to insert it into the page below (where it's really supposed to work), I can't get it to function. I'm guessing that it has something to do with the queries.

Here is the link to the page I am trying to incorporate it into.
http://www.blackoliveeastnursery.net/zenCart_boe2/price_list_binder/active/list_by_common_name.php?s=144&p=26

Here is the test page link that works fine.
http://www.blackoliveeastnursery.net/zenCart_boe2/price_list_binder/active/test_row_number.php

If you have time to help, I appreciate it.

<?php


//Attempt to connect to MySQL and print out messages.
include "../../../../../connect_info_links.php";

//BEGINNING OF COUNT NUMBER OF RECORDS SECTION... ADD A NEW QUERY. USE THE SAME AS THE ONE WITH JOINS BELOW. THEN AD THE CODE.



//BEGINNING OF SECTION 1 OF THE PAGINATION CODE:

//Number of records to show per page:

//$display = 32;//setting for small binder
$display = 48;//setting for large binder

//Determine how many pages there are:

if(isset($_GET['p']) &&
is_numeric($_GET['p'])) {//Already been determined.

$pages = $_GET['p'];

} else {//Need to determine.

//Count the number of records.

$q = "SELECT COUNT(products_id) FROM zen_products";

$r = @mysqli_query ($dbc, $q);

$row = @mysqli_fetch_array ($r, MYSQLI_NUM);

$records = $row[0];

//Calculate the number of pages.

if ($records > $display)  {//More than one page.

$pages = ceil ($records/$display);

} else {

$pages =1;

}

}//End of IF Statement.


//Determine where in the database to start returning results.

if (isset($_GET['s']) && is_numeric($_GET['s'])) {

$start = $_GET['s'];

} else {

$start = 0;

}






//BEGINNING OF MAIN QUERY

$query = " SELECT *

FROM
    zen_products AS p 

INNER JOIN
    zen_products_description AS pd
    ON
    p.products_id = pd.products_id



JOIN 
    zen_categories_description AS cd
    ON
    p.master_categories_id = cd.categories_id";

	$query .= " ORDER BY p.products_common_name

	LIMIT $start, $display

";
	//	INNER JOIN
      //  zen_products_price AS pr
      //  ON
      //  p.products_id = pr.products_id	

if ($result = @mysqli_query($dbc, $query)) {
//print "It looks good from here.";


} else {

print "The query failed because ". mysql_error() .". <br/><br/>
The query was $query";


}



while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
			{
			extract($row);



		?>

		<?php

			print 
			'<tr class="rowHeight">
				<td class ="leftAlign">&nbsp;' . $row['products_common_name'] . '</td>

				<td class ="leftAlign">'. $row['products_name'] . '</td>

				<td class ="leftAlign">'; 


				//Get all records from the database and assign a number
				$query='SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=0) r, zen_products t';

				//make sure the query worked
				if ($result = @mysqli_query($dbc, $query)) {

				//print "It looks good from here.";
				} else {


				print "The query failed because ". mysql_error() .". <br/><br/>
				The query was $query";


				}

				print '</td>';
		}

		//BEGINNING OF SECTION 2 OF THE PAGINATION CODE

if ($pages > 1) {

echo '<div id="pagination_nav_bar_com_nam">';

$current_page = ($start/$display) + 1;


//If it's not the first page, make a Previous button.

if($current_page !=1) {

echo '<div id="first"><a href="http://blackoliveeastnursery.net/zenCart_boe/priceManager/list_by_common_name.php?s=' . ($start - $display) . '&p=1">First</a></div>';

} else {

echo '<div id="first">&nbsp;</div> ';
 }


if($current_page !=1) {

echo '<div id="previous_nav"><a href="list_by_common_name.php?s=' . ($start - $display) . '&p=' . $pages . '">Previous</a></div> ';

} else {

echo '<div id="previous_nav">&nbsp;</div> ';
 }

 if($current_page !=36) {

echo '<div id="last"><a href="http://blackoliveeastnursery.net/zenCart_boe/priceManager/list_by_common_name.php?p=36">Last</a></div>';

} else {

echo '<div id="last">&nbsp;</div> ';
 } 


//If it's not the last page, make a Next button:

if ($current_page != $pages){

echo '<div id="next_nav"><a href="list_by_common_name.php?s=' . ($start + $display) . '&p=' . $pages . '">Next</a></div><br />';

}

echo '</div>';//End of pagination navigation bar.

}//END OF PAGINATION SECTION.

print '<div id="page_of_print_common">Page ' . number_format($current_page,0) . '</div>';			
			//' of ' . $pages . 


?>

[DaveRandom]

OMG I don't even know where to start with that...

A couple of observations:

• You are not closing your table rows with a '</td>' in the while loop

• You do not have an opening '<table>' or closing '</table>' element.

• You are calling extract() at the beginning of each iteration of the while loop and never using any of the resulting variables.

• The 'include "../../../../../connect_info_links.php";' call is presumably producing a whole html page with no content, including closing '</body></html>', before any of the content you are producing. If you view source on your first link above, you will see what I mean.

• Running a query on your database in every iteration of a loop will put your database under a lot of stress, try and work this into a single query at the beginning/end of the loop if possible

• MySQLi is really designed for OO (Object Oriented) use, it would be much better to write your code like this, although since you say you are very inexperienced I suppose this is a secondary consideration

I do not know enough about SQL to fix this - I suspect part of the problem lies in your main SQL query, since the page keeps producing content forever, so far as I can tell, and this would (presumably) because the while loop never exits.

There are two ways do the pagination exercise you would like to do that I can think of...

If you can SELECT (one of) your primary keys from the database you can use that as the increment for the calculation, or, probably a more reliable way to do it would be to use a for loop instead of while.

e.g.

// mysqli_fetch_array() returns NULL when there are no more rows, so compare with that instead of treating it as a boolean
// the variable $i is incremented with every row, so you can use that for the pagination calculation
for ($i = 1; ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) !== NULL; $i++) {

  // calculate the page this row is on
  $thispage = ceil ( $i / $display );

  // do the query (I don't know what this query does - does it need to be done here?)
  $qresult = FALSE;
  $query='SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=0) r, zen_products t';
  if (!$result = @mysqli_query($dbc, $query)) {
    //print "It looks good from here.";
  } else {
    $qresult = "The query failed because ". mysql_error() .". <br/><br/>The query was $query";
  }

  // print the table row
    print 
				'<tr class="rowHeight">
					<td class ="leftAlign">&nbsp;' . $row['products_common_name'] . '</td>

				<td class ="leftAlign">'. $row['products_name'] . '</td>

				<td class ="leftAlign">$thispage';
  // If the query failed, print the message in the last column
  if ($qresult) print " ($qresult)";

  // close the last cell and the table row
  print '</td>
       </tr>';
} // end FOR loop

Try replacing your whole while () {} structure with that and see if it does what you want.

Let us know how you get on.

P.S. sorry for the random attack of info at the top 😉