SELECT * FROM $table_name WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND

[MarkBad311]

Edited

[devinemke]

what version of mySQL are you using?

from the manual at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

The values QUARTER and WEEK are available beginning with MySQL 5.0.0.

[MarkBad311]

I am using 4.1.13-standard how can I get this to work? I basically need it to break it in to monday through sunday. and then on the next monday everything changes.

I will need it to be this way to offer querys on next week and archives upon request.

Thanks!

[rincewind456]

Then why not use interval 7 days? Syntax here.

[MarkBad311]

Am I really asking fro the right thing? I want it to change every monday really.

So if I set it this way

mysql> SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

I just switch the 30 DAY with 7 DAY instead of the Syntax: WEEK

[Roger_Ramjet]

Well, if you want to just show this week's data then you can generalise the problem by using the

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('1998-02-03');
-> 3

So, if you want to change the display every Monday, get the daynumber for today and use that minus 1 to get the date of the Monday and +6 to get the date of the Sunday.

if it is Wednesday then dayofweek returns 3, so Monday is

date_sub(curdate() - interval (dayofweek(curdate()) -1) day)

A messy looking expression that could be reformed thus

$SQL = "SELECT *, curdate() AS c, dayofweek(curdate()) AS d  FROM $table_name WHERE date BETWEEN date_sub(c  INTERVAL (d -1) DAYS) AND date_add(c INTERVAL (7-c) DAYS)";

[MarkBad311]

Still getting a error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERVAL (d -1) DAYS) AND date_add(c INTERVAL (7-c) DAYS)' at line 1

[MarkBad311]

Newest version of the file with just the PHP in it for easier viewing

<?php
$db_name = "markbad_markbadsql";
$table_name = "event";
$connection = mysql_connect ("localhost", "markbad_drpl1", "n4x4q37IhCez")
or die ('I cannot connect to the database because: ' . mysql_error());
$db = mysql_select_db ($db_name, $connection)
or die (mysql_error());
//sort by date.. only monday thru friday
$sql = "SELECT *, curdate() AS c, dayofweek(curdate()) AS d  FROM $table_name WHERE date BETWEEN date_sub(c INTERVAL (d -1) DAYS) AND date_add(c INTERVAL (7-c) DAYS)";
$result = mysql_query($sql,$connection)
or die (mysql_error());
//While loop will create an array called $row
while ($row = mysql_fetch_array($result)) {
// get individual elements from events
  $date = stripslashes($row['date']);
  $bar = stripslashes($row['bar']);
  $updated = stripslashes($row['updated']);
  $details = stripslashes($row['details']);
  $map_url = stripslashes($row['map_url']);
   $display_block .= "
    \t<h4>$bar <span class=\"date\"> $updated</span></h4>
    \t<p>
    \t\t&details<br />
    \t\t<span class=\"date\">$map_url</span>
    \t</p>";
    }
?>

<!-- Bunch of HTML -->

 <? echo "$display_block"; ?> 

The two things I am concerned about to re-cap is:

1) Displaying the data that is less then a week then the current date and not any furture dates that are set in the table past the current date.

2) Am I using stripslashes correctly?

this is the MySql error I am getting still:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERVAL (d -1) DAYS) AND date_add(c INTERVAL (7-c) DAYS)' at line 1

[Roger_Ramjet]

Not thinking straight, me bad 🙁 .Missing a comma in the date functions. May also not work with my substituting c and d for the functions - just don't want to have to call curdate() 5 times. If the corrected query does not work try the second version.

// version 1
 $SQL = "SELECT *, curdate() AS c, dayofweek(curdate()) AS d  FROM $table_name WHERE date BETWEEN date_sub(c ,  INTERVAL (d -1) DAYS) AND date_add(c , INTERVAL (7-c) DAYS)"; 

// version 2
 $SQL = "SELECT *  FROM $table_name WHERE date BETWEEN date_sub(curdate(),  INTERVAL (dayofweek(curdate()) - 1) DAYS) AND date_add(curdate(), INTERVAL (7 - dayofweek(curdate())) DAYS)";

You may have to play around with the add and subtract params to get the days you want.