Don't show if out of date..

Hi,

What i am trying to do is when i am running an sql query i don't want to show an entry if the available date is already come and gone. In the table the available date is stored like e.g. 30-05-2007 so if this date is not below the current date +24hours then it does not show.

I hope this makes sense.

What i was trying to do is :

$currentdate = date("d-m-Y")+172800;
											
$selects = "SELECT * FROM vehicles WHERE available < '$currentdate' ORDER BY 'available'";

Code (markup):

But it does not work

Any ideas?
Cheers,
Adam

 

WHERE available < DATE_ADD(NOW(),INTERVAL 24 HOUR)+0

 

Thank you for your reply but does not seem to work and is still displaying all the entrys.

 

What type is "available" in your database?

Is it a date or timestamp format?

 

It is set as a varchar as the date is an entry from the user for when they want there product to be picked up.

 

You wont be able to compare dates like this, if you're using a varchar format - although, how are you storing your dates in the database?

If, for example, you stored a date as YY-MM-DD, then that may well work
(e.g. 07-07-10 would be "greater than" 07-06-15, using string comparisons)
but if it was DD-MM-YY, then that wouldnt work
(e.g. 10-07-07 would be "less than" 15-06-07)

Best thing - use the DATE column type in MySQL - lets you do more useful comparisons when it comes to querying orders etc.

 

Thank you for that it has set me on the right path. What i have done now is change the field to date format so that it displays like e.g. 2007-05-30 what would i be best doing in terms of only displaying the relevant items.

 

If you use the above MySQL query, posted by ecentricNick, that should work:

WHERE available < DATE_ADD(NOW(),INTERVAL 24 HOUR)+0

Code (markup):

 

That does not work it just shows an error on this link

$countrowssa = mysql_num_rows($selects);

 

Can you post your whole select statement again please?

 

This seems to be working but i don't think the logic behind the code is correct as what i think i need it to be is add 24 hours onto the available time and see if the current date is not greater than this.

 

try

WHERE DATE_ADD(available, INTERVAL 24 HOUR)+0 < NOW()

Code (markup):

(not tested)

 

Finally it works I used this in the end:

SELECT * FROM vehicles WHERE available > DATE_ADD(NOW(),INTERVAL -24 HOUR)+0

Code (markup):

Which seemed to do the trick

Thanks a lot for you help.

 

Good stuff!

I always find that messing around with SQL code, just to see what the results are is the best ways of learning. If you're running something like PHPMyAdmin (or any sort of MySQL admin software - even the MySQL control panel through windows or equivalent) there should be an area where you can type code in directly and see the results straight away.

This is probably the best way of experimenting, rather than typing the code into the page, reloading the page, waiting etc.

Good luck with the rest of the codings!

 

Thanks again and will have a play around and see what i come up with

 

TIMESTAMP is always a very nice date handling format. In fact it's a number of seconds, so you can do every mathematical comparison you want with it!