[FIXED] Search date in database PHP

Issue

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

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.

Answered By - Andrew Kozak