(SOLVED)SQL Results with Catagories and Pagination
Posted: Sun Mar 13, 2005 11:50 pm
Im trying to display a list of results from a database witch can be sorted by catagories and then by page number. I can either or to work but not both at the same time. As it sits the catagoris work fine, but the page numbers dont change the page. I think there is something im missing in the query. Hopen someone can help me figure it out..
Heres the code.
Still tryin to learn, but it just seems to get more complicated every day
Thanks again for your help..
Ron
feyd | Please use
Heres the code.
Code: Select all
<?
session_start();
header("Cache-control: private"); //IE 6 Fix
include 'includes.inc.php';
$session = "7d9497312c7cac501c0fc4292dc581fb";
//$session=session_id();
mysql_connect ("$hostname", "$username", "$password") or die ('I cannot connect to the database because: ' . mysql_error());
@mysql_select_db ("$database") or die( "Unable to select database");
//===PANGINATION CODE. CHECK TO SEE IF PAGE NUMBER IS SET
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}
//MAX RESULTS
$max_results = 30;
// Figure out the limit for the query based on the current page number.
$from = (($page * $max_results) - $max_results);
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM $session"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
// BASE SQL QUERY
$sql = ("SELECT id, domain, alexa, yahoo, altavista, hotbot, google, alltheweb, dmoz, zeal, total FROM $session");
// RECOGNIZED ARRAY FOR CATAGORIES
$recognized = array('id'=>array('id',0,1),'domain'=>array('domain',0,1),'alexa'=>array('alexa',1,0),'yahoo'=>array('yahoo',1,0),'altavista'=>array('altavista',1,0),'hotbot'=>array('hotbot',1,0),'google'=>array('google',1,0),'alltheweb'=>array('alltheweb',1,0),'dmoz'=>array('dmoz',1,0),'zeal'=>array('zeal',1,0),'total'=>array('total',0,1)); // add recognized names in here..
//SET ADDON TO NULL
$addon = '';
//ADDON TO SQL RESULTS FOR CATAGORY ORDER
foreach($_GET as $k => $v)
{
if(isset($recognized[$k]))
$addon .= (empty($addon) ? ' ORDER BY ' : ', ') . ($recognized[$k][0]) . ($recognized[$k][1 + (int)(bool)($v)] ? ' ASC' : ' DESC');
}
$sql .= ("$addon Limit $from, $max_results") ;
echo $sql;
//create a query
$result = mysql_query($sql) or die(mysql_error());
// Begin your table outside of the array
echo $total_results;
print "<table width=\"100%\" border=\"0\" cellpadding=\"4\" cellspacing=\"0\">\n";
//get field names
print "<tr>\n";
while ($field = mysql_fetch_field($result))
{
print" <td><a href=\"";
print $_SERVER['PHP_SELF'];
print"?$field->name=\n";
print ((int)!(isset($_GET["$field->name"]) ? $_GET["$field->name"] : 1));
print" \"><b>$field->name</b></a></td>\n";
}
print "</tr>\n\n";
// DEFINE COLORS FOR ALTERNATING ROWS
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count = 0;
//get row data as an associative array
while ($row = mysql_fetch_assoc($result))
{
$row_color = ($row_count % 2) ? $color1 : $color2;
$row_count++;
print "<tr>\n";
//look at each field
foreach ($row as $col=>$val)
{
print " <td bgcolor=\"$row_color\" nowrap>$val</td>\n";
}
print "</tr>\n\n";
}
print "</table>\n";
// BEGIN BOTTOM TABLE
echo "<table width=\"100%\" border=\"0\">
<tr>
<td width=\"225\" align=\"left\"><p>Click <a href=\"http://\">here</a> to empty your database.</p></td>
<td align=\"center\">";
// BEGIN PAGINATION
$pagetotal = $total_results-1;
if($pagetotal > $max_results){
echo "<center>Select a Page<br />";
// Build Previous Link
if($page > 1){
$prev = ($page - 1);
print "<a href=\"";
print $_SERVER['PHP_SELF'];
print "?";
// CATAGORY NAME
print $recognized[$k][0];
print "=";
// CATAGORY ASC OR DESC
print $recognized[$k][1 + (int)(bool)($v)];
print"?page=$prev\"><<Previous</a>";
}
// BUILD LINK NUMBERS
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i ";
} else {
// BEGIN LINK
print "<a href=\"";
// SERVER ADDRESS
print $_SERVER['PHP_SELF'];
print "?";
// CATAGORY NAME
print $recognized[$k][0];
print "=";
// CATAGORY ASC OR DESC
print $recognized[$k][1 + (int)(bool)($v)];
//PAGE NUMBER
print "?page=$i\">$i</a> ";
}
}
// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
print "<a href=\"";
print $_SERVER['PHP_SELF'];
print "?";
// CATAGORY NAME
print $recognized[$k][0];
print "=";
// CATAGORY ASC OR DESC
print $recognized[$k][1 + (int)(bool)($v)];
print"?page=$next\">Next>></a>";
} echo "</center>";
}
// END LINK PNAGINATION
// END BOTTOM TABLE
echo "</td>
<td width=\"225\" align=\"right\">
</td>
</tr>
</table>";
mysql_close();
?>Thanks again for your help..
Ron
feyd | Please use
Code: Select all
andCode: Select all
tags where approriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]