pagination and alternating table row colors probs...

[chaosdivine]

Hello to anyone looking at this thread and can help me out. I have found a great set of pagination code (prev/next with 5|10

|20|50) search results from a MySQL database. I have tried to get pretty with the design and tried to code in alternating

table row colors from the results...I have achieved this goal however when I choose to return say 20 or 50 results at a time,

on the last page of my results, I am getting empty color filled rows of "lines" because of my search criteria-50 returns per

page-and only having 8 returns on a second page (58 total rows of data), I get 42 rows of empty, scrunched up lines of color

...

This is ugly and I am tring to figure out a better way to implement alternating table row colors without having these extra

lines of color in them. Could some of you help me on this please?

I know this is hard to describe in text, so I have attached a URL from my web site so you can see it in action.

URL LINK

I have also attached the code for the pagination and alternating row colors here...I have ommitted most of the HTML coding

from the page to save space...Feel free to take this code and alter it for your liking by the way.

I am also trying to figure out how to implement clickable table headers that can sort the MySQL return results by the

different column headings...I have found snippets of code for this too but they are long and arduous. I know that this stems

from the MySQL queries and somehow linking them to the PHP and HTML tags, but where to begin, I am lost too...

This is a secondary goal so if one can help with the first part(alternating row colors within the pagination results) I would

be super happy! Thanks for your time and help!

The code:

<?
$db_addr = 'localhost';		// address of MySQL server.
$db_user = 'blah';		// Username to access server.
$db_pass = 'blah';		// Password access server.
$db_name = 'somedbase';	// Name of database to connect to.
$connect = @mysql_connect("$db_addr", "$db_user", "$db_pass");

if (!($connect)) // If no connect, error and exit().
{
     echo("<p>Unable to connect to the database server.</p>");
     exit();
}

if (!(@mysql_select_db($db_name))) // If can't connect to database, error and exit().
{
     echo("<p>Unable to locate the $db_name database.</p>");
     exit();
}


if (!($limit)){
     $limit = 10;} // Default results per-page.

if ( !$page or $page < 0 ) { $page = 0; } // Default page value.

$numresults = mysql_query("SELECT * FROM domains"); // the query.

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.

if ($numrows == 0){
     echo("No results found matching your query"); // modify the "Not Found" error for your needs.
     exit();}



$pages = intval($numrows/$limit); // Number of results pages.
// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++;} // has remainder so add one page

$current = intval($page/$limit) + 1; // Current page number.

if (($pages < 1)) {
$total = 1;} // If $pages is less than one, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.


//escape from PHP mode and go into HTML mode.
?>

<TABLE class=border cellSpacing=0 cellPadding=10 width="100%" 
            align=center border=0>
                  <TBODY>
                    <TR> 
                      <TD width="100%" class=side> <table width="100%" border="0">
                          <tr> 
                            <td colspan="2" align="left"><font size="1" face="Arial, Helvetica, sans-serif">Results 
                              per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a> 
                              | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a> 
                              | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a> 
                              | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a></font><font face

="Arial, Helvetica, sans-serif">&nbsp; 
                              </font></td>
                          </tr>
                          <tr> 
                            <td colspan="2" align="left">&nbsp;</td>
                          </tr>
                          <tr> 
                            <td width="50%" align="left" valign="top"><font size="2" face="Arial, Helvetica, sans-serif"

Showing 
                              Results <b> 
                              <?=$first?>
                              </b> - <b> 
                              <?=$last?>
                              </b> of <b> 
                              <?=$numrows?>
                              </b></font> </td>
                            <td width="50%" align="left" valign="top"> <div align="left"><font size="2" face="Arial, 

Helvetica, sans-serif">Page 
                                <b> 
                                <?=$current?>
                                </b> of <b> 
                                <?=$total?>
                                </b></font></div></td>
                          </tr>
                          <tr> 
                            <td height="21" colspan="2" align="left"> <font face="Arial, Helvetica, sans-serif">&nbsp; 
                              &nbsp; </font> </td>
                          </tr>
                          <tr> 
                            <td height="21" colspan="2" align="left"> 

