Page 1 of 1

(SOLVED)SQL Results with Catagories and Pagination

Posted: Sun Mar 13, 2005 11:50 pm
by ron_j_m
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.

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&nbsp;"; 
        } 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>&nbsp;";
    } 
} 

// 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();

?>
Still tryin to learn, but it just seems to get more complicated every day :?

Thanks again for your help..
Ron


feyd | Please use

Code: Select all

and

Code: 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]

Posted: Mon Mar 14, 2005 10:15 am
by ron_j_m
I have got it working a little bit.

Here are the changes I made.
I added this statement

Code: Select all

$to = ($from + $max_results);
Changed my sql addon query to

Code: Select all

$sql .= ("$addon Limit $from, $to") ;
And I think the main problem was I was using another ? in the page links, instead of an &.
Now the problem is that page 1 has the correct result of 0 to 30, page 2 has the wrong results. It shows results from 30 to 90 when it should be going from 30 to 60. Page 3 shows results from 60 to 150, when it should be going from 60 to 90, and so on and so fourth.... Can anyone spot out what in this code is making this happen??
Here is the current 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']; 
}
echo ("$page<br>");
     
	 //MAX RESULTS
$max_results = 30; 
     // Figure out the limit for the query based on the current page number. 
$from = (($page * $max_results) - $max_results); 
echo ("$from<br>");
    //  Figure out the limit for the query based on the current page + max results
$to = ($from + $max_results);
echo ("$to<br>");
    // 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, to $to") ;
 
 echo ("$sql<br>");
 //create a query
 $result = mysql_query($sql) or die(mysql_error());
 //$result = mysql_query('SELECT * FROM phoneList', $conn);

 
 // Begin your table outside of the array 
 echo ("$total_results<br>");
 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&nbsp;"; 
        } 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>&nbsp;";
    } 
} 

// 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();

?>
ron

Posted: Mon Mar 14, 2005 10:24 am
by ron_j_m
edit: sorry double post.

Posted: Mon Mar 14, 2005 10:36 am
by ron_j_m
Ok fixed it.
changed sql query from $to back to $max_results.