All Probs Solved: Pagination/Clickable Ordering/Alternating Row Colors

chaosdivine

Here is some GOOD code with the results of my previous thread. It has all the goodies in it. Pagination/Clickable Header Ordering/Alternating Table Row Color Output. Thanks to all for helping...

Hope this helps you peeps out with your future coding trials!

<?
/* Customizable pagination with alternating table row colors and
clickable header ordering This is the result of my pains and the
application of other's wisdom.
Special thanks to Loafin for his code on table row colorization!
Feel free to modify and use at your discression.
If this helps you out, it would be nice to hear from you, so you can e-
mail me at [email]chaosdivine@hotmail.com[/email].
Peace!
Chaosdivine
*/

$db_addr = 'localhost';		// address of MySQL server.
$db_user = '';		// Username to access server.
$db_pass = '';		// Password access server.
$db_name = 'domaintrader';	// 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.
?>

<html>
<head>
<title>Customizable Pagination With Alternating
Table Row Colors and Clickable Header Ordering</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="100%" border="0">
  <tr> 
    <td colspan="2" align="left"><font face="Arial, Helvetica, sans-serif"><font
       size="1">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>&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"> 
<?
//$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&order=$order\"><< 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&order=$order\">$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&order=$order\">Next >></a>");
}
?>
    </td>
  </tr>
</table>
<?
//Go back into PHP mode.

$default_sort = 'domains'; //set default sort ordering scheme
$allowed_order = array ('domains', 'price'); //these are the allowable fields for ordering

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise, 
* set it to what was passed in. */

