How to filter on a date in MySQL and PHP?

Question

I'm trying in my visitor-book that people can do only one post in one day, but I don't know how it goes. Here's the code:

<?php
        $user = $username;

        $timestamp = time();
        $date = date("Y.m.d", $timestamp);

        // Look if the user has written something..
        $sql="SELECT user, date FROM book WHERE user='$user' AND date=???"; 
        $qry=mysql_query($sql);
        $num_rows = mysql_num_rows($qry); 
        mysql_query($sql);

        if($num_rows > 0) {
        ?>

        Today you have written.

        <?php
        } else {
        ?>

        Action....

        <?php } ?>

I'm sure that $date can't be at "???" because $date is the current date. I need the date of the record in the database.

Would you add more detail to the question? Presumably you are not literally using `???` in your query. — halfer, Nov 22 '14 at 18:20

score 2 · Answer 1 · answered Nov 22 '14 at 17:50

2

You need something like this:

SELECT user, date FROM book WHERE user='$user' AND date= CURDATE();

MySql: Date and Time Functions

answered Nov 22 '14 at 17:50

jherran

3,337
8
37
54

that does not answer the question at all. He is looking for : *I need the date of the record in the database*. – Nov 22 '14 at 18:00
@Begueradj: the OP appears to be wanting to limit users to making one post a day. His **or her** code seems to be looking to see if a particular user has posted on a particular day, and thus this answer seems to fit. – halfer Nov 22 '14 at 18:19
any way i did not downvote your answer (some one did) because the question is not clear to me at all – Nov 22 '14 at 18:21

score 0 · Accepted Answer · answered Nov 22 '14 at 18:04

You have to set last 24 hours date and then set the condition as greater than, here if it returns records, so the user has been wrote, and if there is no records so he/she does not write any thing in the last 24 hours. 86400 are the number of seconds in one day.

$datelimit = date("Y-m-d h:i:s", time()-86400);
$sql="SELECT user, date FROM book WHERE user='$user' AND date >'$datelimit'";

score 0 · Answer 3 · answered Nov 22 '14 at 18:46

so simple!

[book writing]

//...
mysql_query(sprintf("INSERT INTO book(user,date,text) VALUES('%s',%d,'%s');",$username,mktime(0,0,0),$text);

[book reading]

<?php
$user = $username;

$sql="SELECT user, date FROM book WHERE user='$user' AND date=".mktime(0,0,0); 
$qry=mysql_query($sql);
$num_rows = mysql_num_rows($qry);

if($num_rows > 0) {
?>

Today you have writed.

<?php
} else {
?>

Action....

<?php } ?>

How to filter on a date in MySQL and PHP?

3 Answers3