PHP Pagination

[TheKG]

Please help!! I've tried numerous PHP pagination scripts over the last 2 days and can't seem to be able to get any to work.

I got this code from tonymarston.net and changed all the instances of mysql to mysqli. I have my db on hostmonster and found that mysql command does not work. I also changed "tablename" to my actual table name.

When I attempt to open the page, I receive this message:

Warning: mysqli_query() expects at least 2 parameters, 1 given on line 37

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given on line 38

These are the lines in the code below that are causing the errors:

$result = mysqli_query("select id from tablename");
$total_results = mysqli_num_rows($result);

Each time I search for an explanation, I find different commands and codes and now none of it makes sense.

Would appreciate any assistance I can get on this one.


<?
$page = (!isset($_GET['page'])? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);

$max_results = 5;

$from = (($page * $max_results) - $max_results);

/* Query the db for total results. I used "*" for id and used my tablename */
$result = mysqli_query("select id from tablename");

$total_results = mysqli_num_rows($result);

$total_pages = ceil($total_results / $max_results);

$pagination = '';

if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.'">Previous</a> ';
}

for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="index.php?page='.$i.'">$i</a>';
}
}

if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.'">Next</a>";
}


/* Below I changed tablename to my actual table name */
$result=mysqli_query("select *
from tablename
LIMIT $from, $max_results ");

while ($i = mysqli_fetch_array($result))
{

}
?>

[pghcollectibles]

i could research mysqli but i dont have time right now.

mysql_query and mysql_num_rows should both work, they do for me

[TheKG]

PGH,
What 2 paramaters is mysql expecting? Null?? what null?? These seem to be problems for now.

Here's the message:

Warning: mysqli_query() expects at least 2 parameters, 1 given on line 37

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given on line 38

Here are lines 37 & 38:

$result = mysqli_query("select id from tablename");
$total_results = mysqli_num_rows($result);

[sjlplat]

PGH,
What 2 paramaters is mysql expecting? Null?? what null?? These seem to be problems for now.

Here's the message:

Warning: mysqli_query() expects at least 2 parameters, 1 given on line 37

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given on line 38

Here are lines 37 & 38:

$result = mysqli_query("select id from tablename");
$total_results = mysqli_num_rows($result);

Try using mysql_query(), mysql_num_rows(), and mysql_fetch_array() instead of the mysqli derivatives. There's no reason why mysql commands shouldn't work for you. They work fine on all the sites I've developed on a multitude of different Hostmonster servers.


$result = mysql_query("SELECT id FROM tablename");

$total_results = mysql_num_rows($result);

while ($i = mysql_fetch_array($result))
{

[linFox]

The issue is that mysql_query and mysqli_query have their parameters reversed compared to one another.

mysql_query's first two parameters are the query string, and then optionally the link identifier; mysqli_query's is the link identifier first and the query string second.

So, change the mysqli_query line to be:
$result = mysqli_query($link, "query string");
where $link is the name of your mysqli_connect resource (eg. your $link = mysqli_connect(...); line)

The other errors are simply cascading out from that first problem.

[TheKG]

Fox,
Tried your suggestion. No errors, but no pagination either.

[TheKG]

sj - Still got errors. Tried next suggestion by Fox.

[pghcollectibles]

what is the error message when using mysql_query

[TheKG]

Same as in my first post:

Warning: mysqli_query() expects at least 2 parameters, 1 given on line 37

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given on line 38

Here are lines 37 & 38:

$result = mysqli_query("select id from tablename");
$total_results = mysqli_num_rows($result);

[pghcollectibles]

repeat... what was the error when using mysql_query not mysqli_query

[TheKG]

Sorry...lost focus. Here's the error:

Warning: mysql_query() [function.mysql-query]: Access denied for user 'username'@'localhost' (using password: NO) in *//directory name//*on line 38

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in *//directory name//* on line 38

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in *//directory name//* on line 40

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in *//directory name//*on line 42

[sjlplat]

Looks like the code for your MySQL login data is either missing or incorrect. Can you post the code for your connection data?

[TheKG]

Looks like the code for your MySQL login data is either missing or incorrect. Can you post the code for your connection data?

This is at the beginning of my php code:

include('admin/misc.inc');
$cxn = mysqli_connect($host,$user,$passwd,$dbname)
or die ("couldn't connect to server");

I changed mysqli to mysql in the above code, and here's the error messages:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in //*directory name nere*// on line 40

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in //*directory name nere*// on line 42

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in //*directory name nere*// on line 73
couldn't execute query.

And here's lines 73 and 74:

$result= mysql_query($cxn,"SELECT * FROM prints LIMIT $from, $max_results")
or die ("couldn't execute query.");

Hope this helps find the problem(s)

[TheKG]

Been working on this code a bit. Now I got it to the point where there are no errors displaying, but the pagination is not showing. Not sure what code is needed to use the $pagination variable--see the last line of this code.

include('admin/misc.inc');
$cxn = mysqli_connect($host,$user,$passwd,$dbname)
or die ("couldn't connect to server");

$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);
$max_results = 20;
$from = (($page * $max_results) - $max_results);

$result = mysqli_query($cxn,"SELECT print_name FROM prints");

$total_results = mysqli_num_rows($result);

$total_pages = ceil($total_results / $max_results);
$pagination = '';
if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.'">Previous</a> ';
}

