Page 1 of 1

Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 10:19 am
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?

Re: Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 10:33 am
by Celauran
Does it work outside of PHP? The columns are indexed?

Re: Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 10:40 am
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?

Re: Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 10:47 am
by Celauran
https://dev.mysql.com/doc/refman/5.5/en ... earch.html

Columns need to have a FULLTEXT index on them, yes.

Re: Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 11:05 am
by simonmlewis
The two columns being matched, are FULLTEXT yes. Though oddly, if I click More, I can click Full Text again.

Re: Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 11:14 am
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.

Re: Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 11:16 am
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? ??

Re: Why does this MATCH AGAINST code not work?

Posted: Fri Mar 11, 2016 1:07 pm
by Celauran

Code: Select all

SHOW INDEXES FROM table_name_here

Re: Why does this MATCH AGAINST code not work?

Posted: Mon Mar 14, 2016 3:51 am
by simonmlewis
How does this look?

Re: Why does this MATCH AGAINST code not work?

Posted: Mon Mar 14, 2016 4:42 am
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.

Re: Why does this MATCH AGAINST code not work?

Posted: Thu Mar 17, 2016 8:26 am
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?

Re: Why does this MATCH AGAINST code not work?

Posted: Mon Mar 21, 2016 12:07 pm
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.