0

I seem to be having problems with my code and can't trace the issue. I want to have a search for multiple columns in my db, but I'm getting an error report.

I altered the table like so

"ALTER TABLE products ADD FULLTEXT alltext (`title`, `cat`, `status`, `description`, `tags`)";

And also changed the default engine to MyISAM

But when I run this query by searching through my search box I get an error.

$sql="SELECT * FROM products as p INNER JOIN amenities as a ON p.product_id=a.product_id WHERE MATCH(p.title,p.cat,p.status,p.description,p.tags) AGAINST ('$search' )"; 
mysqli_query($con,$sql);

And other conditions follows....

Which should give me results if my search word is in any of the rows.. But it returns a bad query error. Also tried adding an IN BOOLEAN just after the '$search'... i.e

 $sql="SELECT * FROM products as p INNER JOIN amenities as a ON p.product_id=a.product_id WHERE MATCH(p.title,p.cat,p.status,p.description,p.tags) AGAINST ('$search' IN BOOLEAN)";

Which gives no results when there are obviously matching terms in the table rows.

This is my search variable

$search = mysqli_real_escape_string($con, $_POST['q']);

Any help on this will be appreciated.

Makzino
  • 43
  • 9
  • Just to be sure, you are using the right quotation marks for your string literals, right? Please edit your code accordingly. – AndreasT Jan 20 '18 at 08:29
  • Okay, will do.. But the ...... Thing is just to show that it continues or there were some codes before it.. Let me edit to make it clearer.. Thanks for replying – Makzino Jan 20 '18 at 08:32

1 Answers1

0

From what I see here it is hard to give you a direct solution, but I would follow this strategy:

  1. Replace the $search variable in your query with a query string you know should work.
  2. BOOLEAN queries use a special language syntax, try that only after you have gotten a good result in NATURAL LANGUAGE mode.
  3. Fishing for straws: I have no time to test this right now, but I am not sure if mysql would allow you to create a FULLTEXT index on columns that are of incompatible type or if it would complain on index creation. Just something to check.

Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.

AndreasT
  • 9,417
  • 11
  • 46
  • 60
  • yeah.. All columns listed are VARCHAR columns. And I already created the FULLTEXT index in phymyadmin SQL box which is reflected on the structure base. – Makzino Jan 20 '18 at 08:59