Error While using MATCH() AGAINST() in php mysql

Question

I'm new in php. I am trying to search mysql dayabase using MATCH AGAINST rather than using LIKE. using this script,

    <?php

 if (isset($_GET['q'])){

        error_reporting(-1);



      $query = $_GET['q'];

        $dbh  = new mysqli($host, $user, $password,  $database);

        if ($dbh->connect_error) {
            echo 'Unable to connect to database '. $dbh->connect_error;
        } else {


            if ($stmt = $dbh->prepare("SELECT index, sura, aya, text FROM bn_bengali WHERE MATCH(sura,text) AGAINST(?) "))

               {
                $stmt->bind_param("s", $query);

                $stmt->execute();

                $stmt->bind_result($index, $sura, $aya, $text);


                $stmt->store_result();
              printf("Number of rows: %d.\n", $stmt->num_rows);


                while ($stmt->fetch()) {
                    echo $sura.'-'.$aya;
                    echo $text;
                    echo '<hr />';
                }
            } else {
                   echo "Prepare failed: (" . $dbh->errno . ") " . $dbh->error;
            }
        }


} // end isset get q
else
{
     echo '<form action="" ><input type="text" name="q"><button>Search</button></form>';
}
    ?>

but it's giving this error,

Prepare failed: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index, sura, aya, text FROM bn_bengali WHERE MATCH(sura,text) AGAINST(?)' at line 1

Where is the problem in this Script?

I want to search the database table with match against.

But the same script is working fine with

SELECT sura, aya, text FROM bn_bengali WHERE text LIKE ?

why not match against is working? Where is the problem in this script?

Index is Reserved Words in mysql – Ankur Tiwari Apr 25 '16 at 06:04 — Ankur Tiwari, Apr 25 '16 at 06:04

score 3 · Accepted Answer · answered Apr 25 '16 at 06:05

3

index is Reserved Words in mysql it must be in backtick

Your query would be

SELECT `index`, `sura`, `aya`, `text`...

answered Apr 25 '16 at 06:05

Saty

22,443
7
33
51

after changing SELECT `index`, `sura`, `aya`, `text` its giving this error Prepare failed: (1191) Can't find FULLTEXT index matching the column list – Niamul Hasan Apr 25 '16 at 06:12
Please refer [link](http://stackoverflow.com/questions/9680472/cant-find-fulltext-index-matching-the-column-list-indexes-is-set) for your error – Saty Apr 25 '16 at 06:14
I already had the text in longtext. but after running this ALTER TABLE bn_bengali ADD FULLTEXT(text); the error is still there – Niamul Hasan Apr 25 '16 at 06:23
Use query `ALTER TABLE bn_bengali ADD FULLTEXT(`text`);` and `ALTER TABLE bn_bengali ADD FULLTEXT(`sura`);` in your phpmyadmin and check – Saty Apr 25 '16 at 06:26

score 1 · Answer 2 · answered Apr 25 '16 at 06:11

1

index is Reserved Words in mysql it must be in backtick

Try changing your query to this :

SELECT `index`, `sura`, `aya`, `text` FROM...

Also I would suggest you to change the column name so that you don't face problem in future as well, as after doing this change there is possibility of getting errors.

answered Apr 25 '16 at 06:11

Nehal

1,542
4
17
30

after changing SELECT `index`, `sura`, `aya`, `text` its giving this error Prepare failed: (1191) Can't find FULLTEXT index matching the column list – Niamul Hasan Apr 25 '16 at 06:14
@HasanBinKarim, please try to change your column name by using `ALTER` query – Nehal Apr 25 '16 at 06:16
I changed the name index to id but the error Prepare failed: (1191) Can't find FULLTEXT index matching the column list is still there – Niamul Hasan Apr 25 '16 at 06:28
have you changed your query also @HasanBinKarim, to `SELECT `id`, `sura`, `aya`, `text` FROM...` – Nehal Apr 25 '16 at 06:30

score 1 · Answer 3 · answered Apr 25 '16 at 06:13

It is good practice to add 'backtick' with every column in query. so that if you use even mysql reserve keyword then it will not create any problem. Try below code.

  if ($dbh->connect_error) {
            echo 'Unable to connect to database '. $dbh->connect_error;
        } else {


            if ($stmt = $dbh->prepare("SELECT `index`, `sura`, `aya`, `text` FROM bn_bengali WHERE MATCH(sura,text) AGAINST(?) "))

               {
                $stmt->bind_param("s", $query);

                $stmt->execute();

                $stmt->bind_result($index, $sura, $aya, $text);


                $stmt->store_result();
              printf("Number of rows: %d.\n", $stmt->num_rows);


                while ($stmt->fetch()) {
                    echo $sura.'-'.$aya;
                    echo $text;
                    echo '<hr />';
                }
            } else {
                   echo "Prepare failed: (" . $dbh->errno . ") " . $dbh->error;
            }
        }


} // end isset get q
else
{
     echo '<form action="" ><input type="text" name="q"><button>Search</button></form>';
}
    ?>

Error While using MATCH() AGAINST() in php mysql

3 Answers3