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!

    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

      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)";

        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

          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

            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.

              Write a Reply...