query by current weekend
Posted: Sun Feb 08, 2009 6:10 am
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?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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";
}
}
}
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
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 ";
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"
. " ";