if (!isset ($_GET['order']) || 
    !in_array ($_GET['order'], $allowed_order)) {
 $order = $default_sort;
} else {
 $order = $_GET['order'];
}
$results = mysql_query("SELECT *
                        FROM domains
                        ORDER BY $order
                        LIMIT $page, $limit"); //defines the query parameters

// Now we can display results

//defines the clickable sort ordering by header title while maintaining $limit (listing offset)
?>
<TABLE width="50%" BORDER="0">
  <TR bgcolor="#4f5c38">
    <TD><font color="white"><a href="<?="$PHP_SELF?query=$query&limit=$limit&order=domains"?>">Domain Name</a></font></TD>
    <TD><font color="white">Date Submitted</font></TD>
    <TD><font color="white"><a href="<?="$PHP_SELF?query=$query&limit=$limit&order=price"?>">Asking Price</a></font></TD>
  </TR>
<?php

//displays the results and starts the row colorization
for($i = 0; $i < $limit; $i++){
 for ($count = 1; $row = mysql_fetch_assoc ($results);++$count) {
  if ($count & 1) { 
   $color = "#c8cfc6";
   $font = "yellow";
  } else {
   $color = "#9aa486";
   $font = "white";
  }
  $printthis = "<tr>
  <td bgcolor=\"$color\"><font color=\"$font\">$row[domains]</font></td>
  <td bgcolor=\"$color\"><font color=\"$font\">$row[domainDate]</font></td>
  <td bgcolor=\"$color\"><font color=\"$font\">$row[price]</font></td>
</tr>";
  print $printthis;
 }

 echo "</TABLE>";
}
?>
</body>
</html>

Ciao folks 😃

phplearner

Thanks for sharing...also sent you PM.

BTW, will this work with 'register_globals off' ???

phplearner

OK...I set up a quick test DB with only 2 entries. If I click on the 5 | 10 | 20 | 50 | ... I get a Forbidden on this Server type error. So, apparently if there are not enough entries, this whacks out. Is there some way to have these options NOT show if insufficient records?

The clickables work okay, except on the same page I get all these errors:

Notice: Undefined variable: limit in c:\www\test\rmphp\gocode\tryagain.php on line 33

Notice: Undefined variable: page in c:\www\test\rmphp\gocode\tryagain.php on line 36
Results per-page: 5 | 10 | 20 | 50

Showing Results 1 - 2 of 2 Page 1 of 1

Notice: Undefined variable: PHP_SELF in c:\www\test\rmphp\gocode\tryagain.php on line 161

Notice: Undefined variable: query in c:\www\test\rmphp\gocode\tryagain.php on line 161

Notice: Undefined variable: PHP_SELF in c:\www\test\rmphp\gocode\tryagain.php on line 161

Notice: Undefined variable: query in c:\www\test\rmphp\gocode\tryagain.php on line 161
Domain Name Date Submitted Asking Price

Notice: Undefined index: domainDate in c:\www\test\rmphp\gocode\tryagain.php on line 175
test.com 100

Notice: Undefined index: domainDate in c:\www\test\rmphp\gocode\tryagain.php on line 175

ARRRGGGHHHH!!!!

phplearner

FYI...more feedback...

If you click on the Domain Name the 1st time, it will NOT re-order. You have to click on Asking Price first to resort, then the Domain Name clickable will reorder.

Is this the way you wanted it to work??? It would seem to me that any clickable should work 'anytime' ???

Hope this helps.

chaosdivine

I am looking at your errors and I am thinking the way you set up your database does not reference the variables defined in your code. You have to modify your code to reflect the variables you set up in your database.

Here is my database SQL structure. Try this and see if she works:

Create database domaintrader;
Use domaintrader;


CREATE TABLE domains (
   ID int(15) NOT NULL auto_increment,
   domains text NOT NULL,
   domainDate date DEFAULT '0000-00-00' NOT NULL,
   price varchar(20) NOT NULL,
   PRIMARY KEY (ID)
);

#
# Dumping data for table 'domains'
#

INSERT INTO domains VALUES ( '1', 'Ladieswear.ca', '2003-02-15', '$25.00 CDN');
INSERT INTO domains VALUES ( '2', 'Childrenswear.ca', '2003-02-15', '$25.00 CDN');
INSERT INTO domains VALUES ( '3', 'ProTeamSportswear.com', '2003-02-15', '$25.00 USD');
INSERT INTO domains VALUES ( '4', 'ProTeamApparel.com', '2003-02-15', '$25.00 USD');
INSERT INTO domains VALUES ( '5', 'TellCanada.com', '2003-02-15', '$25.00 USD');
INSERT INTO domains VALUES ( '8', 'SportswearOnly.com', '2003-02-15', '$25.00 USD');
INSERT INTO domains VALUES ( '9', 'ProTeam.ca', '2003-02-15', '$25.00 CDN');
INSERT INTO domains VALUES ( '10', 'Comet.ca', '2003-02-15', '$45.00 CDN');
INSERT INTO domains VALUES ( '11', 'Comet.us', '2003-02-15', '$45.00 USD');
INSERT INTO domains VALUES ( '12', 'SurreyBritishColumbia.com', '2003-02-15', '$25.00 USD');

for example: in your errors they reference undefined variables...to me this means that the server is tying to find the values that the variables hold but they do not technically exist in your database structure and therefore nothing at all is returned and things break down....

Register globals off: Not sure actually. I am running PHP Version 4.1.1 only and if memory serves me...php version 4.2.x plus has these off by default....so I am not sure.

I do know the code works though. It may be buggy cause I am kinda new to all this too but you can test it out at my site:

See it in action

If you need more help I will try to help you out here in the forum.

By the way the default sort order is defined to be by domain name in the code...so how could it resort by domain name when it already is sorted that way by default?

By clicking on the table column headers you are overriding the default sort criteria. I believe it is still working correctly...

look at my working example above and let me know if this helps or hinders...

chaosdivine

One more thing...

It does not however resort both ascending and decending if this is what you are asking about when you click a second time on the column header link. If this is what you are asking about...

I believe you could modify your SQL accordingly with the ASC or DESC keywords in your SQL statements and get different sort results. But to get them to do this in the coded link HREF could be tricky. I am not sure how to do this yet...

Here is what I am referring to:

1) click the link the first time and it sorts ascendiing....
2) click the link the second time and it sorts descending...

chaosdivine

I have slept on this clickable header resorting thing and I think you can just use simple IF and ELSE statements or someother type of control structure (SWITCH CASE). To get this to work...

One statement would include $order=domains&ASC
and the other would have $order=domains&DESC

You would do this for both clickable column headers (4 statements in total versus the 2 created now.)

I have not tried this yet in action...but I will to see if I am right or not.

I think I am on the right track though...Anyone feel like trying out something different? Please post your results...

phplearner

Thank you very much for your replies and the SQL for testing.

I will study this for a while!!!

Regards...

phplearner

chaosdivine:

The SQL worked great...setup a domaintrader DB and queries directly via MYSQLCC produce results fine.

Changed the user/pwd in the .php but it simply will not work. I have strong suspicions that the code must be altered in order to work in the PHP4.2 'register_globals off' security default environment...but dunno how to do this.

"HELP" (anyone)!!!

Thanks.

chaosdivine

