Quick Tutorial on Displaying SQL Query Results


#1

By Steve Knoblock

This quick tutorial illustrates using functions that generate HTML to create start and end table rows. This is a technique, creating three functions, two to create the start (including header row) and end table sections and a middle section generated by looping construct that outputs HTML table rows filled in with data. This is great for standardizing and gaining control over the HTML tables your applications generate. A lot better than a bunch pages with code randomly strewn through HTML tables. It keeps the code in one place, it keeps you pages simple by only having to drop a function call into it where you want the table. YOu can take advantage of the function to set the appearance or names of table headings.

Tip: It’s a good practice to name all functions that generate HTML starting with “make_”. This makes it clear which functions output HTML.

    <?php

    function make_users_table_start() {

        $content = '<table width="100%" border="0" cellspacing="1"

         cellpadding="2" bgcolor="#000000">

    <tr bgcolor="#e7e7d6">

    <td><font face="Verdana,Arial,sans-serif"

     size="-1"><strong>User Name

     </strong></font>



     </td><td><font

     face="Verdana,Arial,sans-serif" size="-1">

     <strong>Name</strong></font></td><

     td><font face="Verdana,Arial,sans-serif" size="-1">

     <strong>Email Address</strong></font></td>



     <td><font face="Verdana,Arial,sans-serif" size="-1">

     <strong>Member Group</strong></font></td><td>

     <font face="Verdana,Arial,sans-serif" size="-1">

     <strong>Member Since</strong></font>



     </td>';

        return $content;

    }



    function get_alt_row_color($i,$light_color="#ffffff",$dark_color="#E6EFFD") {

    if ($i % 2 == 0) {

            return $light_color;

        } else {

            return $dark_color;

        }

    }

    ?>

The get_alt_row_color() function adds a little eye candy and readability to the table.

<?php

function make_users_table_end() {

$content = '</table>';

return $content;

}



function browse_users() {

$sql = "SELECT

 user_id, user_pass, personal_name, family_name,

 user_group, email_address,

 DATE_FORMAT(date_created, $date_format) AS member_since

  FROM user ORDER BY user_id DESC";

$result=mysql_query($sql);

if (!$result) {

    print mysql_error()." ERROR - browse query failed.";

}



while($row = mysql_fetch_array($result)) {

        $content .= '<tr bgcolor="'. get_alt_row_color($line_count). '">';

        $content .= '<td class="normalprint"><font

         face="Verdana,Arial,sans-serif" size="-1">' ."\n";

        $content .= '<a href="user-detail.php'. make_sid()

         .'&user_id='. $row[user_id] .'">'. $row[user_id]

          .'</a></font></td>



         <td class="normalprint"><font face="Verdana,Arial,sans-serif"

          size="-1">'. $row[personal_name] .' '. $row[family_name];

        $content .= '<td class="normalprint"><font

         face="Verdana,Arial,sans-serif" size="-1">' ."\n";

        $content .= $row[email_address];

        $content .= "</font>\n\t</td>\n";

        $content .= '<td class="normalprint"><font

         face="Verdana,Arial,sans-serif" size="-1">' ."\n";

        $content .= $user_group[$row[user_group]];

        $content .= "</font>\n\t</td>\n";

        $content .= '<td class="normalprint"><font

         face="Verdana,Arial,sans-serif" size="-1">'.

          $row[member_since] ."</td>\n";

        $content .= "</font>\n\t</td>\n</tr>\n";

        $line_count++;

    }

}

?>

Adding the output of this query is as easy as adding three funtion calls to a PHP page. It doesn’t even matter hwat directory it’s in.

<?php

  print make_users_table_start();



  print browse_users($start_list);



  print make_users_table_end();

?>

Tip: Don’t forget the print statement! Otherwise, you’ll get nothing.

Or you can create a wrapper function for all of them. Place it anywhere on any PHP page and you’re ready to go!

<?php

function browse_box() {



  print make_users_table_start();



  print browse_users($start_list);



  print make_users_table_end();

}

?>