Don't show if out of date..

[adamjblakey]

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'";

 

But it does not work

 

Any ideas?

Cheers,

Adam

[metrostars]

$currentdate = date("Y-m-d", mktime(0, 0, 0, date('m'), date("d") + 1, date("Y")));

 

replace that and itshould work.

[adamjblakey]

Does not seem to work that.. It is still showing dates that are less than the current date.

[metrostars]

Sorry, I didn't realise you store dates different to the normal way.

 

$currentdate = date("d-m-Y", mktime(0, 0, 0, date('m'), date("d") + 1, date("Y")));

[adamjblakey]

Thank you for your further reply but that still does not seem to work and is displaying all entries still even if they are out of date.

[metrostars]

It might because you look like you are storing the date in a varchar field, so MySQL will be treating the date field as a string, rather than a date. You'll have to change that. It should be in the format of Y-m-d, unless you changed it in the mysql ini, which I doubt.

[per1os]

It might because you look like you are storing the date in a varchar field, so MySQL will be treating the date field as a string, rather than a date. You'll have to change that. It should be in the format of Y-m-d, unless you changed it in the mysql ini, which I doubt.

 

He needs to store it as an INT(11) for a UNIX Timestamp or the date field to use MySQL for date calculations. Varchar is taken literally so running operators on it does no good.

[adamjblakey]

Yes the date is as a varchar. What shall i change it to so it will work?

[SharkBait]

datetime is what i usually use

[per1os]

datetime is what i usually use

 

I like the int(11) for the unix timestamp. Just personal preference.

[$username]

The INT 11 is a good way. I like this way as well.

 

Brett

[mosi]

Just to add, I'm another one who prefers int(11) with unix time stamps, I find it easier to sort and use for some other scripts I use.

[adamjblakey]

Right what i have done is change the field to a date inplace of varchar now all the dates in the table are setup like e.g. 2007-05-30 then i tried changing the statement to

 

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

 

But this does not work.. What am i doing wrong here?

 

Cheers,

Adam

[per1os]

Right what i have done is change the field to a date inplace of varchar now all the dates in the table are setup like e.g. 2007-05-30 then i tried changing the statement to

 

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

 

But this does not work.. What am i doing wrong here?

 

Cheers,

Adam

 

Remove the single quotes from available and $currentdate in the SQL.

[metrostars]

Not sure, looks OK, but the order by bit should read

 

ORDER BY `available` ASC

 

You can change the ASC if you want it going up instead of down.

[adamjblakey]

Thank you again but i still cannot get this to work. When this is what i have now but it shows no results even though i have an entry which is 2007-08-09 and even this does not show.

 

The current code is:

 

$currentdate = date("Y-m-d", mktime(0, 0, 0, date('m'), date("d") + 1, date("Y")));
$selects = "SELECT * FROM vehicles WHERE available < $currentdate";

 

Any ideas?

[per1os]

$currentdate = date("Y-m-d", (time()+3600*24));
$selects = "SELECT * FROM vehicles WHERE available < $currentdate";

 

Try that. the 3600 = 1 hour in seconds, times it by 24 to simulate tommorow. Time returns a unix timestamp.

[adamjblakey]

Right i need to scrap this as its not working and then go back to the drawing board.

 

At the moment the date is coming from the user as e.g. 2007-07-12 from a JavaScript popup calender then passed to the database in this format.

 

If the date is more than 1 day older than the current date then it does not show.

 

This is the code i have at present but does not work and simply does not show any items.

 

$currentdate = date("Y-m-d", (time()+3600*24));
$selects = "SELECT * FROM vehicles WHERE available < $currentdate";

 

Does anyone have any ideas on why this is not working or any ideas what i can do differently to get this to work?

 

Cheers,

Adam