about search on mysql

hello,

I am trying to match a field on mysql. this is the query i use.

$query = "SELECT * FROM orderr  WHERE orderr_collect_time like \"%$mynewdate%\" ORDER BY orderr_id DESC LIMIT $start, $limit";

PHP:

$mynewdate format is: 23-Feb-2010
data format in mysql is: 23-Feb-2010 00:57:11

I do not need to match the time. I only need to match the date. Is the query i am using is ok. for this purpose? Will i allways get the right data?

Thanks

 

What data type is the column "orderr_collect_time"? If it is DATETIME, you can't use the format 23-Feb-2010. Try 2010-02-23 instead.

 

it is not datetime, it is varchar(50).

 

I dont think your query will result exact searched date, like if u search for 2010, it will result all results having year 2010. Doesnt matter whats the date..

 

I will never do a search for the year. my search will be under 1 format which is like: 23-Feb-2010. but format of the column in data base is like 23-Feb-2010 00:57:11

 

Yes, as long you do not search to the hours/minutes/seconds, you'll get the right data although it's varchar column

 

$query = "SELECT * FROM orderr WHERE orderr_collect_time like '$mynewdate%' ORDER BY orderr_id DESC LIMIT $start, $limit";

 

what is the difference between
\"%$mynewdate%\"
and
'$mynewdate%'

 

I am not sure but u cant use Escape \ character in query.
U can use my query as it is.
If it dont work there is some pob in your code...

 

% is a kind of asterisk.

For example you have these data

1. Big John Guy
2. John Doe
3. Small John
4. Small Smart John

Query

SELECT * FROM users WHERE name LIKE '%john%';

Code (markup):

Will return whole data (4 data).

Query

SELECT * FROM users WHERE name LIKE 'john%';

Code (markup):

Will return this data

1. John Doe

While this query

SELECT * FROM users WHERE name LIKE '%john';

Code (markup):

Will return these data

1. Small John
2. Small Smart John

But in your case, i guess your query is fine

 

thanks for the info.

in this case i should use:

SELECT * FROM orderr  WHERE orderr_collect_time like '$mynewdate%'

Code (markup):

to match 23-Feb-2010
in 23-Feb-2010 00:57:11

 

Although your query will match the correct data, but this is not the best choice to match dates. At the minimum use DATETIME (yyyy-mm-dd time) to store dates. VARCHAR is not the best choice for any data type. It should only be used if you are uncertain about the data length. CHAR is a better choice.
Thanks