Pagination

[JPark]

I have a page that queries the mysql database.  Some of the queries return a large number of entries so I am trying to set up some pagination.  However, I am running into an error.

 

Here is the code that is giving me the error:

//look for starting marker
//if not available, assume 0
if (!$_GET['start']) 
$start=0;
else 
$start = $_GET['$start'];

 

And the Next Page and Previous Page links are:

// set up the previous page link
// this should appear on all pages except the first page
// the start point for the preious page will be
// the start point for this page
// less the number of records per page

echo "<br /><br />";
$next= ($start+$records_per_page);
echo "start= ".$start." Records per page= ".$records_per_page." Next= ".$next;
echo "<br /><br />";
$previous= ($start+$records_per_page);
echo "previous = ".$previous." Total records =".$total_records;
echo "<br /><br />";
echo "<a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start-$records_per_page) . ">Previous Page</a>";
echo "  ";
echo "<a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start+$records_per_page) . ">Next Page</a>";
$start=($start+$records_per_page);
echo "<br />";
echo "Start= ".$start;
echo "<br /><br />";

if ($start >= $records_per_page)
{
echo "<a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start-$records_per_page) . ">Previous
Page</a>     ";
echo "Previous";

}
// set up the "next page" link
// this should appear on all pages except the last page
// the start point for the next page
// will be the end point for this page
if ((($start+$records_per_page) < $total_records) && ($start > 0)) {

echo "<a href=" . $_SERVER['PHP_SELF'] . "?start=" . ($start+$records_per_page) .
">Next Page</a>";
echo "Next";
}

 

The Next Page and Previous Page links come out fine; for example http://www.mywebsite.com/pagination.php?start=5

 

However, the error I am getting is:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5' at line 2

 

Any ideas?

 

Thanks!

 

Joe

[Garethp]

Well where's your SQL?

[JPark]

Well where's your SQL?

??  I'm a bit of a newbie...  All I have on the page is php and a little html.

[scvinodkumar]

hello, print your mysql query here

[JPark]

$records_per_page = 5;

//open connection to MySQL server
$connection= mysql_connect($host, $user, $password) 
or die ('Unable to Connect');

//select database
mysql_select_db($database) or die ('Unable to select database');

// create and execute query to count available records
$query= "SELECT COUNT(*) FROM shirts WHERE sex='MEN'";
$result= mysql_query($query) or die ('Error in query: $query.'.mysql_error());

//get the total number of rows
$row= mysql_fetch_row($result);
$total_records= $row[0];

//if records exist
if (($total_records > 0) && ($start < $total_records)) {
// Retrieve all the men's shirt data from the "shirts" table
$result = mysql_query("SELECT * FROM shirts WHERE sex='MEN' 
&& shirtType='Ringer T-Shirt' LIMIT $start,$records_per_page")
or die(mysql_error());  

 

Is that enough/what you need?

[scvinodkumar]

can u echo this query for me

echo "SELECT * FROM shirts WHERE sex='MEN' && shirtType='Ringer T-Shirt' LIMIT $start,$records_per_page";

 

put this query after "// Retrieve all the men's..."

 

i think the limit will be problem,

 

or $start=0 add this line at the top

 

[JPark]

Ok, I see where the problem is.  I don't yet see how to correct it.

 

On the first page, I get

SELECT * FROM shirts WHERE sex='MEN' && shirtType='Ringer T-Shirt' LIMIT 0,5

 

But, if I type in the address bar "http://www.mywebpage/pagination?start=5" (as the next page should be), I get:

SELECT * FROM shirts WHERE sex='MEN' && shirtType='Ringer T-Shirt' LIMIT ,5You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5' at line 2

 

So, my problem is in the calculation of the variable $start.  I am currently using

//look for starting marker
//if not available, assume 0
if (!$_GET['start']) 
	$start=0;
else 
	$start = $_GET['$start'];

 

What is a better way -- i.e. a way that works?

 

Thanks!

[scvinodkumar]

use this code, you have used $start (it treat as variable), this is wrong, you should use 'start'

 

//look for starting marker
//if not available, assume 0
if (!$_GET['start'])
  $start=0;
else
  $start = $_GET['start'];

 

[JPark]

Thanks!  It works now.

 

The last problem is that I have 21 records in this query.  However, I am limiting by 5.  When I get to the last screen (?start=20), I get nothing.  I assume it's because I cannot pull another increment of 5 as only one record remains.

 

Solution?  Suggestion?

 

Thanks,

 

Joe