CODEWITHSUNDEEP
phpmysqlsqldatabasedatetime
Lion King
Lion King

Reputation: 33823

How can extract data that have specific day from database

I have a table contain questions as a data.
Every question has a row named creation_date, that store the time of creation of the question as unix timestamp.

I want to extract all questions that have specific day, and I don't care about the month or year.

For example:
I want to extract all questions that created on day 16.

What I done:

$today = date('d');

$query = mysql_query("SELECT * FROM questions WHERE qcreation_date = '$today' ");
$numRows = mysql_num_rows($query);
if ($numRows > 0) {
    while ($data = mysql_fetch_assoc($query)) {
        echo $data['questionTitle']."<br/>";
    }
} else {
    echo "There is not any data.";
}

$today is a variable will contain the day of today, for example, if today is 22/2/2004, the $today variable will contain 22 that represent day of today.

Upvotes: 0

Views: 167

Answers (1)

John Conde
John Conde

Reputation: 219934

Use DAY() and FROM_UNIXTIME(). FROM_UNIXTIME() will convert your timestamp into a datetime stamp. DAY() will get the day number from it which you can then compare against.

SELECT * FROM questions WHERE DAY(FROM_UNIXTIME(creation_date)) = '$today'

An alternative way that would reduce your need to use PHP variables in this query would be to use CURDATE():

SELECT * FROM questions WHERE DAY(FROM_UNIXTIME(creation_date)) = DAY(CURDATE())

FYI, a better way to get today's day is just to use $today = date('d'). Using mktime() is much more convoluted.

Upvotes: 1

Related Questions