<?
//un comment this $limitnum next line and the below elseif if you want to return limited pages of searches...say 6 on screen 

//at a time
//instead of 300 pages for example...stays pretty on screen.
//$limitnum = 15;
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a>    \n");}

for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
 $ppage = $limit*($i - 1);
 if ($ppage == $page){
 echo("<b>$i</b>\n");} // If current page don't give link, just text.
//elseif ($ppage < $page - $limitnum || $ppage > $page + $limitnum){} 
else{
 echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}

if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>");}
?>
                            </td>
                          </tr>
                        </table>



 <?
//Go back into PHP mode.
// Now we can display results

$results = mysql_query("SELECT * FROM domains ORDER BY domains ASC LIMIT $page, $limit");
echo "<TABLE width=\"100%\" BORDER=\"0\">\n";
echo "<TR bgcolor=\"#4f5c38\"><TD><font color=\"white\">Domain Name</font></TD><TD><font color=\"white\">Date Submitted</

font></TD><TD><font color=\"white\">Asking Price</font></TD></TR>\n";
//while ($row = mysql_fetch_array ($results))
for($i = 0; $i < $limit; $i++) 
{
$row = mysql_fetch_array($results); //get a row from our result set
    if($i % 2) { //this means if there is a remainder
        echo "<TR bgcolor=\"#c8cfc6\">\n";
    } else { //if there isn't a remainder we will do the else
        echo "<TR bgcolor=\"#9aa486\">\n";
    }
    echo "<TD>".$row['domains']."</TD><TD>".$row['domainDate']."</TD><TD>".$row['price']."</TD></TR>";
    echo "</TR>";
}
//now let's close the table and be done with it
echo "</TABLE>";
?>

[Loafin]

Sample of alternating row color:

<table>
<?
for ($count = 1; $row = mysql_fetch_assoc ($result_handle);++ $count;)
        {

if ($count & 1) {
$color = "red";
$font = "blue";
}
else {
$color = "#ccdd00";
$font = "#665500";
}

$printthis = "<tr><td bgcolor=#d0d0d0>$count</td><td bgcolor=$color></td><td bgcolor=$color><font color=$font>$row[field1]</font></td><td bgcolor=$color><font color=$font>$row[field2]</font></td></tr>";

print $printthis;
?>
</table>


}

This is how I acheive alternating colors for each row with no leftovers. This is an ugly example to view, but it works. If you run out of rows to parse (a partial list page) it simply jumps out of the loop.
The 'if ($count & 1)' control checks if the last bit is flipped to a 0 or a 1 (even or odd).

[rpter]

after copying your code down and modifying it slightly to run off my database it doesnt seem to run properly anyway, although linked page you provided does.

You'd be better using a paging class, rather than having all the code in one page.

i suggest this one http://www.sitepoint.com/article/662/1, this is what i use (or a slightly modified version anyway), and it works a treat. it should also be easy to add alternating colours.

As for the clickable headers... i've looked at this myself in the past. As far as i could see the only way to do it is with long code.

Hope this helps.

Roland

[Loafin]

Actually, there's a slight problem in the last line of code there. The curly brace should be inside the <? ?> tags at the end like so...

<table>
<?
for ($count = 1; $row = mysql_fetch_assoc ($result_handle);++ $count; )
        {

if ($count & 1) {
$color = "red";
$font = "blue";
}
else {
$color = "#ccdd00";
$font = "#665500";
}

$print "<tr><td bgcolor=#d0d0d0>$count</td><td bgcolor=$color></td><td bgcolor=$color><font color=$font>$row[field1]</font></td><td bgcolor=$color><font color=$font>$row[field2]</font></td></tr>";

}
?>
</table>

Syntax can be so particular sometimes 🙂

[chaosdivine]