/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="index.php?page='.$i.'">$i</a>';
}
}
if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.'">Next</a>';
}

/* Now we have our pagination links in a variable($pagination) ready to print to the page. I put it in a variable because you may want to show them at the top and bottom of the page */

/* Below is how you query the db for ONLY the results for the current page */
$result= mysqli_query($cxn,"SELECT print_name FROM prints LIMIT $from, $max_results")
or die ("couldn't execute query.");

while ($i = mysqli_fetch_array($result))
{
/* This is where you print your current results to the page */

}

[linFox]

Apart from one typo
$pagination .= '<a href="index.php?page='.$i.'">'.$i.'</a>';
it's working fine for me.

Don't forget to actually echo the $pagination variable (see that first comment).

[TheKG]

Apart from one typo
$pagination .= '<a href="index.php?page='.$i.'">'.$i.'</a>';
it's working fine for me.

Don't forget to actually echo the $pagination variable (see that first comment).

OK...the incorrect code line explains why echo $pagination; wouldn't work. I correct the code and tried again. Must be something else I'm missing. All records show on one page even though the pagination links show at the beginning. Here's the page:

http://www.testsite.uniqueuniforms.com/Testphp.php

Thanks so much...hope you all know how much this helps!!

[pghcollectibles]

in your code you have &nbsp and they should be &nbsp;

[TheKG]

PGH -- thanks for the reply. I've been tweaking this code for hours. Below is the entire code. I've highlighted the two rows that I think may be the problem. My page displays with the pagination links correct, but does not display all the records and the subsequent pages are completely blank. http://www.testsite.uniqueuniforms.com/Testphp.php

The first while command is from the pagination code; the second while command is from the code I found to display my records in a 5 column table. I've tried to use only one or the other, but the results are even worse than what you'll see now. Read up on using array and assoc; found the both command and tried that too.

Appreciate any further assistance before I loose all my hair!

<?php
include('admin/misc.inc');
$cxn = mysqli_connect($host,$user,$passwd,$dbname)
or die ("couldn't connect to server");

$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);
$max_results = 20;
$from = (($page * $max_results) - $max_results);

$result = mysqli_query($cxn,"SELECT * FROM prints");

$total_results = mysqli_num_rows($result);

$total_pages = ceil($total_results / $max_results);
$pagination = '';
if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.'">Previous</a> ';
}

/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="index.php?page='.$i.'"><b>&nbsp;'.$i.'&nbsp;</b></a>';
}
}
if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.'">Next</a>';
}

/* Now we have our pagination links in a variable($pagination) ready to print to the page. I put it in a variable because you may want to show them at the top and bottom of the page */
/* Below is how you query the db for ONLY the results for the current page */

{
echo $pagination;
}

echo "<table border='0' cellpadding='10' cellspacing='0' width='100%'>";

$result= mysqli_query($cxn,"SELECT * FROM prints ORDER BY print_name LIMIT $from, $max_results");
$counter = 0;
while ($i = mysqli_fetch_array($result))
while($row = mysqli_fetch_assoc($result))


{
if($counter==0) print"<tr>";
++$counter;



echo "<td align='center'; valign='bottom'>
<a href='http://www.uniqueuniforms.com/{$row['order_url']}'
<font face='Comic Sans'; color='#990033'>
{$row['print_name']}\n
<img src='../images/CHE/{$row['image_url']}'
border='0' width='100' height='100'></a>
</td>";

if($counter==5)
{
$counter=0;
echo"</tr>";
}
}
while ($counter>0)
{
++$counter;
echo'<td>&nbsp;</td>';
if($counter==5)
{
$counter=0;
echo"</tr>";
}
}

echo "</table>\n";

{
echo $pagination;
}

?>

[pghcollectibles]

change your pagination href from index.php to Testphp.php

[TheKG]

