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

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

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') ";

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

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.

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

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.

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

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

drumdogg · 21 Mar 2013 at 14:25

No problem. You just need to remove the quotes from the table name in your update. I just tested it and it definately works.

Thanks, got it working now

Plus i was putting the code into the wrong sql query box. I chose the database i was using and clicked on the sql tab along the top but every time i ran the code it would never work.

Then i found a small icon to the left called 'Query Window' and it worked from there

Have been tearing my hair out for days lol

Thanks Again RedRum.