query by current weekend

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cupaball
Forum Commoner
Posts: 85
Joined: Sun Feb 12, 2006 1:46 pm

query by current weekend

Post by cupaball »

I have a database with date and days column and I want to pull only events from the current weekend. Any idea how to acheive this?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query by current weekend

Post by VladSun »

Take a look at MySQL Date and Time functions, especially:
http://dev.mysql.com/doc/refman/5.1/en/ ... _dayofweek
There are 10 types of people in this world, those who understand binary and those who don't
cupaball
Forum Commoner
Posts: 85
Joined: Sun Feb 12, 2006 1:46 pm

Re: query by current weekend

Post by cupaball »

I am at my day job, so I don't have a chance to test, but would something like this work?

I am not sure if I can add to the dates, but am I on the right path?

Code: Select all

 
function show_weekend() {
 
$date = CURDATE();
$day_of_the_week = DAYOFMONTH($date);
 
if $day_of_week = '1'{
 
$date_range_1 = $date - 2;
 
 
$q = "SELECT `tb_events`.`event_title`, `tb_events`.`date`, `tb_events`.`info`\n"
    . "FROM `tb_events`\n"
    . "WHERE  (date BETWEEN $date AND $date_range_1) AND WHERE `tb_events`.`event_title` = 1, 6, 7\n"
    . "ORDER BY `tb_events`.`date` ASC\n"
    . " LIMIT 0, 30 ";
 
$result = $mysqli->query($q) or die($mysqli_error($mysqli));
 
if ($result) {
 
while($row = $result->fetch_object()) {
    $tile = $row->event_title;
    $date = $row->date;
    $info = $row->info;
 
print '<h3>'.$title.'</h3>
<p>'.$date.'</p>
<p>'.$info.'</p>
 
';
print "\n";
 
}
 
 
}
 
else if ($day_of_week = '2') {
 
 
$date_range_1 = $date + 4;
$date_range_2 = $date + 6;
 
 
$q = "SELECT `tb_events`.`event_title`, `tb_events`.`date`, `tb_events`.`info`\n"
    . "FROM `tb_events`\n"
    . "WHERE  (date BETWEEN $date_range_1 AND $date_range_2)\n"
    . "ORDER BY `tb_events`.`date` ASC\n"
    . " LIMIT 0, 30 ";
 
while($row = $result->fetch_object()) {
    $tile = $row->event_title;
    $date = $row->date;
    $info = $row->info;
 
print '<h3>'.$title.'</h3>
<p>'.$date.'</p>
<p>'.$info.'</p>
 
';
print "\n";
 
}
 
}
 
}
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query by current weekend

Post by VladSun »

cupaball wrote:

Code: Select all

 
function show_weekend() {
 
$date = CURDATE();
$day_of_the_week = DAYOFMONTH($date);
 
CURDATE() and DAYOFMONTH() are MySQL functions, not PHP ones. You must use them in a SQL query.
DAYOFWEEK(date)

Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.
mysql> SELECT DAYOFWEEK('2007-02-03');
-> 7
There are 10 types of people in this world, those who understand binary and those who don't
cupaball
Forum Commoner
Posts: 85
Joined: Sun Feb 12, 2006 1:46 pm

Re: query by current weekend

Post by cupaball »

Thanks for your help. And I apologize about the comment earlier. Just a little furstrated with this project.!!!!
cupaball
Forum Commoner
Posts: 85
Joined: Sun Feb 12, 2006 1:46 pm

Re: query by current weekend

Post by cupaball »

okay, I have the concept to pull the weekend, but now my query will not pull the result. I have the colum data type as date but when I try the following I zero results even though there is clearly the date there. What am I doing wrong?

Code: Select all

 
$sql = "SELECT `tb_events`.`event_title`, `tb_events`.`date`\n"
    . "FROM `tb_events`\n"
    . "WHERE (`tb_events`.`date` =2009-03-28)\n"
    . " LIMIT 0, 30 ";
 
 
cupaball
Forum Commoner
Posts: 85
Joined: Sun Feb 12, 2006 1:46 pm

Re: query by current weekend

Post by cupaball »

I got it!!

I had to use STR_TO_Date.

Code: Select all

 
$sql = "SELECT `tb_events`.`event_title`, `tb_events`.`date`\n"
    . "FROM `tb_events`\n"
    . "WHERE (`tb_events`.`date` >=STR_TO_DATE( \'2009-02-14\', \'%Y-%m-%d\' ))\n"
    . "AND (`tb_events`.`date` <=STR_TO_DATE( \'2009-03-14\', \'%Y-%m-%d\' ))\n"
    . " ";
 
Post Reply