AdRock Posted October 28, 2013 Share Posted October 28, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 28, 2013 Share Posted October 28, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
AdRock Posted October 31, 2013 Author Share Posted October 31, 2013 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 31, 2013 Share Posted October 31, 2013 afaik, you can use a named placeholder multiple times. Quote Link to comment Share on other sites More sharing options...
Solution AdRock Posted October 31, 2013 Author Solution Share Posted October 31, 2013 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.