mysqli_query SELECT FROM WHERE [ partial match ] ?

Question

Hi guys, I have a database that a time filed that is stored in this format "2010.06.04. | 18:53 01". What I need is to select rows that have a specific date, for example "2010.06.04."

Right now I am doing it my first selecting all rows and then looping through them and adding ones with the specified date to a new array.

Maybe there is an easier way to do it and I somehow can select it using mysqli_query?

Thanks!

Answer 1

What you will need is a like query (http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html)

SELECT * FROM `table` WHERE `date_field` LIKE '2010.06.04%'

Note The % is a wildcard that states any text can go here.

I would also consider switching that field to be DATETIME field, this would allow you to do alot more with the data. - Note be careful when doing this, I would recommend creating another field first then calculating the proper value. You can then drop the unwanted field.

Answer 2

The LIKE keyword....

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

SELECT * FROM .... WHERE date LIKE '2010.06.04%'

Answer 3

Three cheers for not using the DATE and DATETIME field types.

WHERE time_field LIKE "2010.06.04 %"