MySQL Possibly Query Error

[Bopo]

Hey

 

Well I'm trying to write this query which will return records dated from today to the past 30 days, however I keep getting the following error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL

 

Below is the related coding

 

		<form id="form1" name="form1" method="post" action="">
  <label>Comments From:
    <select name="days" id="days">
      <option value="1day">Today</option>
      <option value="2day">Yesterday</option>
      <option value="7day">Past 7 Days</option>
      <option value="30day">Past Month</option>
      <option value="allday">All</option>
    </select>
  </label>
  <label>
    <input type="submit" name="submit" id="submit" value="Go" />
  </label>
</form>

 

<?php

session_start();

if($_SESSION['loggedin'] == "correct") {
echo "welcome";
} else {
header('Location: http://www.koicarpadvice.com/admin/login.php');
}

if(isset($_POST['submit'])) {

$getdate = $_POST['days'];
$todaysdate = date("m.d.y"); 

if($getdate == "30day") {
$day = 30;
$sql = "SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '$day' DAY) <= $todaysdate";  //date = column field in table

echo $sql;
}

include("blogconnect.php");

$query = mysql_query($sql, $connect); 
while ($row = mysql_fetch_assoc($query)) {   //this is where the error is highlighted

echo $row['id'] . '<br /><br />';
echo $row['comments'] . '<br /><br />';

}
}
?>

[lonewolf217]

you echo out $sql already.

 

throw it into SQL manually and execute it to see what the problem with it is

[Bopo]

Thanks for the sugestion

 

Heres what I tried using in PHPMyAdmin against the table

 

SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '30' DAY) <= 03/01/09

 

Below is the error,

 

Error

 

SQL query: Documentation

 

SELECT *

FROM comments

WHERE date( CURDATE( ) , INTERVAL '30'

DAY ) <=03 /01 /09    <--- line the error occured.

LIMIT 0 , 30

 

MySQL said: Documentation

#1064 - 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 '30' DAY) <= 03/01/09

LIMIT 0, 30' at line 1

[lostnucleus]

well $todaysdate = date("m.d.y"); its not correct format of mysql date, shd be used YY-MM-DD

[Maq]

Please read up on your MySQL Date functions.  You're using them incorrectly.  There are examples in the link I provides that are similar to yours.

[Bopo]

Woah I see what you mean hopefully, I need to sort the table out a little due to the date format, and hopefully it will be resolved.

[Maq]

Woah I see what you mean hopefully, I need to sort the table out a little due to the date format, and hopefully it will be resolved.

 

I prefer TIMESTAMPS, makes everything easier.

[Bopo]

Okay I just can't get a date stored into each record, I've tried the various date formats, date, datetime, timedate, and tried a variety of methods to generate today's date. It's 5am and I'm being stubborn an won't go to sleep until it works.

 

To generate todays date, in the format 2009-03-11 you would use something like

 

$date = date('yyyy/mm/dd');

 

sorry if that's a silly suggestion, just that every example I find is overcomplexed.

[Maq]

Okay I just can't get a date stored into each record, I've tried the various date formats, date, datetime, timedate, and tried a variety of methods to generate today's date. It's 5am and I'm being stubborn an won't go to sleep until it works.

 

To generate todays date, in the format 2009-03-11 you would use something like

 

$date = date('yyyy/mm/dd');

 

sorry if that's a silly suggestion, just that every example I find is overcomplexed.

 

I don't know what you're trying to ask here.

 

But for the format you're trying to do for the date should look like this:

 

$date = date('Y/m/d');
echo $date;
?>