(SOLVED)SQL Results with Catagories and Pagination

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ron_j_m
Forum Commoner
Posts: 35
Joined: Wed Feb 02, 2005 8:56 pm

(SOLVED)SQL Results with Catagories and Pagination

Post 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]
Last edited by ron_j_m on Mon Mar 14, 2005 10:37 am, edited 1 time in total.
ron_j_m
Forum Commoner
Posts: 35
Joined: Wed Feb 02, 2005 8:56 pm

Post 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
ron_j_m
Forum Commoner
Posts: 35
Joined: Wed Feb 02, 2005 8:56 pm

Post by ron_j_m »

edit: sorry double post.
ron_j_m
Forum Commoner
Posts: 35
Joined: Wed Feb 02, 2005 8:56 pm

Post by ron_j_m »

Ok fixed it.
changed sql query from $to back to $max_results.
Post Reply