JPark Posted June 9, 2009 Share Posted June 9, 2009 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 Link to comment https://forums.phpfreaks.com/topic/161505-pagination/ Share on other sites More sharing options...
Garethp Posted June 9, 2009 Share Posted June 9, 2009 Well where's your SQL? Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852278 Share on other sites More sharing options...
JPark Posted June 9, 2009 Author Share Posted June 9, 2009 Well where's your SQL? ?? I'm a bit of a newbie... All I have on the page is php and a little html. Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852306 Share on other sites More sharing options...
scvinodkumar Posted June 9, 2009 Share Posted June 9, 2009 hello, print your mysql query here Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852309 Share on other sites More sharing options...
JPark Posted June 9, 2009 Author Share Posted June 9, 2009 $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? Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852324 Share on other sites More sharing options...
scvinodkumar Posted June 9, 2009 Share Posted June 9, 2009 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 Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852327 Share on other sites More sharing options...
JPark Posted June 9, 2009 Author Share Posted June 9, 2009 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! Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852351 Share on other sites More sharing options...
scvinodkumar Posted June 9, 2009 Share Posted June 9, 2009 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']; Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852368 Share on other sites More sharing options...
JPark Posted June 9, 2009 Author Share Posted June 9, 2009 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 Link to comment https://forums.phpfreaks.com/topic/161505-pagination/#findComment-852508 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.