What have I done wrong?

[TomFromKWD]

I am trying to get the SELECT query to take a date from a field in the database called displaydate and ensure it only shows entries that have a displaydate of the date today or earlier

<?PHP
mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y-%m-%d' as thedate FROM `2011-09` WHERE DATE_SUB(CURDATE(),INTERVAL 31 DAY)  ORDER by day_id DESC");
?>

 

the above code is just kicking out an error so I assume I've got lost somewhere but not sure where

 

can someone please help me correct what I have wrong and explain WHY it is wrong

[AyKay47]

what error..?

going off of strictly what you stated..

 

mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y-%m-%d') as thedate FROM `2011-09` WHERE thedate <= CURDATE() ORDER by day_id DESC");

[TomFromKWD]

the error is as follows

 

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in "path"/2011-09.php on line 49

 

 

and unfortunately the issue persists after the above supplied code

 

could it be to do with how I am storing the date in question in the database?

 

 

and if it helps the full bit is now as follows:

 

<?php

                           include 'connection.php';

                           $getimages=mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y %m %d') as thedate FROM `2011-09` WHERE thedate <= CURDATE() ORDER by day_id DESC");

                           while ($images=mysql_fetch_array($getimages)) {
                           echo " " .$getimages['codeopen']. "" .$getimages['code']. "" .$getimages['codeclose']. ""; }
                         ?> 

[AyKay47]

thats saying that you dont have a table called. "2011-09" in your database..which you call in your query

[TomFromKWD]

it is there though, and without the date alterations im trying to make and just using the following alone works, but obviously is showing EVERYTHING in the table, including that set with a date for the future

 

$getimages=mysql_query ("SELECT * FROM `2011-09` ORDER by day_id DESC");

[AyKay47]

my mistake.. that error simply means that you r query is failing.. add a die() after the query to receive a specific error..

 

mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y-%m-%d') as thedate FROM `2011-09` WHERE thedate <= CURDATE() ORDER by day_id DESC") or die(mysql_error());

[TomFromKWD]

its telling me "Unknown column 'thedate' in 'where clause'"

 

 

[AyKay47]

im not an expert at mysql, don't try to be.. however something is going wrong with your alias of the displaydate field.. so try removing the alias..

 

mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y-%m-%d') FROM `2011-09` WHERE displaydate <= CURDATE() ORDER by day_id DESC") or die(mysql_error());

[TomFromKWD]

on that try weve gone back to it kicking out the original error and no die messgae

 

(the or die is still in the code)

[web_craftsman]

the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of thedate to where clause not yet occurred

$getimages=mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y %m %d') as thedate FROM `2011-09` WHERE DATE_FORMAT(displaydate,'%Y %m %d')<= CURDATE() ORDER by day_id DESC");

 

[jcbones]

$getimages=mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y %m %d') as thedate FROM `2011-09` WHERE DATE_FORMAT(displaydate,'%Y %m %d')<= CURDATE() ORDER by day_id DESC");

 

Running the date_format twice is extra overhead.  Just access the column.

 

$getimages=mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y %m %d') as thedate FROM `2011-09` WHERE displaydate <= CURDATE() ORDER by day_id DESC") or trigger_error(mysql_error());

[TomFromKWD]

mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y-%m-%d') FROM `2011-09` WHERE displaydate <= CURDATE() ORDER by day_id DESC") or die(mysql_error());

 

 

the above worked i just forgot to put my &getimages= on the front like a fool

 

Thanks for all the support guys

 

Youve been immense as always

[AyKay47]

mysql_query ("SELECT *,DATE_FORMAT(displaydate,'%Y-%m-%d') FROM `2011-09` WHERE displaydate <= CURDATE() ORDER by day_id DESC") or die(mysql_error());

 

 

the above worked i just forgot to put my &getimages= on the front like a fool

 

Thanks for all the support guys

 

Youve been immense as always

I was surprised when you said the code i provided didn't work at first.. simple error glad it worked for you

[TomFromKWD]

one final note I've just thought about after discussing it with the user is

at what time will it recognise the date change to the new day

Is it going to run off GMT, or the viewers time zone, or another time zone IE UCT,PCT etc?

[AyKay47]

it depends on what time zone your server is set to.. check for the value of the "date.timezone" directive in your php.ini file

[TomFromKWD]

Brilliant stuff

 

cheers for all the support 

[jcbones]

it depends on what time zone your server is set to.. check for the value of the "date.timezone" directive in your php.ini file

 

You need to check the timezone settings of MySQL, as it is a separate service than PHP.

[AyKay47]

yeah goo catch thanks..

 

OP here's some info on it.. http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html