Help with a search clause...

[DBookatay]

I have a search page, that pulls data from my dB based on several criteria, and I want to add  a new criteria: status = "New".

"New" meaning I have a field named "submitted" that when a user submits a form it uses "NOW()" and saves it as timestamp: "2007-03-19 19:22:21," I want "New" to pull all the forms submitted within the last 7 days, but I cant get my script to work correctly. Here is what I have:

   $where[] = "submitted <= '" . date('Y-m-d', strtotime('-7 days')) . "'";

   $query = "SELECT * FROM $table $where ORDER BY $order";
   $result = mysql_query($query) or die(mysql_error()); if($result) {
   $numrows = mysql_num_rows($result); 
   while($row = mysql_fetch_array($result)){

   }

 

All of my other "$where[]" work correctly, so the only problem I have is determining what the 7 days are

"submitted <= '" . date('Y-m-d', strtotime('-7 days')) . "'";

 

Does anyone have any suggestions?

 

[cooldude832]

mysql dates are a different format

<?php
$mysql_7daysago = date("Y-m-d G:i:s", strtotime("-7 Days"));
?>

proper syntax for mysql 7 days ago date.

[DBookatay]

mysql dates are a different format

<?php
$mysql_7daysago = date("Y-m-d G:i:s", strtotime("-7 Days"));
?>

proper syntax for mysql 7 days ago date.

 

So how do I write my line? :

$where[] = "submitted $mysql_7daysago = date("Y-m-d G:i:s", strtotime("-7 Days"));";

 

[cooldude832]

no you need to use it as an example for how to put dates in a format for mysql

[DBookatay]

   $mysql_7daysago = date("Y-m-d G:i:s", strtotime("-7 Days"));

   if($_GET['status'] == "New") {
      $where[] = "submitted <= $mysql_7daysago";
   } else {
      $where[] = "status = '{$_GET['status']}'";
   }

 

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 '== 2008-01-17 20:37:38 and status = 'New' ORDER BY submitted DESC, ap_nmL ASC, a' at line 1"

[cooldude832]

well considering you aren't showing where the dual equal sing is comming from your making it hard to guess whats wrong.

[DBookatay]

well considering you aren't showing where the dual equal sing is comming from your making it hard to guess whats wrong.

 

I dont understand what you mean, I showed all the code, or at least I thought I did...