Search date in database PHP

Question

When I was a start up student in PHP I made my database to store dates the date and time together, now I have a big problem, I have already in the database over 3000 orders but when I want to make a search with dates am I in big trouble because the dates and time is together in one field, I tried to make the query like where date LIKE '%$date' but I'm getting no results, has anybody any idea what I can do now?

And also how can I change the whole database it should be all dates and time separately and it should not effect my database?

UPDATE:

The data in the database looks like, 10/16/2012 5:00pm

Appreciate any help.

Solution 1

In response to the sections of your question:

1. Finding the dates you need in the current schema.

Based on your edits, use:

 <?php

 $query = "SELECT * FROM table_name WHERE `date` LIKE '{$date}%'";

 ?> 

A query similar to what you posted should help you:

<?php

$query = "SELECT * FROM table_name WHERE `date` LIKE '%{$date}%'";

?>

Please note that your use of % in your question ( '%$date' ) will only match values that end with $date, while the pattern in my example ( '%{$date}%' ) will match values that have $date anywhere in them. Alternatively, you could use '{$date}%' to match date at the beginning of the value -- not sure which you want.

2. Updating your schema to split date and time into two columns.

The first step you should take here, is to add two columns ( date_only and time_only ) to your table. Next, update your code to process and store this information in addition to the 'all-in-one' date column your are currently using; you don't want to break your current codebase by switching over in one step. Once you can verify that date/time data is being written the way you want it to be, the third step is to read (and log) from the new date/time columns along with your production reads to date. Once you can verify that the reads are working as planned, switch over your dev environment to read from the new columns and test until you are confident that everything works.

Solution 2

Why %$date? You should do the opposite.

WHERE date LIKE "".$date."%"

Solution 3

You can do the following:

$date = "2012-03-08";
$sql = "SELECT * FROM table WHERE date => '$date 00:00:00' AND date =< '$date 23:59:59'

Edit: Seeing your edit, this does not work anymore. You will need to convert your date column to a proper MySQL datetime or TIMESTAMP type.

At the current database design you could use something like this:

date

$date = "10/16/2012";
$sql = "SELECT * FROM table WHERE date LIKE '$date%'

time

$time = "5:00pm";
$sql = "SELECT * FROM table WHERE date LIKE '%$time'

Solution 4

If it's a DATETIME field, you can use

WHERE DATE(datetime_field) = '01-01-2012';

or (better, as it can use indexes)

WHERE datetime_field >= '01-01-2012 00:00:00' AND datetime_field <= '01-01-2012 23:59:59';

View more solutions

Comments

[thegrede]

Thanks for this great idea, but Andrew Kozak has answered my second question too