Jump to content

MySQL Fetch Array Loop If Statement


old_blueyes

Recommended Posts

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

Link to comment
Share on other sites

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 />";
}
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Psycho
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.