PHP199

Finding the number of rows returned

This is a small tip I haven't seen on the Wiki which shows you how to print correct statements depending on the number of rows return from your MySQL queryies. The function mysql_num_rows() is the one to look for.

Example

<?
// Database connection here...

$sql = "SELECT title,author FROM comics';

$res = mysql_query($sql);

$total = mysql_num_rows($res);

if ($total == 0) {
  echo 'No rows were returned';
} elseif ($total == 1) {
  echo '1 row was returned';
} else {
  echo $total . ' rows were returned';
}
?>

Viewing contents of array

A good tip for checking the information returned from the database is to use <pre> and print_r() which outputs arrays in a more readable form...

Example

Presuming the following SQL was used on the comics database...

SELECT title,author FROM comics LIMIT 1

This would return the title and author details for the first record in the table.

If you then used mysql_fetch_array() to return the details as an array and output it's contents wrapped in <pre> you get a nicely formatted view of the array and it's contents.

<?
// Database connection here...

$sql = "SELECT title,author FROM comics LIMIT 1';

$res = mysql_query($sql);

$row = mysql_fetch_assoc($res);

echo '<pre>'; print_r($row); echo '<pre>';

?>

Would print the following...

Array
(
    [title] => Elfquest
    [author] => Richard/Pini (Wendi)
)

Can be especially useful when using multidimensional arrays, or when you have a lot of fields returned from the database.

Multidimensional Array Example

Using the earlier example of planets, this is how the $planet2 array would look using this method...

<?
       $planets2 = array
       (
         "Mercury"=> array("dist"=>0.39, "dia"=>0.38),
         "Venus"  => array("dist"=>0.39, "dia"=>0.95),
         "Earth"  => array("dist"=>1.0,  "dia"=>1.0, "moons"=>array("Moon")),
         "Mars"   => array("dist"=>0.39, "dia"=>0.53, "moons"=>array("Phobos", "Deimos"))
       );
       echo '<pre>'; print_r($planets2); echo '</pre>';
?>

Would print...

Array
(
    [Mercury] => Array
        (
            [dist] => 0.39
            [dia] => 0.38
        )

    [Venus] => Array
        (
            [dist] => 0.39
            [dia] => 0.95
        )

    [Earth] => Array
        (
            [dist] => 1
            [dia] => 1
            [moons] => Array
                (
                    [0] => Moon
                )

        )

    [Mars] => Array
        (
            [dist] => 0.39
            [dia] => 0.53
            [moons] => Array
                (
                    [0] => Phobos
                    [1] => Deimos
                )

        )

)