phpmyadmin Question posted in
The official documentation can be found here.

Searching database using `LIKE` and `MATCH AGAINST` in MySQL – Phpmyadmin

I have a table in my database called pro_search where I have translated the meta information for different products in different languages.

My table structure is like so:id pro_id en de es fr it nl pl pt
where pro_id is the id of the product and the two letter code in the other columns represent the language of the translation of that products meta data.

I ran the following code to create a FULLTEXT indexes on my database:

ALTER TABLE pro_search ADD FULLTEXT INDEX `FullText` (`en` ASC, `de` ASC, `es` ASC, `fr` ASC, `it` ASC, `nl` ASC, `pl` ASC, `pt` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `enFullText` (`en` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `deFullText` (`de` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `esFullText` (`es` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `frFullText` (`fr` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `itFullText` (`it` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `nlFullText` (`nl` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `plFullText` (`pl` ASC);
ALTER TABLE pro_search ADD FULLTEXT INDEX `ptFullText` (`pt` ASC);

enter image description here

I then tried to do a few tests to see what results I would get back:

$lang = 'en';
$term = 'this is a test';

$params = [$term];
$sql = "SELECT * FROM pro_search WHERE $lang=?;";
$stmt = DB::run($sql,$params);
$count = $stmt->rowCount();
echo $count.'<br />';
// returns '1' so there is definitely a match

$params = ['%'.$term.'%'];
$sql = "SELECT * FROM pro_search WHERE $lang LIKE '?';";
$stmt = DB::run($sql,$params);
$count = $stmt->rowCount();
echo $count.'<br />';
// returns '0'

$params = [$term];
$sql = "SELECT * FROM pro_search WHERE MATCH ($lang) AGAINST ('?');";
$stmt = DB::run($sql,$params);
$count = $stmt->rowCount();
echo $count.'<br />';
// returns '0'

$params = [$term];
$sql = "SELECT * FROM pro_search WHERE MATCH ($lang) AGAINST ('?' IN NATURAL LANGUAGE MODE);";
$stmt = DB::run($sql,$params);
$count = $stmt->rowCount();
echo $count.'<br />';
// returns '0'

$params = [$term];
$sql = "SELECT * FROM pro_search WHERE MATCH ($lang) AGAINST ('?' WITH QUERY EXPANSION);";
$stmt = DB::run($sql,$params);
$count = $stmt->rowCount();
echo $count.'<br />';
// returns '0'

$params = [$term];
$sql = "SELECT * FROM pro_search WHERE MATCH ($lang) AGAINST ('?' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);";
$stmt = DB::run($sql,$params);
$count = $stmt->rowCount();
echo $count.'<br />';
// returns '0'

As you can see the first exact query returns a result, and if I do a query directly in phpMyAdmin of SELECT * FROM pro_search WHERE en LIKE '%test%' I get 7 total results but none in my second query here in my php file which is exactly the same.

Am I missing something here? I thought all of these should be returning at least one result if not more.

Edit

Here is my create code for my table as it stands just in case it helps:

CREATE TABLE `pro_search` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `pro_id` int(11) NOT NULL,
 `en` varchar(255) DEFAULT NULL,
 `de` varchar(255) DEFAULT NULL,
 `es` varchar(255) DEFAULT NULL,
 `fr` varchar(255) DEFAULT NULL,
 `it` varchar(255) DEFAULT NULL,
 `nl` varchar(255) DEFAULT NULL,
 `pl` varchar(255) DEFAULT NULL,
 `pt` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `FullText` (`en`,`de`,`es`,`fr`,`it`,`nl`,`pl`,`pt`),
 FULLTEXT KEY `enFullText` (`en`),
 FULLTEXT KEY `deFullText` (`de`),
 FULLTEXT KEY `esFullText` (`es`),
 FULLTEXT KEY `frFullText` (`fr`),
 FULLTEXT KEY `itFullText` (`it`),
 FULLTEXT KEY `nlFullText` (`nl`),
 FULLTEXT KEY `plFullText` (`pl`),
 FULLTEXT KEY `ptFullText` (`pt`)
) ENGINE=MyISAM AUTO_INCREMENT=1597 DEFAULT CHARSET=latin1

Also, how would people recommend searching in multiple languages? I set it up this way that the product meta is translated, but we don’t have a translator so some of them are straight from Google translate with just a few changes here and there. However, if I get this working I might try creating an index directly from the products table that includes product title, description and meta.

The way I see it I have two options:

  1. Store product translations and search these, or
  2. Translate the search term and search in English

Either way we’re probably going to be dealing with bad Google translations.

Tags:

3

Answers


  1. 0

    I assume DB::run($sql,$params); adds quotes to the string? If so, it creates

    WHERE en LIKE '"stuff"'

    Remove your quotes.

    Login or Signup to reply.
  2. 0

    Try removing the ' around your ?

      $params = ['%'.$term.'%'];
  $sql = "SELECT * FROM pro_search WHERE $lang LIKE ?;";
  $stmt = DB::run($sql,$params);
  $count = $stmt->rowCount();
  echo $count.'<br />';

    In the first statement you are adding the string parameter without any quotes: $lang=?. This would imply that you do not need the single quotes around the ? for other statements;

    Login or Signup to reply.
  3. 0

    Only the first query have no quotes.

    So DB::run is adding quotes.

    Remove quotes from all MATCH … AGAINST queries.

    This way they should work all.

    EDIT:
    try this

    $params = [$term];
$sql = "SELECT * FROM pro_search WHERE MATCH ($lang) AGAINST (?);";
$stmt = DB::run($sql,$params);
$count = $stmt->rowCount();
echo $count.'<br />';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search