CODEWITHSUNDEEP
phpmysqldatabaseselectwhere-clause
Apromer
Apromer

Reputation: 57

How to filter on a date in MySQL and PHP?

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.

Upvotes: 2

Views: 781

Answers (3)

Dienes L&#225;szl&#243;
Dienes L&#225;szl&#243;

Reputation: 84

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

Upvotes: 0

SaidbakR
SaidbakR

Reputation: 13544

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'";

Upvotes: 0

jherran
jherran

Reputation: 3367

You need something like this:

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

MySql: Date and Time Functions

Upvotes: 2

Related Questions