Sorting MYSQL results Paging and column heads
| From: | Edward Brookhouse | Date: | Mon, 05 Oct 2009 18:57:42 +0000 |
| Subject: | Sorting MYSQL results Paging and column heads | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-46563@lists.php.net to get a copy of this message | ||
Hi All,
I am working on a PHP page to display results from a MySQL db and I am having a problem getting my
results to page properly.
When the page loads, it looks fine, but hitting next/previous does not change the data?
Any thoughts appreciated -
Edward
Ebrooathealthydirectionsdotcom
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"
/>
<title></title>
<link href="demo.css" rel="stylesheet" type="text/css"
/>
</head>
<body>
<h1>
<?php
include_once 'config/constants.php';
include_once 'config/opendb.php';
// rows to return
$rowsPerPage = 20;
$pageNum = 1;
$yesterday = strftime ("%Y/%m/%d", strtotime("-2 day"));
$pretty = strftime ("%A %B %d");
$page = $_GET['page'];
print "<table style='font-family: verdana; font-size: 8pt; color: 000066;'
cellpadding=5 cellspacing=1 bgcolor=FFFFFF width=\"1009\" height=\"293\"
border=\"0\">";
print " <tr>";
print "<td width=\"471\" height=\"287\"><h1><img
src=\"http://microbe.healthydirections.com/images/hd_bnr_TLv2.gif\"
width=\"471\" height=\"123\" /></h1>";
print "<font face=arial color=000066><h1>Bounce Report
</font></h1>";
print "<font face=arial color=000066><a href=\"http://microbe.healthydirections.com/bounce\">Go
to Adaptive Log</a><br />";
print "<br />";
print "Showing results from $pretty <br />";
print "<br>";
print " <td width=\"522\">";
print "</td>";
print "</tr>";
print "</table>";
print "</h1>";
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}
$offset = ($pageNum - 1) * $rowsPerPage;
db_connect_ecelerity(DBUSER, DBUSERPW);
$query1 = "select * FROM
![http://microbe.healthydirections.com/images/hd_bnr_TLv2.gif\"](http://microbe.healthydirections.com/images/hd_bnr_TLv2.gif\"){kind=link}
bouncelog ORDER BY insert_date DESC LIMIT
$offset, $rowsPerPage";
$numresults=mysql_query($query1);
$numrows=mysql_num_rows($numresults);
$result = mysql_query($query1) or die('Error, lame query failed');
function makeHeaderLink($value, $key, $col, $dir) {
$out = "<a href=\"" . $_SERVER['SCRIPT_NAME'] .
"?c=";
//set column query string value
switch($key) {
case "insert_date":
$out .= "1";
break;
case "message_id":
$out .= "2";
break;
case "log_type":
$out .= "3";
break;
case "to_local":
$out .= "4";
break;
case "to_domain":
$out .= "5";
break;
case "from_local":
$out .= "6";
break;
case "from_domain":
$out .= "7";
break;
case "binding_group":
$out .= "8";
break;
case "binding_name":
$out .= "9";
break;
case "bounce_phase":
$out .= "10";
break;
case "bounce_code":
$out .= "11";
break;
case "ip_address":
$out .= "12";
break;
case "bounce_message":
$out .= "13";
break;
default:
$out .= "1";
}
$out .= "&d=";
//reverse sort if the current column is clicked
if($key == $col) {
switch($dir) {
case "ASC":
$out .= "1";
break;
default:
$out .= "0";
}
}
else {
//pass on current sort direction
switch($dir) {
case "ASC":
$out .= "0";
break;
default:
$out .= "1";
}
}
//complete link
$out .= "\">$value</a>";
return $out;
}
switch($_GET['c']) {
case "1":
$col = "insert_date";
break;
case "3":
$col = "message_id";
break;
case "3":
$col = "log_type";
break;
case "4":
$col = "to_local";
break;
case "5":
$col = "to_domain";
break;
case "6":
$col = "from_local";
break;
case "7":
$col = "from_domain";
break;
case "8":
$col = "binding_group";
break;
case "9":
$col = "binding_name";
break;
case "10":
$col = "binding_phase";
break;
case "11":
$col = "bounce_code";
break;
case "12":
$col = "ip_address";
break;
case "13":
$col = "bounce_message";
break;
default:
$col = "insert_date";
}
if($_GET['d'] == "1") {
$dir = "DESC";
}
else {
$dir = "ASC";
}
if(!$link = mysql_connect("localhost", "root", "psss")) {
echo "Cannot connect to db server";
}
elseif(!mysql_select_db("ecelerity")) {
echo "Cannot select database";
}
else {
if(!$rs = mysql_query("SELECT * FROM bouncelog ORDER BY $col $dir LIMIT 500"))
{
echo "Cannot parse query";
}
elseif(mysql_num_rows($rs) == 0) {
echo "No records found";
}
else {
echo "<table class=\"bordered\"
cellspacing=\"0\">\n";
echo "<tr>";
echo "<th>" . makeHeaderLink("Date",
"insert_date", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Message ID",
"message_id", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Log Type",
"log_type", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("To Local",
"to_local", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("To Domain",
"to_domain", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("From Local",
"from_local", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("From Domain",
"from_domain", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Binding Group",
"binding_group", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Binding Name",
"binding_name", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Bounce Phase",
"bounce_phase", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Bounce Code",
"bounce_code", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("IP Address",
"ip_address", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Bounce Message",
"bounce_message", $col, $dir) . "</th>";
echo "</tr>\n";
for($i = 0; $i < $numrows; $i++)
{
$row = mysql_fetch_array($rs); //get a row from our result set
$date2 = $row['insert_date'];
$date3 = date("F-d-Y", $date2);
$ip2 = $row['ip_address'];
$host = long2ip($ip2);
if(($i % 2) == 0) {
echo "<TR bgcolor=\"#0099FF\">\n";
} else {
echo "<TR bgcolor=\"white\">\n";
}
echo
"<td>$date3</td><td>".$row['message_id']."</td><td>".$row['log_type']."</td><td>".$row['to_local']."</td><td>".$row['to_domain']."</td><td>".$row['from_local']."</td><
td>".$row['from_domain']."</td><td>".$row['binding_group']."</td><td>".$row['binding_name']."</td><td>".$row['bounce_phase']."</td><td>".$row['bounce_code']."</td><td>".$ro
w['ip_address']."</td><td>".$row['bounce_message']."</td>";
echo "</tr>\n";
}
echo "<br>";
}
}
$query = "SELECT COUNT(insert_date) AS numrows FROM bouncelog";
$result = mysql_query($query) or die('Error, query lamefailed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
$self = $_SERVER['PHP_SELF'];
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' [Prev] '; // we're on page one, don't enable
'previous' link
$first = ' [First Page] '; // nor 'first page' link
}
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' [Next] '; // we're on the last page, don't enable
'next' link
$last = ' [Last Page] '; // nor 'last page' link
}
echo $first . $prev . " Showing page <strong>$pageNum</strong> of
<strong>$maxPage</strong> pages " . $next . $last;
echo "</table>";
?>
</body>
</html>