CODEWITHSUNDEEP
phpmysql
Joshua
Joshua

Reputation: 35

How to output day of week from MySQL date

I'm having trouble finding or figuring out how to output a MySQL date field as the abbreviated day of week.

I'm not sure whether to do this in MySQL or PHP.

Ideally I'd like to do this with a PHP function, but to complicate things, the date field that I want to output as day of week is running through a while loop into a table. This makes me think I need to do it in MySQL.

Basically, I want to be able to use something like PHP's mktime() or MySQL's DAYOFWEEK(), except since I need to be able to do this in the while loop, I need to be able to use a variable or column name in the function instead of having to input or pull one specific date for formatting.

Any help is greatly appreciated!

P.S. I've added below how the data is currently coming from the database. The ### is where I'm having trouble; this is where I need to to echo the Day using the 'e_date' column. (The next Date also uses the 'e_date' column.)

// Get all the data from the "events" table
            $result = mysql_query("
            SELECT *
            FROM events
            WHERE e_type != '' && e_date >= CURDATE() && e_date <= (CURDATE() + 15)
            ORDER BY e_date,e_ampm,e_time") 
            or die(mysql_error());  

            echo "<table border='1' style=\"border:none;font-size:12px;\">";
            echo "<tr> <th>Day</th> <th>Date</th> <th>Time</th> <th>Type</th> <th>Description</th> </tr>";
            // keeps getting the next row until there are no more to get
            while($row = mysql_fetch_array( $result )) {
                // Print out the contents of each row into a table
                echo "<tr><td>";
                ###
                echo "</td><td>";
                echo $row['e_date'];
                echo "</td><td>";
                echo $row['e_time'];
                echo $row['e_ampm'];
                echo "</td><td>";
                echo $row['e_type'];
                echo "</td><td>";
                echo $row['e_name'];
                echo "</td></tr>"; 
            } 

            echo "</table>";

Upvotes: 3

Views: 2046

Answers (3)

You Qi
You Qi

Reputation: 9211

My advice is always deal with date in unixtime format. I always store date as integer field in database.

For your case, you may query the datefield by converting it into unixtime and let php date function to handle the formating.

<?php

$query = "
SELECT UNIX_TIMESTAMP(e_date) AS UNIXTIME, *
FROM events
WHERE e_type != '' && e_date >= CURDATE() && e_date <= (CURDATE() + 15)
ORDER BY e_date,e_ampm,e_time";

$result = mysql_query($query) or die("error");
...

while($row = mysql_fetch_array( $result ))
{
    echo "<tr>";
    echo sprintf('<td>%s</td>', date('l', $row["UNIXTIME"])); //day of week
    ...
    echo "</tr>";
}

?>

Upvotes: 0

Johnnyoh
Johnnyoh

Reputation: 369

Try this modified version. You can use DATE_FORMAT, use the datefield as the input variable, and %a signifies the formatting for the output. %a tells DATE_FORMAT to return the abbreviated day of the date. Give it a shot and see if it works, I haven't used MySQL in a while so I could be wrong.

// Get all the data from the "events" table
        $result = mysql_query("
        SELECT DATE_FORMAT(e_date, '%a') as day, e_date, e_time, e_ampm, e_type,e_name
        FROM events
        WHERE e_type != '' && e_date >= CURDATE() && e_date <= (CURDATE() + 15)
        ORDER BY e_date,e_ampm,e_time") 
        or die(mysql_error());  

        echo "<table border='1' style=\"border:none;font-size:12px;\">";
        echo "<tr> <th>Day</th> <th>Date</th> <th>Time</th> <th>Type</th>    <th>Description</th> </tr>";
        // keeps getting the next row until there are no more to get
        while($row = mysql_fetch_array( $result )) {
            // Print out the contents of each row into a table
            echo "<tr><td>";
            echo $row['day'];
            echo "</td><td>";
            echo $row['e_date'];
            echo "</td><td>";
            echo $row['e_time'];
            echo $row['e_ampm'];
            echo "</td><td>";
            echo $row['e_type'];
            echo "</td><td>";
            echo $row['e_name'];
            echo "</td></tr>"; 
        } 

        echo "</table>";

Upvotes: 2

Eli Sand
Eli Sand

Reputation: 1032

I would keep the database part to dealing with the data, and presentation to PHP. You want to use strftime() or strptime() depending how your data is coming out of MySQL.

Upvotes: 0

Related Questions