Sorting results form a mySQL database in an alternating color table using PHP

PHP Diary | scriptschool.com | PHP Scripts | TD Scripts.com

Link Organizer - A comprehensive link list organizer

[back]

07/31/00 "Sorting and displaying results in mult-colored tables using mySQL"

[next]

The third step - sort and display the results

The last step is to sort and display the results which can be accomplished by the following SQL statement: SELECT:

SELECT * FROM avg_tally ORDER BY average DESC;

You've probably seen sites that alternate the colors inside a table with results. It is actually a lot easier than it looks, actually when pulling this information from a mySQL table. Let's convert this to our PHP script and then create an alternating colorized table with the results in it. Let's creat a table which looks like this:

Rank	Rating	Diary Date	Description of Diary Page
1
2

Now admittedly my choice of colors may be way off base, lol, but I'm not a designer, I'm a programmer, so you can change the color scheme to whatever works for you. Here's the HTML code to make the table above:

<table border="0" width="100%">
<tr>
<td width="6%" bgcolor="#00FFFF">Rank</td>
<td width="7%" bgcolor="#00FFFF">Rating</td>
<td width="11%" bgcolor="#00FFFF">Diary Date</td>
<td width="76%" bgcolor="#00FFFF">Description
of Diary Page
</td>
</tr>
<tr>
<td width="6%" bgcolor="#D8DBFE"> </td>
<td width="7%" bgcolor="#D8DBFE"> </td>
<td width="11%" bgcolor="#D8DBFE"> </td>
<td width="76%" bgcolor="#D8DBFE"> </td>
</tr>
<tr>
<td width="6%" bgcolor="#A6ACFD"> </td>
<td width="7%" bgcolor="#A6ACFD"> </td>
<td width="11%" bgcolor="#A6ACFD"> </td>
<td width="76%" bgcolor="#A6ACFD"> </td>
</tr>
</table>

Now let's create the PHP code to insert the sorted contents from the mySQL database into this table and alternate the colors of the tables. Color 1 is #D8DBFE and color 2 is #A6ACFD. Here is the code to create this:

<table border="0" width="100%">
<tr>
<td width="6%" bgcolor="#00FFFF">Rank</td>
<td width="7%" bgcolor="#00FFFF">Rating</td>
<td width="11%" bgcolor="#00FFFF">Diary Date</td>
<td width="76%" bgcolor="#00FFFF">Description
of Diary Page
</td>
<script language="php">
$query = "SELECT * FROM avg_tally ORDER BY average DESC";
$result = mysql_query($query, $mysql_link);
if(mysql_num_rows($result)) {
$rank = 1;
while($row = mysql_fetch_row($result))
{
 print("</tr><tr>");
 if($color == "#D8DBFE") {
 $color = "#A6ACFD";
 } else {
 $color = "#D8DBFE";
 }
 print("<td width=\"6%\" bgcolor=\"$color\"><center>");
 print("$rank</center></td>");
 print("<td width=\"7%\" bgcolor=\"$color\"><center>");
 print("$row[1]</center></td>");
 print("<td width=\"11%\" bgcolor=\"$color\"><center>");
 $url = $row[2] . ".php3";
 if(!file_exists($url)) { $url = $row[2] . ".html"; }
 print("<a href=\"$url\">$row[2]</a></center></td>");
 print("<td width=\"76%\" bgcolor=\"$color\"><left>");
 print("$row[3]</left></td>");
$rank++;
}
}
</script>

Example #29: Table of most useful diary entries (shown below)

Rank	Rating	Diary Date	Description of Diary Page
1	4.27	012103	How to parse apache access log files and LOAD into mySQL
2	4.26	011103	Double opt-in email verification, php MD5 hash function, unique URLs in mail
3	4.24	073100	Sorting results from a mySQL database in an alternating color table using PHP
4	4.16	010900	Counters and tracking visitors
5	4.13	071200	audio PHP interviews Rasmus Lerdorf at script school
6	4.13	122499	Finding and using matematical averages with php
7	4.13	123099	Building an email list - unsubscribing from mail lists
8	4.11	122902	How to reduce manual tasks using PHP, Convert MMDDYY to Unix Timestamp
9	4.11	070700	Connecting to a mySQL database and creating tables
10	4.09	122399	php cookies file appending introduction training programming learning
11	4.07	010100	Sending email using PHP
12	4.06	042701	mySQL indices and normalization, viewing hidden passwords, mouseovers using php
13	4.05	011303	PEAR + benchmark comparing str_replace, preg_replace and ereg_replace
14	4.04	010700	Searching a directory of files and using to match search criteria
15	4.04	012200	Blending JavaScript with PHP
16	4.04	010300	Windows platform and php
17	4.02	122799	Generating random numbers using PHP
18	4.01	021901	how to make a page counter using mySQL and multicolored souce code
19	4.01	010800	Looping through multiple files compiling a search list
20	4.00	010500	Searching internal and external webpages for information
21	3.97	021500	Using the flock function to protect files from corruption
22	3.96	123002	Free cronjob generator, dynamic generating form select options
23	3.95	122999	Building a mail list using php
24	3.93	073000	dir objects and storing the results of multiple flat files access and manipulation in a mySQL database using PHP
25	3.89	011203	Automated Google queries and developer API, WDSL, SOAP, include_once, require_once
26	3.86	011400	mySQL database field names
27	3.85	122899	Sending email using PHP
28	3.84	072800	Reference books TDavid bought for PHP and mySQL
29	3.83	010600	using meta tags for search engine criteria
30	3.80	010103	Verifiying email addresses + 3D php graphs
31	3.78	071300	using PHP and mySQL to authenticate users password protection
32	3.77	070800	Front Page editor conflicts with PHP
33	3.77	072000	admin interface sorting SQL queries for adding and deleting users using mysql and php
34	3.72	021801	Various tips and info relating to PHP scripting and mySQL TDavid reflects
35	3.71	072900	how to shuffle the contents of an array in php
36	3.69	122699	Reading and using files in php
37	3.68	051003	Using mktime, date to track tasks and skip future dates
38	3.66	011000	mySQL database basics and PHP
39	3.65	071400	admin interface for adding and deleting users creating dynamic dropdown select lists using mysql and php
40	3.65	010503	Part 2: How to build a convention planner script php mySQL
41	3.65	010200	Using loops in PHP to send mail to multiple recipients
42	3.60	012100	Shuffling a deck of playing cards using PHP
43	3.59	011503	How to install and configure PostgreSQL and use with PHP
44	3.53	123199	Password protection in php, .htaccess and browser based admin areas
45	3.50	010203	Part 1: How to build a convention planner script php mySQL
46	3.43	123102	XML RSS 0.91 webmaster surfer uses, html validation, W3C Document Definition
47	3.42	030301	how to install php scripts installation instruction
48	3.33	011403	TOS Script GPL Licensing, legalities of scripting
49	3.11	122802	useful built-in php functions: arrays, strings, image, IRC, homeroom discussion forum

Looking at the source code some notes: we used an if condition to test what the color was of the last table column and change it to the other one.

Please vote on the usefulness of this diary entry so other people will know if it is worth their time to read :)

[back] 07/31/00 "Sorting and displaying results in mult-colored tables using mySQL" [next]

PHP Diary | scriptschool.com | PHP Scripts | TD Scripts.com