Searching for date range in PHP

drumdogg · 6 Mar 2013 at 08:45

Hi Guys, i have a few drop down menus for my search function but cant seem to get my date range to work. For date i have two field with a jQuery date picker to choose the date.

Here is the search code:

Code:

$QBuild = "";
$QBuild = "SELECT * FROM ($table) WHERE upper($field)";

	if($Date1 && $Date2 != NULL){
		$QBuild = $QBuild."AND (Date BETWEEN '$Date1' AND '$Date2') ";
	}

	if($Initials != NULL){
		$QBuild = $QBuild."AND (Initials LIKE '$Initials')" ;
	}

		if($PassFail != NULL){
		$QBuild = $QBuild."AND (PassFail LIKE '$PassFail')" ;
	}

Here is the code for the date fields used:

Code:

Search by Date: <input name="Date1" type="text" id="datepicker" /> and 
 <input name="Date2" type="text" id="datepicker1" />
 <Select NAME="field">
 <option value="Date">Date</option>
 </Select>

It will work if i want to search for one date, what i have to do is put the same date into both fields and it works.

Can someone help please

drumdogg · 7 Mar 2013 at 11:43

Anyone please

AS_Platinum · 7 Mar 2013 at 12:46

Code:

$QBuild = "";
$QBuild .= "SELECT * FROM ($table) WHERE upper($field)";

	if($Date1 && $Date2 != NULL){
		$QBuild .= "AND (Date BETWEEN '$Date1' AND '$Date2') ";
	}

	if($Initials != NULL){
		$QBuild .= "AND (Initials LIKE '$Initials')" ;
	}

		if($PassFail != NULL){
		$QBuild .= "AND (PassFail LIKE '$PassFail')" ;
	}

Try something like that.

drumdogg · 7 Mar 2013 at 14:07

Thanks for the reply. No that doesn't work either. When selecting a date range it just prints everything

fez · 7 Mar 2013 at 17:21

Few things.

What are the values of $Date1 and $Date2. Don't take them from the jQuery picker, echo them out and see.

What is this statement trying to do as well?

if($Date1 && $Date2 != NULL)

When working with sql, if its not working as you expected the first port of call is usually to print the sql you are executing to see if the statement is actually correct. If it looks correct and is still not working, chuck it into your sql browser to view the results.

drumdogg · 8 Mar 2013 at 09:19

Thanks for the advice fez. I echoed the two date values and they seem correct and are in the same format as the dates are stored in my database.

Basically what i want it to do is check that there are values in the two fields and if so then get all dates within that range.

I think this part is the issue but not sure what it should be:

$QBuild .= "AND (Date BETWEEN '$Date1' AND '$Date2') ";

fez · 8 Mar 2013 at 12:36

That should be alright, but have you checked that the code is actually being added to the sql query string.

What is the full sql string that is actually be executed. Not what you think is being executed but the actual string that is. What data type is the Date field on your db?

drumdogg · 8 Mar 2013 at 12:57

Ok i have echoed the whole sql string.

I did a search using all of the available search filters i have and it worked fine. Here is the echoed statement:

SELECT * FROM (product1) WHERE(Date)AND (Date BETWEEN '01/02/2013' AND '28/02/2013') AND (Initials LIKE 'steven')AND (PassFail LIKE 'Pass')

Then i did another search and just used two of the filters and it didnt work:

SELECT * FROM (product1) WHERE(Date)AND (Date BETWEEN '01/02/2013' AND '28/02/2013')

I have set the Date field to VarChar which i probably shouldnt have

AS_Platinum · 8 Mar 2013 at 13:22

Just a quickie! Is the var $passfail a static yes/no?

drumdogg · 8 Mar 2013 at 13:36

Yes it is a static yes/no. In the statement to insert the info into the database i have typed it in as Yes/No and not from a variable like the others.

drumdogg · 8 Mar 2013 at 13:52

Sorry didnt mean Yes/No. Dont know what i was talking about there lol

In the statement to add info to the database i have typed in Pass/Fail not Yes/No.

fez · 8 Mar 2013 at 14:03

You need to make the date fields use the "Date" format. Otherwise you are performing a string comparison.

The other thing to note is that you tend to use the "LIKE" operator when doing a bit of fuzzy logic. Things like:

SELECT * from table where field like '%sam%'

which would find any row where the value of field contains "sam" with any characters before or after. You could use it to find all records that contain a phrase etc.

