Seaching by Date MySQL

fasterisbest

Hello again,

I am having some trouble searching for all records that exist with a certian date in one of my tables.

I have a site where people can book a skydive and when they submit I "INSERT" a date into my table with the date() function. This code is as follows:

$date = date('Y:m:d');

It inserts into table_name in exactly this format: dd/mm/yyyy.

I search for all records (a.k.a "rows") using this code:

$query =("	SELECT * 
					FROM table_name 
					WHERE date = '$bookDate'") or die(mysql_error());



//query DB
		$result = mysql_query($query);

//get number or rows	
		$num_rows = mysql_num_rows($result);
		echo '<p>Number of bookings found:'.$num_rows.'</p>';

I know there is an entry in my table that is exactly 18/07/2007 but when i search for it $num_rows echos out:

Number of bookings found: 0

Can anyone give me some advice here?

Thanks in advance for your time.

jazz_snob

are you sure $bookDate is the string you want?
is "date actually a mysql date column type?
recommend using date format yyyy-mm-dd

from the documentation for mysql:

A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.

fasterisbest

Hello,

$bookDate is the short variable name i have set the value of the search box in the form.
date is actually a MySQL column name.
when i find the date using the date function I ask for it like this:

$date = date('YⓂd');

When I INSERT MySQL formats it like this: dd/mm/yyyy.

Thanks

fasterisbest

Here is the code with which I am working:

//create short variable names
	$bookDate = $HTTP_POST_VARS['bookDate'];
	$firstName = $HTTP_POST_VARS['firstName'];
	$lastName = $HTTP_POST_VARS['lastName'];

$bookDate = addslashes($bookDate);



$query =("	SELECT * 
					FROM tblBook 
					WHERE '$bookDate' 
					LIKE date
					OR '$firstName'
					LIKE firstName
					OR '$lastName'
					LIKE lastName") or die(mysql_error());



//query DB
		$result = mysql_query($query);

//get number or rows	
		$num_rows = mysql_num_rows($result);
			echo '<p>Number of bookings found:'.$num_rows.'</p>';

Does anyone see any problems with it?

I can search for names but not for any dates.

THanks

maxpup979

Print out the query you are using, and see if you can find the problem. if it isnt obvious, then post it here. Without knowing how the app is put together, I am uncertain why you are using addslashes on bookDate.

fasterisbest

I'm not sure how to print out the query.

Right now my code allows me to search any names but will not return a date from the 'date' column.

maxpup979

To print out the query:

print $query;

fasterisbest

Hi,

Here is the output from the print $query; statement

SELECT * FROM tblBook WHERE '' LIKE date OR 'sean' LIKE firstName OR '' LIKE lastName OR 'fasterisbest@hotmail.com' LIKE email

Can you see any problems now?

Thanks eveyone

NogDog

It would appear that neither $bookDate nor $lastName has a value (thus the empty pairs of quotes in the $query output).

fasterisbest

Hello,

$bookDate and $lastName do not have values. i am alble to seach which ever criteria i choose. 1 field or all. the more search criteria i enter the more specific the results.

however, if i just choose to search for a date i never have any results returned.

Is there a specific way to seach for date in the database?

i know there is an entry in the DB with the values i am searching. why will mysql_num_rows not return it?

thanks for your help.

NogDog

You probably should use "=" instead of "LIKE" when comparing dates. (LIKE is for comparing strings, and normally is used in conjunction with "%" wildcard characters in the string literal being compared.)