need help in sql database to retrieve day from date

Question

I'm fetching data from the sql database

$sql = mysql_query("SELECT * FROM entries WHERE id='$id'");

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

    $title = $row["title"];
    $contents = $row["contents"];
    $author = $row["author"];
    $date = $row["date"];
     $date = strftime("%b %d, %y", strtotime($date));   

     }

and then I use print $date to view the date. How can also get the day from that date..like sunday, monday ?

Since you tagged it with `sql`, you should read this answer: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496 — Turophile, Jul 02 '15 at 22:36
It doesn't answer my ques. It's for preventing sql injection and the code I provided in the question is just an example to describe my problem. — zauzav gautam, Jul 02 '15 at 22:41
You should also take note [of this comment](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#comment19286459_60174). — Turophile, Jul 02 '15 at 22:43
Thing is, I'm not a php programmer, but I am an sql programmer, so I thought I'd still try to help. — Turophile, Jul 02 '15 at 22:44
possible duplicate of [Retrieving Day Names in PHP](http://stackoverflow.com/questions/7765469/retrieving-day-names-in-php) — Turophile, Jul 02 '15 at 22:47
If you store the DATETIME format in database, you could use sql query to get day of the week and dont have to convert them using PHP. — thanyaj, Jul 02 '15 at 22:51

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

See this answer.

In summary: $day = date('l', strtotime($date));

edited May 23 '17 at 11:43

Community

1
1

answered Jul 02 '15 at 22:45

Turophile

3,367
1
13
21

score 0 · Answer 2 · answered Jul 02 '15 at 22:53

0

If you want to use the query to return day of week instead of using PHP, You could use DATE_FORMAT function that is built into the database like this.

 select *, DATE_FORMAT(entryDate, '%W') as dayOfWeek from entries

answered Jul 02 '15 at 22:53

thanyaj

302
1
6

score 0 · Answer 3 · edited May 23 '17 at 12:21

Another option is to use the MySQL function DAYOFWEEK:

SELECT *, DAYOFWEEK(`date`) AS day FROM entries WHERE id='$id'

And a full PHP example with your code (even though my strong opinion is that you should switch to using PDO or MYSQLI driver)


$days = array(
    'Sunday',
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
    'Saturday',
);


$sql = mysql_query("SELECT *, DAYOFWEEK(`date`) AS day FROM entries WHERE id='$id'");

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

    $title = $row["title"];
    $contents = $row["contents"];
    $author = $row["author"];
    $date = $row["date"];
    $date = strftime("%b %d, %y", strtotime($date));   
    $day = $days[$row["day"]-1];
     }

score 0 · Accepted Answer · answered Jul 02 '15 at 22:56

0

Try this $day = date('l', strtotime($date));

answered Jul 02 '15 at 22:56

ιηςπεδιδιε ζαυπαυ

97
7

need help in sql database to retrieve day from date

4 Answers4