PHP Search MySQL database Fatal error

Question

I wrote the query system with android studio and PHP.

My PHP code is as follows.Opened with a web browser but an error occurred.The error message is as follows

Notice: Undefined index: searchQuery in C:\xampp\htdocs\client\beetle_search.php on line 5

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table 1 where MATCH(name,scientific_name) AGAINST(NULL)' at line 1' in C:\xampp\htdocs\client\beetle_search.php:9 Stack trace: #0 C:\xampp\htdocs\client\beetle_search.php(9): PDOStatement->execute() 1 {main} thrown in C:\xampp\htdocs\client\beetle_search.php on line 9

   require_once('config.inc.php');
   $search_query=$_POST['searchQuery'];
   $sql = 'SELECT * from table 1 where MATCH(name,scientific_name) AGAINST(:search_query)';
   $statement = $connection->prepare($sql);
   $statement->bindParam(':search_query', $search_query, PDO::PARAM_STR);
   $statement->execute();
   if($statement->rowCount())
      {
         $row_all = $statement->fetchall(PDO::FETCH_ASSOC);
         header('Content-type: application/json');
         echo json_encode($row_all);
      }  
   elseif(!$statement->rowCount())
      {
         echo "no rows";
      }

Where did you write it wrong? thanks

`table 1` is that your actual name for the table? – Funk Forty Niner Aug 22 '18 at 18:44 — Funk Forty Niner, Aug 22 '18 at 18:44

score 0 · Answer 1 · 2018-08-22T19:25:19.997

0

You should add a table name in your query. Update your query

from

$sql = 'SELECT * from table 1 where MATCH(name,scientific_name) AGAINST(:search_query)';

to

$sql = 'SELECT * from table_name where MATCH(name,scientific_name) AGAINST(:search_query)';

Also, in order to use MATCH function, you must have the full text index on the column. Otherwise, you will get an error Can't find FULLTEXT index matching the column list'. The solution of this problem is given below...

Assuming you are using MyISAM engine, Execute:

ALTER TABLE table_name ADD FULLTEXT(name);
ALTER TABLE table_name ADD FULLTEXT(scientific_name);

edited Aug 22 '18 at 19:25

answered Aug 22 '18 at 18:42

*"You should add a table name in your query."* - They have, but it seems to be named "table" being a mysql reserved word. – Funk Forty Niner Aug 22 '18 at 18:46
"table 1" is not a reserved keyword, but it does require escaping to use. – tadman Aug 22 '18 at 18:55
@tadman I take it that that ^ was meant for me. mysql complained about `for the right syntax to use near 'table` << *ahem*. – Funk Forty Niner Aug 22 '18 at 18:56
Notice: Undefined index: searchQuery in C:\xampp\htdocs\client\beetle_search.php on line 5 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\client\beetle_search.php:9 Stack trace: #0 C:\xampp\htdocs\client\beetle_search.php(9): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\client\beetle_search.php on line 9 – 皢嘖嘖 Aug 22 '18 at 19:03
Please verify either these columns "name" and "scientific_name" exist in your database? – Aug 22 '18 at 19:09
"name" and "scientific_name" exist in my database – 皢嘖嘖 Aug 22 '18 at 19:17
@皢嘖嘖 i have updated my answer. Please check it and follow it – Aug 22 '18 at 19:25
Still an error is declared: – 皢嘖嘖 Aug 22 '18 at 19:56
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\client\beetle_search.php:9 Stack trace: #0 C:\xampp\htdocs\client\beetle_search.php(9): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\client\beetle_search.php on line 9 – 皢嘖嘖 Aug 22 '18 at 19:56
@皢嘖嘖 You are still getting same error "General error: 1191 Can't find FULLTEXT index matching the column list' " Its mean you didn't add fulltext index on your column – Aug 23 '18 at 07:12

PHP Search MySQL database Fatal error

1 Answers1