One more thing...just looking at the SQL I posted, I noticed a spelling mistake! DOH! It may be affecting your Database and the PHP...that is if you just copied it directly. This could be the problem

I have written down below:
Create database domamaintrader;
Use domamaintrader;

The name of the database should be domaintrader and not the doMAMAINtrader like I wrote above. Oops, I can't type anyhow!

Check that out first. Second, I am running php 4.2.x on my server where my website is at and it does work there too. Global variables may or may not be on to be honest. I will have to run phpinfo.php on it and check. The global variable thing is a pain in the ass thats for sure. I see arrays are accessed differently in situations and this is supposed to be more secure. To be honest it has confused the hell outta me too. There is a great article on www.sitepoint.com by Kevin Yank that tells about this stuff. I would have to direct you over to there. I wish I could remember the exact URL to the article but I can't for the life of me. I need to refresh on this too...

Ciao PHPlearner,
Drew

phplearner

Hi chaosdevine:

Gheezzz... I need new glasses 'cuz I missed the domaintrader thing !!!

Made the changes but am still getting the following errors:

Notice: Undefined variable: limit in <filename> on line 33

Notice: Undefined variable: limit in <filename> on line 36

Notice: Undefined variable: PHP_SELF in <filename> on line 161

FYI:

Line 33 = if (!($limit)){

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

Line 161 = echo "<TR bgcolor=\"#4f5c38\"><TD><a href=\"$PHP_SELF?query=$query&limit=$limit&order=domains\">Domain Name</a></TD><TD>Date Submitted</TD><TD><a href=\"$PHP_SELF?query=$query&limit=$limit&order=price\">Asking Price</a></TD></TR>\n";//defines the clickable sort ordering by header title while maintaining $limit (listing offset)

I have no clue why this works on your system but not on mine (I'm testing on a PC Desktop with Apache/PHP/MySQL where other stuff does work.

ARRRRRGGGGGHHHHHHHHHH!!!

phplearner

MORE ...

Also just found if I click on the 5 | 10 | 30 | 50 options, I get erros on "Line 87, 87, 88 & 90" which are:

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>

BTW, you might want to "Edit" your previous post which contains the SQL and correct the 'domaintrader' name (in the event someone wants to try this out but doesn't read farther down in the thread!)

Tnx.

phplearner

ARRRRRRRRRGGGGGGGGGGGHHHHHHHHHHH :mad: :mad: :mad:

Doesn't anyone know how to fix this???

The_Chancer

About the notice errors, that is to do with your PHP.ini setup. You can disble the notices, which are just letting you know that you don't have the variables set... this is not an error, but a notice as it said...

phplearner

Thank you, but like many others, I can NOT control my ISP's settings of the php.ini which still leaves me hanging.

Is there a script workaround to solve this?

IMHO, many of us are going to have more and more problems with things which won't work with standard ISP installations of PHP since the whole 'register_globals off' situation has come to light.

phpmike

you might also want to validate the code that is coming in using ereg. If you look at how it is coded anyone can manipulate the string which can greatly affect the security of the site.

jassh

dude..

i have the same problem with my ISP
(i understand what you mean by that)
and definately, u cant just aske them to change the server settings.

anyway, try this:
try to change your ['PHP_SELF'] to
$_SERVER['SCRIPT_NAME']

that works for me...
how about giving it a go and tell us about it..

hope that helps..

cheers;
-jassh

The_Chancer

To alter the error reporting at runtime, instead of using the ISP's php.ini... look at

(...The manual....)

Which lets you alter how the reporting is handled.

phplearner

Originally posted by jassh
dude..

anyway, try this:
try to change your ['PHP_SELF'] to
$_SERVER['SCRIPT_NAME']

that works for me...
how about giving it a go and tell us about it..

:p

Tnx...tried this on the Line 161 problem:

$_SERVER['tryagain2.php']

...and now get this:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in <filename> on line 161

ARRRGGGHHH!!!!

Tnx also to phpmike & The Chancer, but it would seem to me that wisdom would be to eliminate whatever the source of errors are in a script in the first place???

jassh

err.. phplearner dude..

i think you get me wrong...

when i meant use $_SERVER['SCRIPT_NAME'] instead of $PHP_SELF..

i didnt mean for you to change
the 'SCRIPT_NAME' to your file name,
just write i as

$_SERVER['SCRIPT_NAME']

without changing it to something else..

hope that clears things out

have a nice day dude.. 🙂
rawk onn...

-jassh