Gnub Posted March 24, 2007 Share Posted March 24, 2007 I've found a tutorial on pagination, seems like a decent tutorial. However, all the tutorials i have seen on this subject, always use the "Select * FROM <table>". My queries almost always have a Where statement inbedded into it. How can i use my queries while still maintaining the tutorials guide, example of where my problem lies is below: $result = mysql_query("SELECT count(*) FROM news_table"); $total = mysql_result($result, 0); $news = mysql_query("SELECT * FROM news_table ORDER BY date_field ASC LIMIT $start, $display"); My problem lies with the $result part of the code. I have a table holding over 40-50k rows. which is why i dont want the select * from table. thanks in advance Gnub Quote Link to comment Share on other sites More sharing options...
Hell Toupee Posted March 24, 2007 Share Posted March 24, 2007 Just adjust it, and add the WHERE in yourself? If you don't want to select all the fields use "Select field1,field2,field3" instead of "Select *". "Select *" means SELECT ALL fields for each record, not select all the rows so whether or not there are 40,000 rows makes no difference, that's where the LIMIT matters. Hope you understand that, feel free to paste your query with all its conditions if you don't. Quote Link to comment Share on other sites More sharing options...
Gnub Posted March 24, 2007 Author Share Posted March 24, 2007 Ok, i've tried to do this on my own. But im now stumped. All my results are being shown on page 1, and are being repeated on every consecutive page. Help ? $db = mysql_connect("<>", "<>", "<>"); mysql_select_db("<>",$db); if (isset($_GET['pageno'])) { $pageno = $_GET['pageno']; } else { $pageno = 1; } $query = "SELECT count(`Teletext`.*) DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC"; $result1 = mysql_query($sql, $db) or trigger_error("SQL", E_USER_ERROR); $query_data = mysql_fetch_row($result1); $numrows = $query_data[0]; $rows_per_page = 5; $lastpage = ceil($numrows/$rows_per_page); $pageno = (int)$pageno; if ($pageno < 1) { $pageno = 1; } elseif ($pageno > $lastpage) { $pageno = $lastpage; } $result = mysql_query($sql,$db) or die(mysql_error() . '<br />'. $query); if (mysql_num_rows($result) >0) { while($row = mysql_fetch_array($result)) { ***Results*** } if ($pageno == 1) { echo " FIRST PREV "; } else { echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> "; $prevpage = $pageno-1; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> "; } echo " ( Page $pageno of $lastpage ) "; if ($pageno == $lastpage) { echo " NEXT LAST "; } else { $nextpage = $pageno+1; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> "; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> "; } } ?> Quote Link to comment Share on other sites More sharing options...
Hell Toupee Posted March 24, 2007 Share Posted March 24, 2007 $db = mysql_connect("<>", "<>", "<>"); mysql_select_db("<>",$db); if (isset($_GET['pageno'])) { $pageno = $_GET['pageno']; } else { $pageno = 1; } $result1 = mysql_query($sql, $db) or trigger_error("SQL", E_USER_ERROR); $query_data = mysql_fetch_row($result1); $numrows = $query_data[0]; $rows_per_page = 5; $lastpage = ceil($numrows/$rows_per_page); $pageno = (int)$pageno; if ($pageno < 1) { $pageno = 1; } elseif ($pageno > $lastpage) { $pageno = $lastpage; }]] $start=(($pageno-1)*$rows_per_page); $display=$start+($rows_per_page-1); $query = "SELECT count(`Teletext`.*) DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC LIMIT $start,$display"; $result = mysql_query($sql,$db) or die(mysql_error() . '<br />'. $query); if (mysql_num_rows($result) >0) { while($row = mysql_fetch_array($result)) { ***Results*** } if ($pageno == 1) { echo " FIRST PREV "; } else { echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> "; $prevpage = $pageno-1; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> "; } echo " ( Page $pageno of $lastpage ) "; if ($pageno == $lastpage) { echo " NEXT LAST "; } else { $nextpage = $pageno+1; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> "; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> "; } } ?> The stuff I've changed is in red. I moved the query down, so that the variables the pagination system needs are declared before the query is declared, so it can use them. Notice the "LIMIT $start,$display" that has been added, aswell as the start and display variables themselves, if you need help understanding the math involved or if it doesn't work, feel free to ask again, hope it works! Quote Link to comment Share on other sites More sharing options...
Gnub Posted March 24, 2007 Author Share Posted March 24, 2007 No change in results, still getting all results on 1 page, and every subsequent page after. Quote Link to comment Share on other sites More sharing options...
Hell Toupee Posted March 24, 2007 Share Posted March 24, 2007 Where is $sql defined? That's the query that you're running, and that's the query that needs to have LIMITs on it, not $query, sorry I didn't see that. Quote Link to comment Share on other sites More sharing options...
Gnub Posted April 2, 2007 Author Share Posted April 2, 2007 Sorry for bringing this back from the dead, but im sure some of you know, that sometimes you need to drop what your doing, and start something that's needed urgently. What i get now: I get the first 25 of the Query, with the limiter, and offset. The number of pages, that is listed on the bottom "First, Prev, 1,2,3,4,5 Next, Last" is correct. But when i click on any of the links, the results change to the first 25 of all of the data in the table. At the very beginning of the script i have the form Post variables, could they be messing up with my queries, which are generated based on the variables at the beginning? if ($HotelName =="") { $limit = 25; $tsql1 = "Select `Teletext`.*, DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC;"; $result_count = mysql_query($tsql1); $totalrows = mysql_num_rows($result_count); if(empty($page)){ $page = 1; } $limitvalue = $page * $limit - ($limit); $tsql = "Select `Teletext`.*, DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC LIMIT $limitvalue, $limit;"; } if ($HotelName !=="") { $tsql = "Select `Teletext`.*, DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE `AccName` LIKE '%$HotelName%' Order BY Price ASC LIMIT $limitvalue, $limit;"; } $sql = $tsql; $result = mysql_query($sql) or die("Error: " . mysql_error()); <<Results Posted>> if($page != 1){ $pageprev = $page--; echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a> "); }else{ echo("PREV".$limit." "); } $numofpages = $totalrows / $limit; for($i = 1; $i <= $numofpages; $i++){ if($i == $page){ echo($i." "); }else{ echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); } } if(($totalrows % $limit) != 0){ if($i == $page){ echo($i." "); }else{ echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); } } if(($totalrows - ($limit * $page)) > 0){ $pagenext = $page++; echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>"); }else{ echo("NEXT".$limit); } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.