PDO MySQL fulltext searches

[AdRock]

 

Is this the correct format to doing a MySQL full text search using PDO?  I want to search about 4 tables for the same keyword and not sure of the syntax

$count = "SELECT COUNT(*) FROM (SELECT t.boardid, t.topicid, 'messages' as mytable, topicname as title, message as content,

MATCH(topicname, message) AGAINST(':keywords*' IN BOOLEAN MODE)

as score FROM topics t INNER JOIN messages m ON t.topicid=m.topicid  

WHERE MATCH(topicname, message) 

AGAINST(':keywords*' IN BOOLEAN MODE)

UNION ALL

SELECT id,'', 'news' as mytable,title, content,

MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE)

as score FROM news WHERE MATCH(title, content) 

AGAINST(':keywords*' IN BOOLEAN MODE)

UNION ALL

SELECT  id,'', 'events' as mytable,title, content,

MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE)

as score FROM events WHERE MATCH(title, content) 

AGAINST(':keywords*' IN BOOLEAN MODE)

UNION ALL

SELECT  id,'', 'blogs' as mytable,title, content,

MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE)

as score FROM blogs WHERE MATCH(title, content) 

AGAINST(':keywords*' IN BOOLEAN MODE)) a GROUP BY title";


$keywords = strtolower($_SESSION['keywords']);


$sth = $dbh->prepare($count);

$sth->bindValue(':keywords', $keywords, PDO::PARAM_STR);

$sth->execute();

Edited October 28, 2013 by AdRock

[mac_gyver]

there two things i can offer -

 

1) your selected values need to have the same structure to use a UNION (the column heading from the first select is what is used for all the data.) if these tables are dissimilar in structure, it would be best to just query them individually.

 

2) the placeholder in the query does not use single-quotes around it and any wild-card characters in it must be in the actual value being bound.

 

behind the scenes, when the bound value is put into the query statement at runtime, because it is a string, single-quotes will be added to it. so, your existing usage would result in -  AGAINST(''some_string_here'*' ...). removing the single-quotes from inside the query and adding the wild-card to the actual data will result in - AGAINST('some_string_here*' ...)

 

beyond these, was the result from your query what you expected when you tried it?

Edited October 28, 2013 by mac_gyver

[AdRock]

Hi mac_guver

 

The query works when i'm not using placeholders as I've used it before when i first wrote it but I just want to updated it to be used with PDO.

 

I haven't tested it yet but just wanted to know if it's in the right format for PDO.

I read somewhere that you can only use a placeholder once but because i'm only binding the search values once, can the same placeholder be used in different parts of the query?

 

I will test it and see what happens

[mac_gyver]

afaik, you can use a named placeholder multiple times.

[AdRock]

Thanks mac_gyver

 

I edited the query and took out all the single quotes like you said, removed the * from the placeholder names and appended the * to the $keywords and it all works