Is there a way to match multiple columns with multiple tables using FULLTEXT search?

[imgrooot]

I currently have a fulltext search that works.  It matches against a item title column in mysql database. Now I would like to include another column to match against.  How can that be done properly?

 

Here's my code. Where it says MATCH, I would like to include the type_1 column as well; like this(type_1.type_1_name).

$get_records = $db->prepare("SELECT items.*, type_1.* FROM items
LEFT JOIN type_1 ON items.type_1 = type_1.type_1_id
WHERE MATCH(items.item_title) AGAINST('$search_query' IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT {$limit} OFFSET ".$offset);
$get_records->execute();
$result_records = $get_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0){
	foreach($result_records as $row) {

		// get results

	}
}

[PravinS]

You can use OR, your WHERE condition will be like this

WHERE MATCH(items.item_title) AGAINST('$search_query' IN BOOLEAN MODE) OR MATCH(type_1.type_1_name) AGAINST('$search_query' IN BOOLEAN MODE)

hope this will work for you.

[Barand]

or you could look up the syntax in the manual

 

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

 

http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html#function_match

[imgrooot]

I meant to say from multiple TABLES not columns.  I know you can search from more than one column in the same table.  But I have having an issue searching from 2 left joined tables at the same time.

 

Also I want to do the same thing as above with the COUNT query.

$count_records = $db->prepare("SELECT COUNT(*) FROM items WHERE MATCH(item_title) AGAINST('$search_query' IN BOOLEAN MODE)");
$count_records->execute();
$total = $count_records->fetchColumn();

[maxxd]

If you've got the tables joined, wouldn't you just name the columns in the match?

SELECT   items.*
        ,type_1.*
FROM items
LEFT JOIN type_1
    ON items.type_1 = type_1.type_1_id
WHERE MATCH(items.item_title, type_1.type_1_name ) AGAINST('$search_query' IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT {$limit} OFFSET {$offset}

I'm assuming you're validating and sanitizing the variables you're injecting into the query, yes?

[imgrooot]

If you've got the tables joined, wouldn't you just name the columns in the match?

SELECT   items.*
        ,type_1.*
FROM items
LEFT JOIN type_1
    ON items.type_1 = type_1.type_1_id
WHERE MATCH(items.item_title, type_1.type_1_name ) AGAINST('$search_query' IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT {$limit} OFFSET {$offset}

I'm assuming you're validating and sanitizing the variables you're injecting into the query, yes?

 

I tried that but it gives me an error like this.  I ready somewhere that Fulltext search might not allow multiple tables to be searched.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1210 Incorrect arguments to MATCH' in...

In terms of validating and sanitizing the variable, I have it like this.  I don't know if that's sufficient enough.

$search_query	= trim(preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $_GET['sq']));

[Jacques1]

The whole purpose of prepared statements is that you pass your input to predefined parameters and prevent SQL injections altogether:

// a static query template with three parameters: search_query, limit, offset
$items_stmt = $db->prepare('SELECT ... AGAINST(:search_query IN BOOLEAN MODE) ORDER BY ... LIMIT :limit OFFSET :offset');

// bind values to the parameters and execute the statement
$items_stmt->execute([
   'search_query' => $_GET['sq'],
    'limit' => $limit,
    'offset' => $offset,
]);

So the query is a constant string. Do not assemble dynamic queries from user input, not even when you think you've “sanitized” it.

 

 

 

I ready somewhere that Fulltext search might not allow multiple tables to be searched.

 

Correct. That's why you need to split the condition into one MATCH ... AGAINST per table and connect them again with OR.

Edited September 2, 2016 by Jacques1

[imgrooot]

The whole purpose of prepared statements is that you pass your input to predefined parameters and prevent SQL injections altogether:

// a static query template with three parameters: search_query, limit, offset
$items_stmt = $db->prepare('SELECT ... AGAINST(:search_query IN BOOLEAN MODE) ORDER BY ... LIMIT :limit OFFSET :offset');

// bind values to the parameters and execute the statement
$items_stmt->execute([
   'search_query' => $_GET['sq'],
    'limit' => $limit,
    'offset' => $offset,
]);

So the query is a constant string. Do not assemble dynamic queries from user input, not even when you think you've “sanitized” it.

 

 

 

 

Correct. That's why you need to split the condition into one MATCH ... AGAINST per table and connect them again with OR.

 

Good to know. 

 

In terms of your example with prepared parameters, it only works if I set the search_query parameter. If I add offset and limit, it will give an error.

 

Now my question is, how is your prepared statement different from mine below? Are both correct methods?

$items_stmt = $db->prepare("SELECT items.*, type_1.* FROM items
LEFT JOIN type_1 ON items.type_1 = type_1.type_1_id
WHERE MATCH(items.item_title) AGAINST(':search_query' IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT {$limit} OFFSET ".$offset);
$items_stmt->bindParam(':search_query', $search_query);
$items_stmt->execute();
$result_items = $items_stmt->fetchAll(PDO::FETCH_ASSOC);
if(count($result_items) > 0){

}

Also using the above query, can you give me an example of the MATCH AGAINST per table you're talking about? Because I've tried many different methods and so far no luck.

Edited September 2, 2016 by imgrooot

[Jacques1]

In terms of your example with prepared parameters, it only works if I set the search_query parameter. If I add offset and limit, it will give an error.

 

That's because you're using emulated prepared statements (which is the default). Turn off PDO::ATTR_EMULATE_PREPARES.

 

 

 

Also using the above query, can you give me an example of the MATCH AGAINST per table you're talking about? Because I've tried many different methods and so far no luck.

 

Your query doesn't involve multiple tables, so there's nothing to be shown. Post the failing query.

[imgrooot]

That's because you're using emulated prepared statements (which is the default). Turn off PDO::ATTR_EMULATE_PREPARES.

 

 

 

 

Your query doesn't involve multiple tables, so there's nothing to be shown. Post the failing query.

 

 

In my database connection, I had this.

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I replaced that with with this.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Is that correct?  After doing that, the query does work with offset and limit parameters.

 

 

"Items" and "type_1" are 2 different tables. Would that not be considered multiple tables?  Below is my full code for the search page.  I have 4 seperate tables(items, type_1, brands, images).  They are all connected.  Currently the search works by matching the user's search query against the "item_title" in the items table.  But what I would like to do is for the search_query to ALSO search the brand_name in the brands table and retrieve results based on on the matching of both brand name item title.

 

I have tried maxxd's method above and it doesn't work. Like this. 

WHERE MATCH(items.item_title, brands.brand_name ) AGAINST(:search_query IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT :limit OFFSET :offset

Original code.

$search_query	= trim(preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $_GET['sq']));

$count_records = $db->prepare("SELECT COUNT(*) FROM items WHERE MATCH(item_title) AGAINST(:search_query IN BOOLEAN MODE)");
$count_records->bindParam(':search_query', $search_query);
$count_records->execute();
$total = $count_records->fetchColumn();

// How many items to list per page
$limit = 8;

// How many pages will there be
$pages = ceil($total / $limit);

// What page are we currently on?
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array (
	'options' => array(
		'default'   => 1,
		'min_range' => 1,
	),
)));

// Calculate the offset for the query
$offset = ($page - 1)  * $limit;

// Some information to display to the user
$start = $offset + 1;
$end = min(($offset + $limit), $total);

if($page > 0) {
	$offset = ($page - 1)  * $limit;
} else {
	$offset = 0;
}

$get_records = $db->prepare("SELECT items.*, type_1.*, brands.*, images.* FROM items
LEFT JOIN type_1 ON items.type_1 = type_1.type_1_id
LEFT JOIN brands ON items.brand_id = brands.brand_id
LEFT JOIN images ON items.item_id = images.item_id
WHERE MATCH(items.item_title) AGAINST(:search_query IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT :limit OFFSET :offset");
$get_records->bindParam(':search_query', $search_query);
$get_records->bindParam(':limit', $limit);
$get_records->bindParam(':offset', $offset);
$get_records->execute();
$result_records = $get_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0){
   foreach($result_records as $row) {
      // loop for items
    }

} else {

   echo '<div id="message-flash">No results found.</div>';

}

Edited September 3, 2016 by imgrooot

[Jacques1]

In my database connection, I had this.

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I replaced that with with this.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Is that correct?  After doing that, the query does work with offset and limit parameters.

 

You shouldn't replace the error setting. You should have both: Turn off emulation, turn on exceptions. Also consider setting the default fetch mode (e. g. to PDO::FETCH_ASSOC).

 

 

 

I have tried maxxd's method above and it doesn't work. Like this. 

WHERE MATCH(items.item_title, brands.brand_name ) AGAINST(:search_query IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT :limit OFFSET :offset

 

Now you need two separate MATCH conditions (as explained above):

WHERE
    MATCH (items.item_title) AGAINST (:title_query IN BOOLEAN MODE)
    OR MATCH (brands.brand_name) AGAINST (:brand_query IN BOOLEAN MODE)

You need two parameters :title_query and :brand_query, because the same parameter may not appear more than once. But of course you can bind the exact same string $search_query to both parameters.

[imgrooot]

You shouldn't replace the error setting. You should have both: Turn off emulation, turn on exceptions. Also consider setting the default fetch mode (e. g. to PDO::FETCH_ASSOC).

 

 

 

 

Now you need two separate MATCH conditions (as explained above):

WHERE
    MATCH (items.item_title) AGAINST (:title_query IN BOOLEAN MODE)
    OR MATCH (brands.brand_name) AGAINST (:brand_query IN BOOLEAN MODE)

You need two parameters :title_query and :brand_query, because the same parameter may not appear more than once. But of course you can bind the exact same string $search_query to both parameters.

 

So based on your answer, here's my new query.

WHERE MATCH(items.item_title) AGAINST(:title_query IN BOOLEAN MODE) OR MATCH(brands.brand_name) AGAINST(:brand_query_ IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT :limit OFFSET :offset");
$get_records->bindParam(':title_query', $search_query);
$get_records->bindParam(':brand_query', $search_query);

The above gives me the error below. The line 117 being the WHERE query.

 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list' in C:\xampp\htdocs\..... on line 117

[Jacques1]

One of the two columns doesn't have a FULLTEXT index. Go check that with phpmyadmin (or whatever tool you're using).

[imgrooot]

One of the two columns doesn't have a FULLTEXT index. Go check that with phpmyadmin (or whatever tool you're using).

 

You're right.  My items table was set to MyISAM but brands table wasn't. I changed it to MyISAM as well and now it WORKS! 

 

Thank you so much for helping me out.