I'm trying to learn how to put a useful search function on my site, where users can affix certain tags to their database entries. The tags live in a keyword column which holds the data as a comma separated string (ie red, car, apple), that (in theory) can be searched for at a later time.
When I construct my sql search like this:
$sql = "SELECT * FROM reporting WHERE (keywords LIKE '%" . $search. "%' )
AND user_id = $user_id
ORDER BY spc_class";
//-run the query against the mysql query function
$result = mysqli_query($conn, $sql);
//-create while loop and loop through result set
$colNames = array();
$data = array();
if ($result->num_rows > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$data[] = $row;
}
$colNames = array_keys(reset($data));
} else {
echo "0 results";
}
It works reasonably well, except if someone searches for multiple words. That problem has been covered extensively in these forums, and it seems that most answers recommend converting the column to be searched to a full text value format in mysql.
I have done this, but when I change my search query like this, I get a non-object found error lwhen using the MATCH AGAINST construct:
$sql = "SELECT * FROM reporting WHERE MATCH(keywords) AGAINST('".$search."')
AND user_id = $user_id
ORDER BY spc_class";
//-run the query against the mysql query function
$result = mysqli_query($conn, $sql);
//-create while loop and loop through result set
$colNames = array();
$data = array();
if ($result->num_rows > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$data[] = $row;
}
$colNames = array_keys(reset($data));
} else {
echo "0 results";
}
Why am I able to execute the mysql search using the WHERE LIKE construct, but not the MATCH AGAINST query? Does the latter not return an object? I can't seem to figure out my error in using the full-text search query.