Thank your for your replies guys! I will try your code and look into your wisdom! As for the point about my code not working anyhow to work from your database, I am not surprised, I must have ommitted some of the goods! So I am posting the code again for someone reading this thread in hope it helps someone. his version does not contain the colorization cause my version is so nasty! And looking at Loafin's code, its just more tight anyhow!

The pagination code:

<?
<?
$db_addr = 'localhost';		// address of MySQL server.
$db_user = 'user';		// Username to access server.
$db_pass = 'password';		// Password access server.
$db_name = 'MyDatabase';	// Name of database to connect to.
$connect = @mysql_connect("$db_addr", "$db_user", "$db_pass");

if (!($connect)) // If no connect, error and exit().
{
echo("<p>Unable to connect to the database server.</p>");
exit();
}

if (!(@mysql_select_db($db_name))) // If can't connect to database, error and exit().
{
echo("<p>Unable to locate the $db_name database.</p>");
exit();
}

if (!($limit)){
$limit = 10;} // Default results per-page.
if ( !$page or $page < 0 ) { $page = 0; } // Default page value.
$numresults = mysql_query("SELECT * FROM your_table"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
echo("No results found matching your query. "); //  modify the "Not Found" error for your needs.
exit();}

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows%$limit) {
$pages++;} // has remainder so add one page

$current = intval($page/$limit) + 1; // Current page number. To make output a bit nicer 

if ( $pages < 1 ) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

//escape from PHP mode.
?>
<html>
<head>
<title>Search Results for SOMETHING</title>
</head>
<body>
<center><h2>Search Results for SOMETHING</h2></center>
<table width="100%" border="0">
 <tr>
  <td width="50%" align="left">
Results <b><?=$first?></b> - <b><?=$last?></b> of <b><?=$numrows?></b>
  </td>
  <td width="50%" align="right">
Page <b><?=$current?></b> of <b><?=$total?></b>
  </td>
 </tr>
 <tr>
  <td colspan="2" align="right">
&nbsp;
  </td>
 </tr>
 <tr>
  <td colspan="2" align="right">
Results per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a> | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a> | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a> | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a>
  </td>
 </tr>
</table>
<?
//Go back into PHP mode.

// Now we can display results.
$results = mysql_query("SELECT * FROM your_table ORDER BY name ASC LIMIT $page, $limit");
while ($data = mysql_fetch_array($results))
{
?>
<p><a href="<?=$data["url"]?>" title="<?=$data["name"]?>"><?=$data["name"]?></a> - <?=$data["description"]?></p>
<?
}
?>
<p align="center">
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a>    \n");}

for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
 $ppage = $limit*($i - 1);
 if ($ppage == $page){
 echo("<b>$i</b> \n");} // If current page don't give link, just text.
 else{
 echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}

if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>\n");}
?>
</p>
</body>
</html>

I am sure this code works by the way...I hope it helps someone! It did me! I am restating the obvious here for the newbies ...Remember to modify the references to the database to reflect your environment. Also when the echo tatements start printing out the array column names from the database, you have to change em too...

I will try to find the clickable page order code at a future time. I am working on some that seems to be not too bad. If I get all this in one complete set of code (proper header ordering, pagination and colored row output), I will post it for all. Three straight forward things that when combined have caused me quite a headache!!!

I do agree with using a function to do my dirty work for me. I am looking into this for future tidy up!

Thanks a lot guys!!!

😃

[chaosdivine]

Loafin, your code works beautifully! You have helped me tremendously! I had to tinker with it but now she works like a charm. Great code by the way! THANK YOU!!!

To the rest of the readers of this thread:
I can post the code with Loafin's color changes implemented if there is any interest...post a follow up and I shall do it.

I am still working on clickable page headers but this shouldn't be too too difficult...ya right! ha ha.

😃

[chaosdivine]

rpter

Great article by the way, thanks! Kevin Yank's articles are what has taught me PHP and MySQL by the way!!!

I am going to buy his book soon too...I think its a must have.

I agree totally that OOP is where its at. I hate typing anyhow!

😉