WChampion WChampion - 6 months ago 23
SQL Question

Need help tuning a select query to change a Match/Against clause to a Like clause

Im currently trying to incorportate a LIKE clause into my sql select statement and take out the associating MATCH/AGAINST clause.

Ive tried many different variations of it and feel that im missing something since none have seemed to work yet.

The full php Page is

<?php

if(isset($_POST['searchQuery']))
{
require_once('config.inc.php');
$search_query=$_POST['searchQuery'];

$sql = 'SELECT * from tbl_fish where MATCH(fish_name,size_name,cat_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";
}
}

?>


Ive tried statements like this

$sql = 'SELECT * from tbl_fish WHERE (fish_name LIKE '%'searchQuery'%')';


But I have yet to come up with a working one. These results are being queryied from an android appllication so im wondering if im missing something more than just changing that one statement.I was following a tutorial for this and it all works except when i try to change the parameters for the query.

Hoping to get some feedback from you guys. Thanks.

Answer

For build a proper like clause you can use concat

$sql = "SELECT * from tbl_fish WHERE  fish_name LIKE concat ( '%', :searchQuery, '%')";

and for match the 3 columns you should use OR

$sql = "SELECT * from tbl_fish 
   WHERE  fish_name LIKE concat ( '%', :searchQuery, '%')
     or size_name   LIKE concat ( '%', :searchQuery, '%')
     or cat_name    LIKE concat ( '%', :searchQuery, '%');";