mysql query capability question

phence

this is my current sql query:
mysql_query("SELECT * FROM table WHERE MATCH (item) AGAINST ('$searchValue') ORDER BY (exp) DESC LIMIT $from, $max_results ");

the query orders the results by the experation date and time. The date and time is 'exp' column in the database.

now, i only want the query to pick the row's that have valid dates. Rows with expired date and time should not be pulled out..
an expired date and time is any date and time past the current date & time..

Is there a way to accomplish this through the query ??

Thank-you, your help is appreciated

barand

Add

AND exp > curdate()

to your where clause.

hth

phence

that easy!! u da man!
thanks alot!

phence

i forgot to mention that the exp date in the database is a varchar, and it's in this format: m-d-Y hⓂs A T

is there still away to accomplish this through the query ????

thank-you!

barand

You would need to hold it in a format that can be sorted to do a a greater or less than query. m-d-y doesn't fit the bill. It would have to be y-m-d.

You could add another field of type DATETIME maybe. Or a year field.

phence

could it still be a varchar? or no?

cause i had to make it a Varchar because i didn't know how to format mysql date for the timezone and AM/PM.

is there a format for mysql date time for the timezome and AM/PM ??

thank-you

barand

From mySQL manual

The DATETIME type is used when you need values that contain both date and time information.
MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.
The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (\Supported"
means that although earlier values might work, there is no guarantee that they
will.)

It's easy to convert to am/pm when you display with the [man]date[/man] function ("h:i:s A") format. You could hold timezone in another field.

As for varchar, yes. If it's 'Y-m-d HⓂs A T' the it can be compared against a similarly format string variable in a query.

hth

trooper

Here are two ways of distinguishing date / time in mysql these are

1) Use the 24 hour clock
DATE_FORMAT(date_value, '%k-%i');

2) PUT THE AM / PM ON THE END
DATE_FORMAT('date_value','%r-%i%p');

1) SELECT DATE_FORMAT(now(),'%k:i');
2) SELECT DATE_FORMAT(now(),'%r:%i%p')

1) Gives you 20:41
2) Gives you 8:41PM

So in short i personally do not think its a good idea to try and store dates as a varchar - you lose out on so much that you can do with dates that way.

HTH