Page 1 of 3

MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 4:09 am
by simonmlewis
Dear all

Very odd problem here. If I search for the word "monster" for example, it will find everything in the database where that word appears. But if I type in "cyclone", of which there is just one product, it won't find it.

That word is in the "title" field of the database for a product, but it won't find it. On the other hand, if I type in one word from another product, it WILL find it.

Can anyone shed any light on why this might be happening, or if they have come across this oddity before.

Code: Select all

 
if(isset($_POST['search']))
{
    $search = $_POST['search'];
    $_SESSION['search']=$search;
}
$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 4:16 am
by Edin
Just suggestion here , when you use something like LIKE '%blah%' you will get very pure performance if you have big table, i would recomand you to use FULLTEXT search. As result you get fast search and better matching. Here is sample:

Select * From Products Where Match(title) Against(' my product name ')

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 4:41 am
by simonmlewis
Hi

Never heard of this method before.
How would I implement that against this please?
SELECT * FROM products WHERE pause = 'off' AND title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());
ie. how do I do it against several fields?

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 4:48 am
by Eran
Edin is referring to this - http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
By the way, LIKE case use an index if the wildcard is not at the beginning (ie, LIKE 'search_term%')

If it is not finding cyclone, perhaps the case doesn't match? (lower / upper casing is different)
Make sure you use a case-insensitive character set if you want case-insensitive search to work.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 5:04 am
by simonmlewis
Ok.

I have wildcard at both ends.

I have copied the word rendered on the site, and pasted it into the search box. It's definitely not the casing. have tried upper, lower and titled casing.

I cannot figure out how to write the MATCH code to cover all bases in my query.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 8:36 am
by simonmlewis
I have tried, but this is the error I get
Can't find FULLTEXT index matching the column list

Code: Select all

$result = mysql_query ("Select * From products Where Match(title) Against('$search') OR Match(description) Against('$search') AND pause = 'off' or id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 11:23 am
by simonmlewis
This doesn't work either:

Code: Select all

$result = mysql_query ("SELECT * FROM products Where Match('title', 'description') Against('%$search%') AND pause = 'off' or id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 11:25 am
by Eran
in order to use fulltext functions you need add a fulltext index to the column. Only MyISAM table can have fulltext indexes by the way

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 11:57 am
by simonmlewis
Thanks - I am asking my site host about that now.
Will update this topic when I have news.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 1:35 pm
by simonmlewis
My local database is MyISAM, and I have been informed that the database has to have had FULLTEXT index created.

Anyone know what this is or means?

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 1:39 pm
by SpankMarvin
Unless I'm going mad, should the $search variable (and in fact all calls of any variable) you are using not be enclosed by double quotes and periods, since you are using double quotes to house the query? I.e. instead of

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());
you might try

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND title LIKE '%".$search."%' OR description LIKE '%".$search."%' OR id = '".$search."' LIMIT ".$offset.", ".$rowsPerPage) or die (mysql_error());

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 1:44 pm
by simonmlewis
hi spankmarvin - search results still produce nothing for "cyclone" when I know "cyclone" is in the database fields being searched on.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 1:47 pm
by SpankMarvin
Another suggestion -- try encapsulating all of your OR queries inside brackets, to separate them from the AND query.

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND (title LIKE '%".$search."%' OR description LIKE '%".$search."%' OR id = '".$search."') LIMIT ".$offset.", ".$rowsPerPage) or die (mysql_error());

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 1:54 pm
by simonmlewis
That didn't work.

The pattern appears to be - when it's the FIRST one of the field, and it's unique.
IE I have a F430 Ferrari as a product. If I search for
F430
It comes up with nothing.

Actually, I just copied the entire field name in, F430 Ferrari Challenger, and it also produced no result.

VERY ODD!

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 1:56 pm
by SpankMarvin
What are your limit values? Ensure you are starting the value at 0?