date 1

[yebaws]

Trying to extract rows from a database where the renewal_date
is sooner than 30 days with this code:

//get todays date 
$today = getdate();
//format date to the same format as "renewal_date" datestamp colummn in database ie YYYY-MM-DD -  "mday" = day of month, "mon" = month, "year" = year 
$todaystamp=$today["year"]."-".$today["mon"]."-".$today["mday"];
echo "Todays date: $todaystamp";

$SQL = " SELECT id, business_name, renewal_date FROM listings WHERE paid = '01' AND $todaystamp - 30 < renewal_date";
$retid = mysql_db_query($db, $SQL, $cid);

if (!$retid) { echo( mysql_error()); }
while ($row = mysql_fetch_array($retid)) {
		$id = $row["id"];
		$business_name = $row["business_name"];
		$email = $row["email"];
		$renewal_date = $row["renewal_date"];
		
echo "Soon due for renewal:";
echo "<br/>";
echo "$id";
}

..but not getting the results I expected. Where am I going wrong?

 

[r937]

you don't have to construct a variable for today's date -- just use CURRENT_DATE

mysql date arithmetic uses INTERVALs which always require specifying the interval value, i.e. in this case you want DAY

SELECT id
     , business_name
     , renewal_date 
  FROM listings 
 WHERE paid = '01' 
   AND renewal_date > CURRENT_DATE - 30 [red]DAY[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[audiopro]

$todaystamp is a string not a numeric value so

"2009-01-26" - 30 = ?

Keith

http://www.studiosoft.co.uk

 

[yebaws]

Thanks,

Looks more logical than my approach, but the DAY bit returns a syntax error when I write the command just as you've written it?

 

[yebaws]

mind you, if I take out the DAY an just use CURRENT_DATE - 30 it seems to work fine...?

 

[r937]

the only possible reason is that you aren't using MySQL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

oops, my bad!!! :blush:

try [blue]renewal_date > CURRENT_DATE - [red]INTERVAL[/red] 30 DAY[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[yebaws]

Thanks - works with INTERVAL, so I'll put it in. But it did seem to work without the DAY and the INTERVAL....

 

[r937]

yes, i think it defaults to days

however, it's better to be explicit (it's standard SQL, too)

sorry for the confusion/misdirection



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon