MySQL Query - SELECT MATCH AGAINST

I'm trying to run this query

$Result = mysql_query("SELECT title,category FROM articles WHERE MATCH(title,category) AGAINST ('text')");
while ($get_info = mysql_fetch_row($Result)){

and the while row is giving me this error

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource

I've tried everything to get this to work but I'm obviously doing something wrong. Any ideas?

 

Try "SELECT title,category FROM articles WHERE MATCH(title,category) AGAINST ('text')" in phpMyAdmin and see if it actually returns anything. Then you'll have a better idea what to do with the PHP side of it.

 

Thanks T0PS. Good idea.

#1191 - Can't find FULLTEXT index matching the column list

is the error I got in phpmyadmin. Does this give you any clues? It's the first time I've actually used this function.

 

Do as T0PS says and in addition are you sure you have a full text index on title and category and there is a t least one record that contains the word text.

For extra debug in PHP you can do something like

echo mysql_error();

PHP:

 

Yeah now I see have a look at http://dev.mysql.com/doc/mysql/en/fulltext-search.html

 

Not sure but I think it can only do such searches on indexed columns. So you'll have to make it index and fulltext it seems. You can do that when looking at the table structure in phpMyAdmin. Best to back up first under Export.

 

You've got it dct. I've put an index on them instead of a full text index. I'll try that now!

 

What about T0PS as it was a bit of a joint effort, pleased you're sorted

 

Yeah, what about me?!

$result = mysql_query("UPDATE reputation SET quantity = (quantity + Weirfire's Rep) WHERE member_id = 114") or die "Or die!";

 

You must spread some Reputation around before giving it to T0PS3O again.


I did try

I'll just lavish praise on you T0PS instead.

Here goes.... If anyone ever needed a quality response in a thread like this it would come from T0PS. He's a busy man but he'll always find time for his DP family. His words are like gold spilling from his mouth. Men like these get statues built for them. I could go on but I don't want to bore everyone.

 

That'll do just fine sir Gold spilling from my mouth... Wow! LOL

 

lol thanks for the rep.

Now here's my next problem

SELECT title
FROM articles
WHERE  MATCH ( title )
AGAINST (  '%a%' )

Code (sql):

Is that what I want to do to find all rows where an 'a' exists in the title? or do the percentage signs just break up a variable so you can check if any of the words in a variable appear in a title

e.g.

$keywords = "a b c";

SELECT title
FROM articles
WHERE  MATCH ( title )
AGAINST (  '%$keywords%' )

Code (sql):

would return all those rows where an 'a', a 'b' or a 'c' occurred in the title?

I'll have a read over the site you posted as well dct but I thought I'd just post my query just to help anyone who might stumble across this thread in the future.

 

I'd do that with WHERE title LIKE '%a%' off the top of my head. To be honest, never even seen this MATCH AGAINST business.

 

I hadn't either until I found someone using it with their search system. I saw the LIKE method as well. I'm definitely going to have to read that site dct posted now to see what exactly it does differently. It must be something to do with the speed of queries if it's using an index.

The LIKE way works btw Cheers.

You will be dually repped whenever I can rep you again T0PS lol

 

Like is good but not always scalable, if you do like '%something' it will not be able to use any index and will do a full table scan every time which on a large table with big fields will kill performance.

Like you 2 I'm not that familiar with MATCH but generally when you do full text indexing it basically indexes the textual content internally so that searching for words and/or phrases is a lot faster and more powerful.

In summary if you have any concerns about performance due to many records or large fields be wary of like '%....' (like 'some%' is fine as it will still be able to use an index) and you should really look into and consider full text indexing.

 

I have to agree. I have been following this and I'm of the impression this app wil bring a server to it's knees that way it's laid out and coded so far.

 

Thanks for the comments on this.

I'll see what I can find out and post the solution MATCH query if I find 1.