Why does this MATCH AGAINST code not work?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

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

Why does this MATCH AGAINST code not work?

Post by simonmlewis »

Code: Select all

$query = "SELECT * FROM categories WHERE MATCH(categories) AGAINST (:search) OR MATCH(content) AGAINST (:search)";
$result = $pdo->prepare($query);
$result->execute(array(':search' => $search));
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
      {
      echo "$row->categories";
      }
The 'categories' and 'content' fields are both FULLTEXT.
If I run a query and replacing :search with 'freight', it should bring up at least one row, but it brings up nothing. No errors. Just nothing?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Why does this MATCH AGAINST code not work?

Post by Celauran »

Does it work outside of PHP? The columns are indexed?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why does this MATCH AGAINST code not work?

Post by simonmlewis »

No. If I test it in phpmyadmin, it just comes up with no rows.
I've done FULLTEXT on the two columns. Do I need to index both those columns too?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Why does this MATCH AGAINST code not work?

Post by Celauran »

https://dev.mysql.com/doc/refman/5.5/en ... earch.html

Columns need to have a FULLTEXT index on them, yes.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why does this MATCH AGAINST code not work?

Post by simonmlewis »

The two columns being matched, are FULLTEXT yes. Though oddly, if I click More, I can click Full Text again.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Why does this MATCH AGAINST code not work?

Post by Celauran »

simonmlewis wrote:if I click More, I can click Full Text again.
That's where you'd add the FULLTEXT index. I don't know where else you'd be seeing FULLTEXT, but that's not important.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why does this MATCH AGAINST code not work?

Post by simonmlewis »

Is there some means of checking that it is in fact set??
Some script I can pop into phpmyadmin that gives me the breakdown of the table fields settings? ??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Why does this MATCH AGAINST code not work?

Post by Celauran »

Code: Select all

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

Re: Why does this MATCH AGAINST code not work?

Post by simonmlewis »

How does this look?
Attachments
Seems to be ok ?
Seems to be ok ?
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: Why does this MATCH AGAINST code not work?

Post by simonmlewis »

Ok this does work, if the word "freight" appears in both 'categories' as well as 'content'.
In PHPmyadmin, it then highlights both those fields in the results.

I thought it was becaused I needed to bracketed out the query, so OR is ) OR (.... but nope, that doesn't work.
Take it out of either, and it doesn't show results.
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: Why does this MATCH AGAINST code not work?

Post by simonmlewis »

I'm now using this:

Code: Select all

$query = "SELECT * FROM pages WHERE MATCH(title) AGAINST (:search) OR MATCH(catname) AGAINST (:search)";
$result = $pdo->prepare($query);
$result->execute(array(':search' => $search));
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
If there is ONE entry that has "test" in the title, then it works. Doesn't work for catname, but does work for "test" in the title.
However, if I then update one of the other titles to have 'test' in it, no results come up.

Why might that be?
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: Why does this MATCH AGAINST code not work?

Post by simonmlewis »

This still doesn't work. It works only if there is just one result to be found. If there are more, it doesn't like it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply