CODEWITHSUNDEEP
phpmysqlsearch
Niamul Hasan
Niamul Hasan

Reputation: 146

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

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?

Upvotes: 3

Views: 265

Answers (3)

sandeep soni
sandeep soni

Reputation: 303

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>';
}
    ?>

Upvotes: 1

Nehal
Nehal

Reputation: 1523

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.

Upvotes: 1

Saty
Saty

Reputation: 22532

index is Reserved Words in mysql it must be in backtick

Your query would be

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

Upvotes: 3

Related Questions