MySQL Fetch Array Loop If Statement

[old_blueyes]

Hi,

 

Does anyone have a clue how I might solve this little issue:

 

I have a MySQL query, for example:

<?php
// Make a MySQL Connection
$query = "SELECT * FROM staff";
    
$result = mysql_query($query) or die(mysql_error());


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

    if($row['clock'] < time()) {
    echo "<b>"$row['name']. " - ". $row['age']"</b>";
    echo "<br />";

    } else {

    echo $row['name']. " - ". $row['age'];
    echo "<br />";

    }
}
?>

Taking data from the following table setup:

 

name - age - clock

Timmy Mellowman - 23 - 09:00:00
Sandy Smith - 21 - 12:00:00
Bobby Wallace - 15 - 14:00:00

 

What im trying to achieve is compare the current time to the time in the clock column and depending if it's earlier or later, change the styling of the results.

 

The above code does appear to work somewhat however it seems to change the styling of all results in one go, rather than individually when it passes the time in the cell, which is what im looking for.

 

Thanks

[Ch0cu3r]

Your database returns the time in 24 hour format, which will be represented as a string. You are comparing that against  time()  which returns a unix timestamp (number of seconds passed since Jan 1 1970).  PHP will not convert this to a 24 hour format nor will it convert your database times to a timestamp.

 

You need to convert $row['clock'] to a timestamp in order to compare it to time(). Here is an example

while($row = mysql_fetch_array($result))
{
    $output = $row['name']. ' - ' . $row['age'];

    // explode, the time into its components (hour, minutes, seconds)
    list($hours, $minutes, $seconds) = explode(':', $row['clock']);

    // build a new timestamp using mktime(), compare the new timestamp with the current time
    if(mktime($hours, $minutes, $seconds) < time())
    {
       // bold string if timestamp is less than the current time.
       $output = "<b>$output</b>";
    }

    echo "$output<br />";
}

[ginerjm]

You also need to turn on php error checking so you can be warned about your syntax errors.  This statement:

 

 

    echo "<b>"$row['name']. " - ". $row['age']"</b>";

 

is faulty.  You have a string followed by a var name which the php interpreter is not going to recognize.  You have a var followed by a string at the end.  The interpreter is not going to accept those situations.

This:

 

    echo "<b>" . $row['name'] . " - " . $row['age'] . "</b>";

 

will work.

[Psycho]

A simpler solution is to modify the query to return a boolean value for each record to determine if it is earlier or later than the current time rather than converting and comparing each date in PHP (not the speediest process). Plus, you should not replicate code. The two outputs are nearly identical save for the bold format. So, create one code block for the output with a variable to make it bold or not.

// Make a MySQL Connection
$query = "SELECT name, age, (clock < NOW()) as past FROM staff";
$result = mysql_query($query) or die(mysql_error());
 
while($row = mysql_fetch_assoc($result))
{
    $fontWeight = ($row['past']) ? 'bold' : 'normal';
    echo "<span style='font-weight:{$fontWeight};'>{$row['name']} - {$row['age']}</span><br />\n";
}

Edited August 1, 2014 by Psycho