You shouldn't be testing for fields either in mysql. Select * from table WHERE field is not really valid, you should be checking the value of the field using a comparison like "field is NULL" etc.

drumdogg · 14 Mar 2013 at 14:24

You need to make the date fields use the "Date" format. Otherwise you are performing a string comparison.

Hi fez, sorry for the late responce.

I manage my database through phpMyAdmin. Tried to change to format on existing table but throws up the following error - Incorrect data value '06/12/2012'. Do i need to create my tables from scratch again and lose my data??

Thanks

fez · 14 Mar 2013 at 16:53

the default date format will be Y-m-d which would give you dates of 2012-12-06. If you clear out all of the current values for that column or do a string replace on them to reorder the bits of the date you should be fine.

drumdogg · 18 Mar 2013 at 08:02

Could someone give me an example of how to do a string replace to change the date format please.

AS_Platinum · 18 Mar 2013 at 21:22

date("d-m-Y", strtotime($timestamp));

http://php.net/manual/en/function.date.php

Redrum · 19 Mar 2013 at 00:09

I suggest rather than convert, you add a new column that is a date format, then migrate the data to that. You can write a PHP script to set the new column to the correct date on each row as AS_Platinum suggested, or you could look at the MySQL function str_to_date, which would allow you to update all rows with a single update statement from your PHPMyAdmin. Once you're sure the data has been moved over correctly, you can remove the old column.

Also, a few notes about your PHP:

Code:

if($Date1 && $Date2 != NULL){

should be

Code:

if($Date1 != NULL && $Date2 != NULL){

To add something to a string, you can just use .=, so instead of

Code:

$QBuild = $QBuild."AND (Date BETWEEN '$Date1' AND '$Date2') ";

You can do:

Code:

$QBuild .= "AND (Date BETWEEN '$Date1' AND '$Date2') ";

And lastly and most importantly, you don't appear to be escaping your strings even though they have come from the user, which makes your database wide open for attack. If it's just going to be you with access to this, then that's not so bad. Otherwise, if you're using the old MySql libraries, take a look at the mysql_real_escape_string function, or if you're using PDO, the quote() function.

drumdogg · 19 Mar 2013 at 08:51

Guys thanks for the feedback.

I suggest rather than convert, you add a new column that is a date format, then migrate the data to that. You can write a PHP script to set the new column to the correct date on each row as AS_Platinum suggested, or you could look at the MySQL function str_to_date, which would allow you to update all rows with a single update statement from your PHPMyAdmin. Once you're sure the data has been moved over correctly, you can remove the old column.

Ok i tried to migrate one column into another but now the date shows up as 0000-00-00.

Also i really dont know much about these mysql commands. In the above str_to_date link above it states the following code should work:

Code:

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

Sorry for souding like a noob about this

Cheers

Redrum · 20 Mar 2013 at 00:32

The example given in the manual is for someone who has the date stored as text in the format '01,5,2013' (dd,mm,yyyy). This is just an example and you have to adapt it to however you have the text stored based on the various specifiers.

Earlier in the thread you wrote some SQL that had the date of 28/02/2013, so I assume all your dates are currently stored in the varchar as dd/mm/yyyy. This just so happens to be the same as the example in the manual except you have slashes where they have commas, so you just need to change the commas to slashes.

Then, if your table was called drumdogg, the varchar column was called varchar_date_col and your new date column was called actual_date_col, you could run the following in the phpMyAdmin query window:

Code:

update drumdogg set actual_date_col = str_to_date(varchar_date_col,"%d/%m/%Y");

Once you've checked that all the dates have been converted properly, you can then delete your varchar field and rename the date field to the varchar's name if you wish.

Having dates stored in a date field is absolutely the right thing to do but bear in mind that once you switch, any queries you had that were working with the varchar column will need to be changed so that the date is in the right format for MySql.

There are various ways you can do that. There might be some options within your jQuery date picker. Or, you can do it in PHP with the date format functions, or you can do it in all of your queries using the build in functions in mysql (like str_to_date).

drumdogg · 20 Mar 2013 at 08:57

Thanks for the reply RedRum,

Then, if your table was called drumdogg, the varchar column was called varchar_date_col and your new date column was called actual_date_col, you could run the following in the phpMyAdmin query window:

I did as you said but when i go to run the code in phpmyadmin it says there is a value missing in the form. Here is the code I used:

Code:

update '4mmchips' set DateNew = str_to_date(Date,"%d/%m/%Y");

Am i doing something stupid!! lol