Page 1 of 1

query by current weekend

Posted: Sun Feb 08, 2009 6:10 am
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?

Re: query by current weekend

Posted: Sun Feb 08, 2009 12:10 pm
by VladSun
Take a look at MySQL Date and Time functions, especially:
http://dev.mysql.com/doc/refman/5.1/en/ ... _dayofweek

Re: query by current weekend

Posted: Mon Feb 09, 2009 1:15 pm
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";
 
}
 
}
 
}
 

Re: query by current weekend

Posted: Mon Feb 09, 2009 4:14 pm
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

Re: query by current weekend

Posted: Tue Feb 10, 2009 8:59 am
by cupaball
Thanks for your help. And I apologize about the comment earlier. Just a little furstrated with this project.!!!!

Re: query by current weekend

Posted: Thu Feb 12, 2009 4:55 am
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 ";
 
 

Re: query by current weekend

Posted: Thu Feb 12, 2009 5:50 am
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"
    . " ";