query by current weekend
Moderator: General Moderators
query by current weekend
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
Take a look at MySQL Date and Time functions, especially:
http://dev.mysql.com/doc/refman/5.1/en/ ... _dayofweek
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
Re: query by current weekend
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?
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
CURDATE() and DAYOFMONTH() are MySQL functions, not PHP ones. You must use them in a SQL query.cupaball wrote:Code: Select all
function show_weekend() { $date = CURDATE(); $day_of_the_week = DAYOFMONTH($date);
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
Re: query by current weekend
Thanks for your help. And I apologize about the comment earlier. Just a little furstrated with this project.!!!!
Re: query by current weekend
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
I got it!!
I had to use STR_TO_Date.
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"
. " ";