Page 1 of 1
Help displaying data
Posted: Tue Nov 18, 2008 3:16 pm
by cjswanson1355
I am trying to get this loop to go through a set of data and display it to the user in increments of 5. I want the user to hit a button to continue to the next set of data until everything has been parsed through. My problem is I have no idea what library in PHP I can use to accomplish this. Here is my code:
Code: Select all
<html>
<head>
<title>Messaging results</title>
</head>
<h1>Messaging results</h1>
<?php
$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm'];
echo $searchterm;
if (!get_magic_quotes_gpc())
{
$searchtype = addslashes($searchtype);
}
@ $db = new mysqli('localhost', '*****', '****', 'messages');
if (mysqli_connect_errno())
{
echo 'Error: Coult not connect to the database. Please try again later.';
exit;
}
$query = "select * from messages where $searchtype = '$searchterm'";
$result = $db->query($query);
$num_results = $result->num_rows;
echo '<p>Messages found: '.$num_results.'</p>';
for($i = 0; $i < $num_results; $i++)
{
$row = $result->fetch_assoc();
echo '<p><strong>'.($i+1).'.Name: ';
echo $row['Name'];
echo '<p></strong><br />Date and Time: ';
echo $row['Date']." at ";
echo $row['Time'];
echo '<p>Recipient: ';
echo $row['Recipient'];
echo '<p>Message: ';
echo $row['Message'];
}
?>
</html>
right now I am just trying to understand the functionality until I put it into my real program. I would appreciate any help.
Re: Help displaying data
Posted: Tue Nov 18, 2008 3:17 pm
by Syntac
Hopefully that's not your real username/password...
Re: Help displaying data
Posted: Tue Nov 18, 2008 7:53 pm
by califdon
What you described is called pagination. Use that word in searching here and you will find literally hundreds of comments about it, or use the words php pagination in Google and you will find lots of tutorials.
Re: Help displaying data
Posted: Thu Nov 20, 2008 4:28 pm
by cjswanson1355
Ok, I set up pagination for my database, but for some reason the data isn't being displayed in the while loop at the very end. Can someone give me a hint as to what I am doing wrong?
Code: Select all
<?php
$dbhost = 'localhost';
$dbuser = '********';
$dbpass = '************';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'messages';
mysql_select_db($dbname);
$tbl_name="messages"; //your table name
// How many adjacent pages should be shown on each side?
$adjacents = 3;
$query = "select * from messages where $searchtype = '$searchterm'";
$num_results = $result->num_rows;
$query = "SELECT COUNT(*) as num FROM $tbl_name";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];
/* Setup vars for query. */
$targetpage = "pagination.php"; //your file name (the name of this file)
$limit = 5; //how many items to show per page
$page = $_GET['page'];
if($page)
$start = ($page - 1) * $limit; //first item to display on this page
else
$start = 0; //if no page var is given, set start to 0
/* Get data. */
$sql = "SELECT Name FROM $tbl_name LIMIT $start, $limit";
$result = mysql_query($sql);
/* Setup page vars for display. */
if ($page == 0) $page = 1; //if no page var is given, default to 1.
$prev = $page - 1; //previous page is page - 1
$next = $page + 1; //next page is page + 1
$lastpage = ceil($total_pages/$limit); //lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1; //last page minus 1
/*
Now we apply our rules and draw the pagination object.
We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<div class=\"pagination\">";
//previous button
if ($page > 1)
$pagination.= "<a href=\"$targetpage?page=$prev\">« previous</a>";
else
$pagination.= "<span class=\"disabled\">« previous</span>";
//pages
if ($lastpage < 7 + ($adjacents * 2)) //not enough pages to bother breaking it up
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
}
elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
{
//close to beginning; only hide later pages
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
$pagination.= "...";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//close to end; only hide early pages
else
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
$pagination.= "...";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
}
}
//next button
if ($page < $counter - 1)
$pagination.= "<a href=\"$targetpage?page=$next\">next »</a>";
else
$pagination.= "<span class=\"disabled\">next »</span>";
$pagination.= "</div>\n";
}
?>
<?php
while($row = mysql_fetch_array($result))
{;
echo '<p><strong>'.($i+1).'.Name: ';
echo $row['Name'];
echo '<p></strong><br />Date and Time: ';
echo $row['Date']." at ";
echo $row['Time'];
echo '<p>Recipient: ';
echo $row['Recipient'];
echo '<p>Message: ';
echo $row['Message'];
}
?>
<?=$pagination?>
Re: Help displaying data
Posted: Thu Nov 20, 2008 6:44 pm
by califdon
OK, you're almost there. You have a couple of syntax oddities in your code, although I don't think they are causing the problem you described. But to get them out of the way, you have a line immediately after the
while... line that shows:
{; -- get rid of the
;. And I don't know what that last line is,
It looks like a hangover from ASP, but it should be removed.
So you're saying that your pagination display looks OK, but no data is being presented? I would check to see whether your query is returning any rows of data. You can do that easily by echoing $num_results right after you do that first query. If it's not returning any rows (my first guess), it means your query isn't finding anything, so then you need to echo the query string $query right after you set its value. You may see that something is missing.
Now if the $num_results shows a value of 5, something is wrong in your while loop, although I don't see anything wrong, from looking at the code.
Try the above and let us know what you get.
Re: Help displaying data
Posted: Tue Nov 25, 2008 3:49 pm
by cjswanson1355
Ok, after messing around for a while I decided to use a library,and I've finally isolated the problem, but I am unsure how to fix it. I want this to display the data requested in the search. However, I don't know how to retain the parameters when a user is paging through. Basically, when I remove the $searchtype and $searchterm with Name and 'theName', it works fine, but when I replace them with the variables, i start getting errors. the first page loads up fine, but subsequent pages have errors trying to retrieve data.
How can I get this data originally passed through the html to stay within access of this pagination?
Code: Select all
<?php
require_once ('Pager/Pager.php');
$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm'];
/* Replace this with your database details */
$connection = mysql_connect("localhost", "***", "********");
mysql_select_db(messages, $connection);
/* First we need to get the total rows in the table */
$result=mysql_query("SELECT count(*) AS total FROM messages where name = 'Andy'", $connection);
$row = mysql_fetch_array($result);
/* Total number of rows in the logs table */
$totalItems = $row['total'];
/* Set some options for the Pager */
$pager_options = array(
'mode' => 'Sliding', // Sliding or Jumping mode. See below.
'perPage' => 4, // Total rows to show per page
'delta' => 4, // See below
'totalItems' => $totalItems,
);
/* Initialize the Pager class with the above options */
$pager = Pager::factory($pager_options);
/* Display the links */
echo $pager->links;
/* The following code will retreive the result using the pager options */
/* The function below will get the page offsets to be used with
the database query. For e.g if we are on the third page then the
$from variable will have the value of '21' (we are showing 10 items per
page, remember) and the $to variable will have the value of '30'.
*/
list($from, $to) = $pager->getOffsetByPageId();
/* The MySQL 'LIMIT' clause index starts from '0',
so decrease the $from by 1 */
$from = $from - 1;
/* The number of rows to get per query */
$perPage = $pager_options['perPage'];
$result = mysql_query("SELECT * FROM messages where name = 'Andy' LIMIT $from , $perPage",
$connection);
while($row = mysql_fetch_array($result))
{
echo "<p>";
Print $row['Name'];
echo " ";
Print $row['Method'];
echo " at ";
Print $row['Time'];
echo " on ";
Print $row['Date'];
echo " to let ";
Print $row['Recipient'];
echo " know that... ";
echo "<p>";
Print $row['Message'];
}
?>
Re: Help displaying data
Posted: Tue Nov 25, 2008 6:22 pm
by califdon
Basically, pagination usually uses hyperlinks for "Previous" and "Next" or more complicated links to let the user skip to a page, or "First" or "Last" page of the data. That's entirely up to you, the designer. The
href= parameter of the hyperlink would consist of the name of the PHP script that will fetch the data (commonly the same script that it's in), including a URL string that contains the variable name and the value of the first record to fetch. This would be determined at the time the existing page is sent, using PHP logic, so that each link will have the proper value, depending on whether "Previous" or "Next" (or other button) was clicked. This means that the script must also obtain this value at the beginning of the script, so it can fetch the rows from the database. Something like this:
Code: Select all
<?php
if(isset($_GET['begin'])) {
$begin=$_GET['begin'];
} else {
$begin=1;
}
$perpage=10; //or whatever number you want to fetch each page
// here you do your database connection and query...
// something like $sql = "SELECT * FROM mytable LIMIT $begin $perpage";
$result=mysql_query($sql) or die("Query failed. " . mysql_error());
$total=mysql_num_rows($result);
// then you show the data...
while($row=mysql_fetch_assoc($result)) {
extract($row);
echo "<tr><td>$field1</td><td>$field2</td><td>$field3</td></tr>";
}
$prev = $begin - $perpage;
if($prev < 1) { $prev = 1; }
$next = $begin + $perpage;
if($next > $total) { $next = $total - $perpage; }
echo "<tr><td colspan=99><a href='" . $_SERVER['HTTP_SELF'] . "?begin=$prev'> <-- PREVIOUS</a>";
echo " ";
echo "<a href='" . $_SERVER['HTTP_SELF'] . "?begin=$next'> NEXT --></a></td></tr>";
I haven't tested this, but it probably works, and you will have to adapt it to your data and format in any case. This should give you an idea how it works.