Jump to content

PDO MySQL fulltext searches


AdRock
Go to solution Solved by AdRock,

Recommended Posts

 

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 by AdRock
Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.