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

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