Pagination question

Discussion in 'PHP' started by Lotos1, Mar 17, 2010.

0
  1. #1
    I found this tutorial http://www.w3schools.com/PHP/php_ajax_database.asp that lets me use Ajax with MySQL to retrieve data based on the selected value from a drop-down list. It works well, but sometimes when I retrieve data by selecting a value from the drop-down list, there are too many records displayed on the page. How can I add pagination to this script so that when I retrieve more than a certain number of records, I get pagination on the bottom to flip through pages?
     
    Lotos1, Mar 17, 2010 IP
  2. bozghiyy

    bozghiyy Peon

    Messages:
    19
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Hello,

    I made some changes , they are not the best , but I made it very quick - what came to my mind!

    so... the first page (i called it index.php)
    
<html>
<head>
<script type="text/javascript" src="selectuser.js"></script>
</head>
<body>

<form>
Select a User:
<select name="users" onchange="showUser(this.value,'0')">
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Glenn Quagmire</option>
<option value="4">Joseph Swanson</option>
</select>
</form>
<br />
<div id="txtHint"><b>Person info will be listed here.</b></div>

</body>
</html>
    PHP:
    - i changed the javascript function -> added another param (page), so you have now
    selectuser.js
    
var xmlhttp;

function showUser(str,page)
{
xmlhttp=GetXmlHttpObject();
if (xmlhttp==null)
  {
  alert ("Browser does not support HTTP Request");
  return;
  }
var url="getuser.php";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
//added page variable to send to the http request
url=url+"&page="+page;
xmlhttp.onreadystatechange=stateChanged;
xmlhttp.open("GET",url,true);
xmlhttp.send(null);
}

function stateChanged()
{
if (xmlhttp.readyState==4)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}

function GetXmlHttpObject()
{
if (window.XMLHttpRequest)
  {
  // code for IE7+, Firefox, Chrome, Opera, Safari
  return new XMLHttpRequest();
  }
if (window.ActiveXObject)
  {
  // code for IE6, IE5
  return new ActiveXObject("Microsoft.XMLHTTP");
  }
return null;
}
    Code (markup):
    and getuser.php

    
<?php
$q=$_GET["q"];

// added for pagination
$page=$_GET["page"];
$fperpage = 5; // change the number of results per page
$limit = $page * $fperpage;

$con = mysql_connect('localhost', 'root', ''); // here complete with your DB username and password
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("dp", $con); // here change with your database name

// i think you will have another query here , but don't forget LIMIT $limit,$fperpage
$sql="SELECT * FROM user WHERE 1 LIMIT $limit,$fperpage";

$result = mysql_query($sql);


echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "<td>" . $row['Age'] . "</td>";
  echo "<td>" . $row['Hometown'] . "</td>";
  echo "<td>" . $row['Job'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

// added another query , is needed for geting the count of all fields
$sql=str_replace(" LIMIT $limit,$fperpage",'',$sql);

$result = mysql_query($sql);
$num = mysql_num_rows($result);


mysql_close($con);

$nrpage = ceil($num / $fperpage);
$i = 0;

while($i < $nrpage){
		echo "<a href='#' onclick='showUser($q,$i);return false;'>".($i+1)."</a> &nbsp;&nbsp;&nbsp;";
		$i++;
		}
?>
    PHP:
    here I made some changes, i tried to comment on each one!

    Hope that helps you.
     
    bozghiyy, Mar 18, 2010 IP
  3. Lotos1

    Lotos1 Peon

    Messages:
    454
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I did what you said there, but I get page links and clicking on them does nothing. To the links <a href='#' onclick='showUser($q,$i);return false;'> in getuser.php I added onclick and also in the selectuser.js I added the page parameter called 'page' to know which page to retrieve. On the index.php page I added the page parameter to the onchange call to showUser(this.value,'0'). Clicking on the page links does not give any errors and page 1 is always selected and I cannot select the other pages as the links seem not to do anything when clicked. Did you test the code above and can you confirm that it works on your side?
     
    Lotos1, Mar 18, 2010 IP
  4. Lotos1

    Lotos1 Peon

    Messages:
    454
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I think that the page links don't work because getuser.php file does not have the definition of showUser(). Even though index.php includes selectuser.js where the showUser() function is located, getuser.php itself does not. Now, my question is, how can I include selectuser.js in getuser.php file which by itself is just a php script without any html elements?
     
    Lotos1, Mar 19, 2010 IP