search engine results pagination

[cindreta]

So i have worked with pagination before but on simpler and smaller query's, this time i have a really long and huge search query and i don't wanna use it twice (once to count the results without the limit and then again the same query with a limit). I don't know how to use only one main query and somehow get the totalnumber of results and limit and everything.  if you get what i am talking about, i can post more info and the code if needed.

 

thank you hope someone helps ???

[.josh]

post your query.  I see no reason why you can't put the limit on there.  That shouldn't interfere with whatever count you are doing in the query...the limit just returns x amount of rows with an offset (if specified), after everything is said and done.  so, whatever query you're doing, it will internally select whatever the condition dictates, etc.. but will actually give you just the limit at offset from that result.

[cindreta]

Thank you for a fast replay. here is the deal.

first i don't know if i can put COUNT () into this search query, plus because i wanna work with only one query how do i get the actual $offset value, because i have to first have the number of total results in order to get the offset, when i worked with  smaller query's i just put first query like this:

$sql_pagin = "SELECT COUNT(id) FROM table";

i get my variables then from here like totalnumber of rows, offset and all and then i do the second query with the limit and the offset value

SELECT a lot of stuff here FROM table WHERE a lot of conditions GROUP BY id LIMIT $offset, $rowsperpage

 

so how do i have only one query for search and get the offset number, have the count and all?

[.josh]

okay maybe I'm misunderstanding you, but you don't want to base your pagination off of the total rows in your table unless you are wanting to display all of the rows in your table (which I don't think you're wanting to do...hence the big long query full of conditions).

 

You have conditions in your query to return only x amount of rows from your table, and the goal is to paginate just those results.  So for instance, if you have a total of 500 rows in your table, and your conditions select 100 of those rows, you want to paginate based off those 100 (for example, 10 pages of 10 rows), not based off of all of your rows (50 pages of 10 rows). 

 

So you would still use COUNT in your query and base the total pages off of that count, because that count will return how many rows were selected by the query.

[cindreta]

ok so i kinda devided the sql code into two parts, one gets all the results and then later on it puts a limit to it... i used the phpfreaks tutorial for pagination.

BUT I HAVE ONE PROBLEM, if you do a search for something and it turns out to have per say 13 results, and my limit per page is 10, whenever you click on a page number that resets, i guess it losses the search string value and just displays the whole database as a result. So how do i fix it, that i have that value of the search trough the whole pagaination process.

 

1. my sql search code:

//SEARCH
$q = $_POST['inputString'];
$trimmed = trim($q); //trimaj
$sql = "SELECT some stuff from table then a few joins
WHERE name LIKE \"%$trimmed%\" OR lasname LIKE \"%$trimmed%\" GROUP BY idx";
$numresults = mysql_query ($sql) or die ("Couldn't execute query: Reason; ".mysql_error());
$row_num_links_main =mysql_num_rows ($numresults);

2. and then i put the first paggination code

//results per page
$rowsperpage = 10;
$totalpages = ceil($row_num_links_main / $rowsperpage);
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
$currentpage = (int) $_GET['currentpage'];
} else {
$currentpage = 1;
} 
if ($currentpage > $totalpages) {
$currentpage = $totalpages;
}
if ($currentpage < 1) {
$currentpage = 1;
} 
$offset = ($currentpage - 1) * $rowsperpage;
$sql .= " LIMIT $offset,$rowsperpage" ; // continue the sql query with a limit

3. finish the pagination links

// range
$range = 10;
if ($currentpage > 1) {
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1' title='first'>first</a> ";
$prevpage = $currentpage - 1;

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage' title='previous'>previous</a> ";
}
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
if (($x > 0) && ($x <= $totalpages)) {
if ($x == $currentpage) {
echo " [<strong>$x</strong>] ";

} else {
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
} 
} 
} 
if ($currentpage != $totalpages) {
$nextpage = $currentpage + 1;
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage' title='next'>next</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages' title='last'>last</a> ";
}