Changed that line of code, but the results are the same. There are 70 records in this table with no blank rows. I've looked at the curly brackets too and changed them numerous times. Where can I get info on correct use of (), [] or {}?

[TheKG]

Changed all instances of index.php to Testphp.php. Still didn't get all the records.

[pghcollectibles]

you accidentally added an extra " when pasting your url:


<a href=""http://www.testsite.uniqueuniforms.com/Testphp.php?page=2"><b>&nbsp;2&nbsp;</b></a>

it should be:


<a href="Testphp.php?page=2"><b>&nbsp;2&nbsp;</b></a>

also there is no need for the full url. i will make it easier to move files without editing using relative urls

[TheKG]

you accidentally added an extra " when pasting your url:


<a href=""http://www.testsite.uniqueuniforms.com/Testphp.php?page=2"><b>&nbsp;2&nbsp;</b></a>

it should be:


<a href="Testphp.php?page=2"><b>&nbsp;2&nbsp;</b></a>

also there is no need for the full url. i will make it easier to move files without editing using relative urls

WOW am I a DUH or what? Corrected the quote issue and removed the full url. All four pages display, but they each drop the last record on the page and do not carry it over to the next.

[TheKG]

I have pinpointed that the FIRST record on each page is the one that is not displaying. Do I have code in the wrong place? I have looked through the db and cannot see that there are any blank records.

[pghcollectibles]

i wish i could help more with the mysqli stuff but i have gone over it a little and i determined im going to need more time to learn it. i can try to help with what i know of mysql. first let me show you how i do tables:


$numpicsrow="4";
$numrows="3";
$picspage=$numpicsrow * $numrows;

// code was here to put pictures into the array $picture[]

$numpics=count($picture);

$numpages=ceil($numpics / $picspage);
$page=filter_input(INPUT_GET, 'page', FILTER_SANITIZE_INT);
if ($page<=$numpages && $page>"0" && $page!=""){
$picnum=$page * $picspage;
} else {
$picnum=$picspage;
$page="1";
}
$next=$page+1;
$previous=$page-1;

// simple pagenation
echo "{$numpics}&nbsp;pictures found<br />";
if ($previous>"0"){
$pagenation.="&nbsp;<a href='index.php?page={$previous}' target='_top'>Page&nbsp;{$previous}</a>&nbsp;";
}
$pagenation.="&nbsp;Page&nbsp;{$page}&nbsp;";
if($next<=$numpages){
$pagenation.="&nbsp;<a href='index.php?page={$next}' target='_top'>Page&nbsp;{$next}</a>&nbsp;";
}
echo $pagenation;
//end pagination

echo "<table>";
$i="0";
$ii="0";
while($i<$numrows){
echo "<tr>";
while($ii<$numpicsrow){
echo "<td align='center' valign='middle' width='100px'>";
if($picnum > ($picnum-$numpicsrow)){
echo "<img border='0' width='100%' src='{$picture[$picnum-1]}' /><br />{$picture[$picnum-1]}";
$picnum--;
}else{
echo "&nbsp;";//this will put a blank space in the datacell if there is no picture
}
echo "</td>";
$ii++;
}
echo "</tr>";
$i++;
$ii="0";
}
echo "</table>";
?>

ill be back with some mysql code to pull data if you already know how to use the mysqli then hopefully you can adapt it to yours

[pghcollectibles]

one thing i found was you missed the closing bracket > on the beggining of the anchor.

anyway... this would work if you used mysql or change this into mysqli:


<?php
$con = mysql_connect($host,$user,$passwd) or die ("couldn't connect to server" . mysql_error());
mysql_select_db($dbname, $con);
$result = mysql_query("SELECT * FROM prints",$con) or die("problem with first query" . mysql_error());
$total_results = mysql_num_rows($result);//using mysqli i believe this value may allready defined as $d after the query check with php.net
$result= mysql_query("SELECT * FROM prints ORDER BY print_name LIMIT {$from}, $max_results",$con) or die ("problem with second query" . mysql_error());

//your pagenation was here

$numrows=5;
$numcols=4;
$i=0;//initialize row
$ii=0;//initialize column

echo $pagination;

echo "<table border='0' cellpadding='10' cellspacing='0' width='100%'><tbody font face='Comic Sans'; color='#990033'>";
//with the tbody you wont have to repeat the <font> however, look into using css

while($row = mysql_fetch_array($result))
while ($ii<$numcols){
echo "<tr>";
while ($i<$numrows){
echo "<td align='center'; valign='bottom'>";
echo "<a href='http://www.uniqueuniforms.com/{$row['order_url']}'>";
echo "{$row['print_name']}\n<img src='../images/CHE/{$row['image_url']}' border='0' width='100' height='100'>";
echo "</a></td>";
$i++;
}
echo "</tr>";
$ii++;
}
}

