Page 1 of 1

Pagination

Posted: Sun Oct 03, 2004 1:59 am
by StrykstaGuy
feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I have this code that searches my database and returns the results in sections of 5 and a  previous and next to view the rest, everything works fine except for after you click next and view the second set. (&Result_Set=5) when i click next on it it stays (&Result_Set=5) instead of (&Result_Set=10). i guess it is not adding properly, can anyone help?
here is the code:

Code: Select all

<? 
// Set Script Variables 
$DB_Host="address"; 
$DB_Name="test"; 
$DB_User="test"; 
$DB_Pass="password"; 
$Per_Page=5; 

// Open MySQL Connection 
$Connection = mysql_connect($DB_Host, $DB_User, $DB_Pass); 
mysql_select_db($DB_Name, $Connection); 

if ($_GET['Result_Set']) 
{ 
    $Result_Set=$_GET['Result_Set']; 
} 
else 
{ 
    $Result_Set = 0; 
} 

$SQL = "SELECT * FROM contact WHERE firsts LIKE '%".$_REQUEST['Keyword']."%' LIMIT $Result_Set, $Per_Page"; 
$Result = mysql_query($SQL);
$Total = mysql_num_rows($Result); 

// Run The Query Without a Limit to get Total result 
$SQL = "SELECT * FROM contact WHERE firsts LIKE '%".$_REQUEST['Keyword']."%' LIMIT $Result_Set, $Per_Page"; 
$SQL_Result = mysql_query($SQL); 

while ($SQL_Result_Array = mysql_fetch_array($SQL_Result)) 
{ 
    $Product = $SQL_Result_Array['first']; 
    $Description = $SQL_Result_Array['last']; 
    echo "$Product - $Description<BR><BR>"; 
} 

$Result_Set2 = $Result_Set; 

if ($Result_Set > 0) 
{ 
    //Previous button 
    $Result_Set = $Result_Set - $Per_Page; 
    if ($Result_Set < 0) 
    { 
        $Result_Set = 0; 
    } 
    echo '<a href="?Keyword=' . $_REQUEST['Keyword'] . '&Result_Set=' . $Result_Set . '">Previous Page</a> '; 
} 

if ($Result_Set2 <= $Total) 
{ 
    //Next button 
    $Result_Set2 = $Result_Set2 + $Per_Page; 
    if ($Result_Set2 > $Total) 
    { 
        $Result_Set2 = $Total; 
    } 
    echo '<a href="?Keyword=' . $_REQUEST['Keyword'] . '&Result_Set=' . $Result_Set2 . '">Next Page</a> '; 
} 
?>

feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Sun Oct 03, 2004 2:19 am
by feyd
$Total will not be larger than $Per_Page. In order to know if there are more results possible in the result set, you can select without the limit, to give you the total, and then another with the limit added. Or just use the first (non-limited) result set.

This can be displayed by skipping over the results for previous pages with a loop of mysql_fetch_* or a call to [php_man]mysql_data_seek[/php_man]()

Some additional tips:
  • use [php_man]urlencode[/php_man]() on your keyword as you insert it into the href.
  • use [php_man]isset[/php_man]() and [php_man]is_numeric[/php_man]() on the Result_set url parameter when you are examining if it's set.
  • don't use the keyword variable directly in your query, as it could have some [google]sql injection[/google] in it.
  • if and when you get no sql output, modify your query calls to something similar to:

    Code: Select all

    $result = mysql_query($sql) or die(mysql_error());

Posted: Sun Oct 03, 2004 3:35 am
by Christopher
I'd recommend something more like this:

Code: Select all

$Result_Set = intval($_GET['Result_Set']);
if ($Result_Set < 1) { 
    $Result_Set=$_GET['Result_Set']; 
} else { 
    $Result_Set = 0; 
} 
$keyword = preg_replace('/[^a-zA-Z\ ]/', '', $_REQUEST['Keyword']);

$SQL = "SELECT COUNT(*) FROM contact WHERE firsts LIKE '%$keyword%"; 
$Result = mysql_query($SQL);
$row = mysql_fetch_row($Result); 
$Total = $row[0];
Also, the obvious optimization is to save $Total in the session so you only have to do that query the first time.