MySQL query - finds somethings, but all everything!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

MySQL query - finds somethings, but all everything!

Post 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());
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Edin
Forum Newbie
Posts: 5
Joined: Thu Jul 23, 2009 6:59 am

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

Post 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 ')
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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());
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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());
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

Thanks - I am asking my site host about that now.
Will update this topic when I have news.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
SpankMarvin
Forum Newbie
Posts: 17
Joined: Mon Jul 27, 2009 1:08 am

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

Post 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());
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

hi spankmarvin - search results still produce nothing for "cyclone" when I know "cyclone" is in the database fields being searched on.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
SpankMarvin
Forum Newbie
Posts: 17
Joined: Mon Jul 27, 2009 1:08 am

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

Post 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());
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
SpankMarvin
Forum Newbie
Posts: 17
Joined: Mon Jul 27, 2009 1:08 am

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

Post by SpankMarvin »

What are your limit values? Ensure you are starting the value at 0?
Post Reply