echo "</tbody></table>\n";

echo $pagination;
?>

[TheKG]

Copied your code to a page and tried it--won't recognize the mysql. It'll take some time, but I'm working on changing it to something mysqli likes. Not sure which bracket you are referring to. Looked over the code for open/closed brackets and can't pinpoint one that is left open; unless it's open until the end and should be closed earlier in the code.

Thank you so much for helping!

[pghcollectibles]

i just saw a bunch of them i think i saw a

{
echo "....
}

you dont need that. they are for groups of coding: for, while, if, function etc... also concatenation look at php.net for syntax

also you do not have to use the static url address for example, if this code is in a file in the public_html folder:

echo "<a href='http://www.uniqueuniforms.com/{$row['order_url']}'>";

you could use the relative address and it will make it easier for you later if you move or rename folders:

echo "<a href='{$row['order_url']}'>";

[TheKG]

OK...removed the excess {} brackets. As for the static url; I only did that because this is a testing directory. The pages I want the pics linked to are in the live web (pages designed with Front Page..gasp!!). Once I have a better handle on php and mysqli I intend to redo the entire site and will insert the full url in the db. Thanks for the advice. Still working on the code you sent.

[TheKG]

Here's the latest. See the results at http://www.testsite.uniqueuniforms.com/test2.php. What is displayed on each page is only the first record for each page of 20. That's why in my original code I used the while ($row = mysqli_fetch_assoc($result)). Tried using this in your code below and each page then returned only the second record for each page of 20.

Hope this gives you better information.





include('admin/misc.inc');

$cxn = mysqli_connect($host,$user,$passwd,$dbname) or die ("couldn't connect to server" . mysqli_error());

$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);
$max_results = 20;
$from = (($page * $max_results) - $max_results);

$result = mysqli_query($cxn,"SELECT * FROM prints");

$total_results = mysqli_num_rows($result);

$total_pages = ceil($total_results / $max_results);
$pagination = '';
if($page > 1)
{
$pagination .= '<a href="test2.php?page='.$prev.'">Previous</a> ';
}

/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="test2.php?page='.$i.'"><b>&nbsp;'.$i.'&nbsp;</b></a>';
}
}
if($page < $total_pages)
{
$pagination .= '<a href="test2.php?page='.$next.'">Next</a>';
}




$result = mysqli_query($cxn,"SELECT * FROM prints") or die("problem with first query" . mysqli_error());
$total_results = mysqli_num_rows($result);//using mysqli i believe this value may allready defined as $d after the query check with php.net
$result= mysqli_query($cxn,"SELECT * FROM prints ORDER BY print_name LIMIT $from, $max_results") or die ("problem with second query" . mysqli_error());


//your pagenation was here

echo $pagination;
$numrows=5;
$numcols=4;
$i=0;//initialize row
$ii=0;//initialize column

echo "<table border='0' cellpadding='10' cellspacing='0' width='100%'><tbody font face='Comic Sans'; color='#990033'>";
//with the tbody you wont have to repeat the <font> however, look into using css

while($row = mysqli_fetch_array($result))

while ($ii<$numcols){
echo "<tr>";
while ($i<$numrows){
echo "<td align='center'; valign='bottom'>";
echo "<a href='http://www.uniqueuniforms.com/{$row['order_url']}'>";
echo "{$row['print_name']}\n<img src='../images/CHE/{$row['image_url']}' border='0' width='100' height='100'>";
echo "</a></td>";
$i++;
}
echo "</tr>";
$ii++;
}


echo "</tbody></table>\n";

echo $pagination;

[pghcollectibles]

i feel dumb looking at the code now of course it would do that as i only pulled the data one time for the whole table plus i forgot to reset $i to 0 after the row finished. if you look at the page source, it shows there are four rows but the last three dont have any data cells in them. ill redo this and get back in a little bit. sorry about that

[pghcollectibles]

something feels wrong but try this:



while ($ii<$numcols){
echo "<tr>";
while ($i<$numrows){
echo "<td align='center'; valign='bottom'>";
while($row = mysqli_fetch_array($result))
echo "<a href='http://www.uniqueuniforms.com/{$row['order_url']}'>";
echo "{$row['print_name']}\n<img src='../images/CHE/{$row['image_url']}' border='0' width='100' height='100'>";
echo "</a>";
}
echo "</td>";
$i++;
}
echo "</tr>";
$i=0;
$ii++;
}
and if that dont work, ill just make a database and test it myself before giving you more code. hopefully this is a learning experience for you. debugging usually ends up teaching you something unless its just typos, then it just tells you to pay attention.

