SunQuest
 
       PHP
  Home arrow PHP arrow Alternating Row Colors With MySQL Results
Moblin
Dev Articles Forums 
ADO.NET  
Apache  
ASP  
ASP.NET  
C#  
C++  
ColdFusion  
COM/COM+  
Delphi-Kylix  
Design Usability  
Development Cycles  
DHTML  
Embedded Tools  
Flash  
Graphic Design  
HTML  
IIS  
Interviews  
Java  
JavaScript  
MySQL  
Oracle  
Photoshop  
PHP  
Reviews  
Ruby-on-Rails  
SQL  
SQL Server  
Style Sheets  
VB.Net  
Visual Basic  
Web Authoring  
Web Services  
Web Standards  
XML  
Dedicated Servers  
Actuate Whitepapers 
Moblin 
JMSL Numerical Library 
IBM® developerWorks 
Sun Developer Network 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
PHP

Alternating Row Colors With MySQL Results
By: Eric 'phpfreak' Rosebrock
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 9
    2003-04-01

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

    You may wish to make you display stand out from the rest. A way of doing this, as Eric reports, is to use PHP to alternate the colors for every line of output.

    One of those popular ways to display your database queries is with alternating row colors inside of a table. Let's face it, if you are displaying any number of items inside your database, you should be using tables. This allows better formatting and better display of your results. If you would like to spice it up a little you can change the color of every other row inside your table and allow the results to be displayed in a manner which the user can read them easier. In this tutorial I will show you how.

    Let's start out with two basic colors for your row. I think I am going to use these colors because they fit the theme of our website. #CCFFCC for my odd number of rows and #BFD8BC for my even number of rows. Now that I have determined what colors I want my rows to be, I will move on.

    The first portion of any MySQL query inside PHP you should always put your table headers outside of your array, and then decide which of the table rows(<TR></TR>) and table data (<TD></TD>) you want to be inside the array for the results. Here's the code flow for this tutorial. I will break it down on the next page.

    // Begin your table outside of the array
    echo "<table width="100%" border="0" cellpadding="4" cellspacing="0">
        <tr>
            <td width="110"><b>Event Date</b></td>
            <td><b> Event Title</b></td>
        </tr>";
    // Define your colors for the alternating rows
    $color1 = "#CCFFCC"; 
    $color2 = "#BFD8BC"; 
    $row_count = 0;
    // Perform an statndard SQL query:
    $sql_events = mysql_query("SELECT date_format(event_date, '%M %d, %Y') as event_date,
    event_title FROM my_events ORDER BY event_date      ASC") or die (mysql_error());
    // We are going to use the "$row" method for this query. This is just my preference.
    while ($row = mysql_fetch_array($sql_events)) {
        $event_date = $row["event_date"];
        $event_title = $row["event_title"];
        /* Now we do this small line which is basically going to tell
        PHP to alternate the colors between the two colors we defined above. */
        $row_color = ($row_count % 2) ? $color1 : $color2;
        // Echo your table row and table data that you want to be looped over and over here.
        echo "<tr>
        <td width="110" bgcolor="$row_color" nowrap>
        $article_date</td>
        <td bgcolor="$row_color">
        <a href="articles.php?cmd=full_article&article_id=$article_id">$article_title</a></td>
        </tr>";
        // Add 1 to the row count
        $row_count++;
    }
    // Close out your table.
    echo "</table>";

    Ok, let's break this down.

    Code Breakdown

    Let's define what we have done here to give you a better understanding.

    Start the Table
     
    // Begin your table outside of the array
    echo "<table width="100%" border="0"
    cellpadding="4" cellspacing="0"><tr>
    <td width="110"><b>Event Date</b></td>
    <td><b>Event Title</b></td></tr>";

     
    I mentioned above that you want to start your table outside of the loop or array that you are going to display your data. The reason for this, as any experienced PHP developer may know, is so you can loop the rows and not the tables. Either way is fine, but for the course of this tutorial and my own coding methods, I use this way of doing things. This particular table will look something like below. I put a border size of 1 on my table just to show you in my example the borders. In my code I do not use borders unless absolutely necessary.

    Event Date Event Title

    Define The Color Variables

    // Define your colors for the alternating rows
        $color1 = "#CCFFCC"; 
        $color2 = "#BFD8BC"; 
        $row_count = 0;

     
    This is pretty straight forward. We are defining the colors that we decided to use and then setting the initial $row_count variable to 0. Later on we will add 1 to the row_count for every number of rows we find in the database.

    Do the MySQL Query

    // Perform an statndard SQL query:
    $sql_events = mysql_query("SELECT date_format(event_date, '%M %d, %Y') as event_date,
    event_title FROM my_events ORDER BY event_date ASC") or die (mysql_error());

     
    We won't teach you how to do an SQL query in this tutorial. You should already know how to do that if are trying to alternate your rows. However, in this SQL statement, we are pulling from two coloumns in our table. event_date and event_title for each of the events in our database will be displayed in our table rows below.

    Use the SQL Query

    // We are going to use the "$row" method for this query. This is just my preference.
    while ($row = mysql_fetch_array($sql_events)) {
        $event_date = $row["event_date"];
        $event_title = $row["event_title"];

     
    There are quite a few ways that you can use the $sql_events variable we defined in the previous step. For this tutorial and my methods of coding, I use this one here. All we are doing is fetching an array from the $sql_events and defining the results as variables to be used later.

    Equation

    $row_color = ($row_count % 2) ? $color1 : $color2;
     
    In order for this to work properly, we must perform this small equation which is going to decide which color to use in which row of our table. I am almost positive if you add another color varialbe like "$color3" above and you change the $row_count % 2 to $row_count % 3 and then add your third color variable here that you can get 3 colors of alternating rows. I have not tested that yet, so if it works, let me know!

    Define Your Rows

    // Echo your table row and table data that you want to be looped over and over here.
    echo "<tr>
    <td width="110" bgcolor="$row_color" nowrap>
    $article_date</td>
    <td bgcolor="$row_color"><a href="articles.php?cmd=full_article&article_id=$article_id">$article_title</a></td>
    </tr>";
     

    Here you are going to define your rows and table data. Take special note that I have defined the td bgcolor to $row_color. This is required, or this whole thing will not work!

    Add 1 to the Count!

    // Add 1 to the row count
    $row_count++;

    To get your rows to alternate color, you must add 1 to the count. This is the same as saying $row_count +1;

    Close out the Table!

    }
    // Close out your table.
        echo "</table>";

     
    You must end your array with } and then close out the table html tag. That's it you're done!

    Let's see the results

    The Results

    Ok, we've done all of this code and hopefully when we run this script it will work. If you have everything setup correctly, you should be in there. Here's the results that I got from my database query:

    Event DateEvent Title
    May 18, 2002Town Festival
    March 05, 2002Air Show
    March 04, 2002River Cruise
    March 03, 2002City Parade

    That's about it! Now hopefully you should be able to write your own code using alternating row colors!


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

    More PHP Articles
    More By Eric 'phpfreak' Rosebrock

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! Try the IBM SOA Sandbox for Connectivity

    Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic.
    FREE! Go There Now!


    NEW! IBM Rational AppScan Standard Edition V7.7

    Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
    FREE! Go There Now!


    NEW! Webcast: Extreme transaction processing with WebSphere Extended Deployment

    In this webcast, you'll get an introduction to the eXtreme Transaction Processing (XTP) features of WebSphere Extended Deployment and the common architectural traits required by XTP applications. See how WebSphere Extended Deployment's ObjectGrid feature provides a state-of-the-art infrastructure for hosting XTP applications.
    FREE! Go There Now!


    NEW! Hello World: WebSphere Service Registry and Repository

    Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
    FREE! Go There Now!


    NEW! Rational Testing eKits

    Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
    FREE! Go There Now!


    NEW! Download the free Web Application Security eKit

    Discover how IBM Rational AppScan Standard Edition can help you detext vulnerabilities in your web applications in the Web Application Security eKit. IBM Rational AppScan is a leading suite of automated web application security solutions that scan and test for common Web application vulnerabilities. The new Web Application Security eKit provides you with valuable resources, including white papers, demos, and additional information on the benefits of testing your Web applications.
    FREE! Go There Now!


    NEW! Applying lean thinking to the governance of software development

    Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations.
    FREE! Go There Now!


    NEW! Webcast: Calling All Testers! Find Application Vulnerabilities Early in the Development Process Where they are Easier to Fix and Less Risky to your Business

    In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
    FREE! Go There Now!


    Role of Integrated Requirements Management in Software Delivery

    As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
    FREE! Go There Now!


    NEW! Hacking 101

    Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    PHP ARTICLES

    - Making Usage Statistics in PHP
    - Installing PHP under Windows: Further Config...
    - File Version Management in PHP
    - Statistical View of Data in a Clustered Bar ...
    - Creating a Multi-File Upload Script in PHP
    - Executing Microsoft SQL Server Stored Proced...
    - Code 10x More Efficiently Using Data Access ...
    - A Few Tips for Speeding Up PHP Code
    - The Modular Web Page
    - Quick E-Commerce with PHP and PayPal
    - Regression Testing With JMeter
    - Building an Iterator with PHP
    - PHP Frontend to ImageMagick
    - Using PEAR's mimeDecode Module
    - Incoming Mail and PHP






    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway