CODEWITHSUNDEEP
phpmysql
aWebDeveloper
aWebDeveloper

Reputation: 38342

Mysql date problem

i have a date field in db.

  1. I want all the data between Saturday Sunday and Monday .
  2. Of this data i want only 1 record that is record whose date is the next coming date

Hope i make my self clear

Upvotes: 0

Views: 115

Answers (4)

aWebDeveloper
aWebDeveloper

Reputation: 38342

SELECT * FROM table WHERE DATEDIFF(CURDATE(), date)>0 AND (DAYOFWEEK(date) in (1,2,7) ORDER BY date LIMIT 0,1

Upvotes: 0

Adam Lynch
Adam Lynch

Reputation: 3369

This will return the data you need from the database:

//once you've connected to the DB
$db_query= "SELECT * FROM table 
            WHERE DATEDIFF(CURDATE(), date)>0 
            AND (DAYOFTHEWEEK(date)<3 OR DAYOFTHEWEEK(date)==7)
            ORDER BY date
            LIMIT 0,1";//limits it to one (date) result
$result=mysql_query($db_query);
if(!$result){ die('ERROR: Query failed.'); }

This will print the resultant row on screen:

while($ROW=mysql_fetch_assoc($result))
{
    echo "<p>date: {$ROW['date']}</p>";
    //do the same for all the fields you want like so:
    echo "<p>other field: {$ROW['other field']}</p>";
}

mysql_free_result($result);

Upvotes: 0

hsz
hsz

Reputation: 152206

$from = strtotime('saturday');
$to   = strtotime('tueseday') - 1; // whole monday

mysql_query('SELECT * FROM table WHERE date >= ' . $from . ' AND date <= ' . $to . ' ORDER BY date LIMIT 1');

Upvotes: 0

Flask
Flask

Reputation: 4996

SELECT DAYOFWEEK(date) as wd FROM table WHERE wd > 1 AND wd < 6 AND date >= CURRENT_TIMESTAMP() ORDER BY date ASC LIMIT 1;

Upvotes: 1

Related Questions