[TheKG]

Tried this code, but I only see 2 open brackets and 3 closed. What am I missing? And, by the way, you're right about this being a learning experience! How many ibuprofen does it take to get rid of the headache? I'll let you know when I get to the magic number (LOL)!

[TheKG]

At first, using the code you provided, I got a blank page. Moving some brackets around I would get the image markers, but no images. When I viewed source, the image source files were just blanks. So, I edited the code just a little and had the brackets here, there and everywhere..then nowhere and now here it is. The page now displays all 20 records, but in one row instead of 4 rows of 5 records each. I can only hope this is a good sign!



while ($ii<$numcols)
{
echo "<tr>";
while ($i<$numrows)
{
while($row = mysqli_fetch_array($result))

echo "<td align='center'; valign='bottom'>
<a href='http://www.uniqueuniforms.com/{$row['order_url']}'
<font face='Comic Sans'; color='#990033'>
{$row['print_name']}\n
<img src='../images/CHE/{$row['image_url']}'
border='0' width='100' height='100'></a>
</td>";

echo "</tr>";

$i++;
}


{
$i=0;
$ii++;
}
}

[pghcollectibles]

you dont need the colons inside the tags like that

WOW its starting to look clearer now... i was using while loops because im used to looping stuff now and i should have used if because i wanted whatever was going to output to only do it foreach $row.

this should work:



$i=0;
$ii=0
$trout=0;
echo "<table border='0' cellpadding='10' cellspacing='0'>";
while($row = mysqli_fetch_array($result)) {
if ($ii<$numrows) {
if ($trout==0){
echo "<tr>";
$trout=1;
}
if ($i<$numcols){
echo "<td align='center' valign='bottom'>";
echo "<a href='http://www.uniqueuniforms.com/{$row['order_url']}'>";
echo "<font face='Comic Sans' color='#990033'>";
echo "{$row['print_name']}\n";
echo "<img src='../images/CHE/{$row['image_url']}' border='0' width='100' height='100'>";
echo "</a></td>";
$i++;
}else{
echo "</tr>";
$i=0;
$trout=0;
$ii++;
}
}
}
echo "</table>";

[TheKG]

OK...getting closer. This displays only 4 columns instead of 5. The info that should be in the 5th column is simply missing. Tried to change the $numcols=4 to $numcols=5, but then it skips every 6th record. Also, since max_results=20 is defined in pagination, is "$numrows=5" necessary?

[pghcollectibles]

$numrows is how you know how many rows <tr> of the table to echo

ok i think something is up with the query.

change this line:
while($row = mysqli_fetch_array($result)) {
to this:
$loops=0;
while($row = mysqli_fetch_array($result)) {
$loops++;

after this:
echo "</table>";
add this:
echo $loops;then post back with the results

[TheKG]

Still skips every 6th record throughout. Look at the pagination at the bottom of the page. There's a number 20 to the left...echoed the loops.

[pghcollectibles]

change this:

}else{
echo "</tr>";
$i=0;
$trout=0;
$ii++;
}
to this:

}
$endtr=$numcols-1;
if ($i==$endtr){
echo "</tr>";
$i=0;
$trout=0;
$ii++;
}


and you can get rid of the $loop info i had you put in. the reason was: while $row is the 6th item, it went through the else condition and advanced to the next $row

[TheKG]

With the newest changes made, was only displaying 4 columns. I changed the $numcols=5 to 6. Displays perfectly!!!!!!!!!!!!!!! Probably a better way to do it, but this one worked.

Here's the code I now have:


$numcols=6;
$numrows=5;
$i=0;
$ii=0;
$trout=0;
echo "<table border='0' cellpadding='10' cellspacing='0'>";
while($row = mysqli_fetch_array($result)) {
if ($ii<$numrows) {
if ($trout==0){
echo "<tr>";
$trout=1;
}
if ($i<$numcols){
echo "<td align='center' valign='bottom'>";
echo "<a href='http://www.uniqueuniforms.com/{$row['order_url']}'>";
echo "<font face='Comic Sans' color='#990033'>";
echo "{$row['print_name']}\n";
echo "<img src='../images/CHE/{$row['image_url']}' border='0' width='100' height='100'>";
echo "</a></td>";
$i++;
}
$endtr=$numcols-1;
if ($i==$endtr){
echo "</tr>";
$i=0;
$trout=0;
$ii++;
}
}
}
echo "</table>";

Thank you for staying with this. I appreciate the dedication!!

[pghcollectibles]

good im glad its working. now if i could just get this fixture list to work