Geek asks and answers

How to derive day of week from MySQL date

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

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

Ideally, I would like to do this using the PHP function, but to complicate the situation, the date field that I want to display as the day of the week is executed through a while loop to the table. This makes me think that I need to do this in MySQL.

Basically, I want to be able to use something like PHP mktime () or MySQL DAYOFWEEK (), except that I need to be able to do this in a while loop, I need to be able to use a variable or name columns in a function instead to enter or pull out one specific date for formatting.

Any help is much appreciated!

PS I added below how the data is currently coming from the database. ### where I am having problems; this is where I need to repeat Day using the column "e_date". (The next date also uses the column "e_date".)

// 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>";
+3
source share
3 answers

Try this modified version. You can use DATE_FORMAT, use the date field as an input variable, and% a means formatting the output. % a tells DATE_FORMAT to return the shortened day of the date. Give him a chance and see if it works, I have not used MySQL for 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>";
+2
source

PHP. strftime() strptime() , MySQL.

0

My advice always applies to a date in unixtime format. I always store the date as an integer field in the database.

In your case, you can request a date field by converting it to unixtime and letting the php date function handle the formation.

<?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>";
}

?>
0
source

More articles:


All Articles