I need some help for the logic of a SELECT
query and hope someone can give me a better idea than the one I have.
I have a table like this:
+---------+----------+---------+-----------+
| id | model | variant | category |
+---------+----------+---------+-----------+
| 1 | round | black | fruit |
| 2 | square | white | vegetable |
| 3 | flat | red | meat |
| 4 | round | black | meat |
+---------+----------+---------+-----------+
Users will be allowed to do a search on this table by entering one or more keywords.
The query I have right now does a MATCH
on all entered keywords AGAINST
each field and I get rows that match ANY of the keywords.
So if the input is "round meat", the result will be rows 1, 3 and 4:
SELECT v.*, vt.color
FROM tbl_items
AS v
LEFT JOIN tbl_apple
AS vt
ON v.id = vt.v_id
WHERE (
MATCH (v.model) AGAINST ('keyword1 keyword2' IN BOOLEAN MODE)
OR MATCH (v.variant) AGAINST ('keyword1 keyword2' IN BOOLEAN MODE)
OR MATCH (v.category) AGAINST ('keyword1 keyword2 ' IN BOOLEAN MODE) )
AND v.type = 4
However, I need to only return the rows that contain ALL keywords. The keywords might be contained in any of the three columns.
So if the user enters "round meat", only row 4 is returned.
My current idea is very simple and probably not very good (I expect some performance issues for multiple keywords):
SELECT v.*, vt.color
FROM tbl_items
AS v
LEFT JOIN tbl_apple
AS vt
ON v.id = vt.v_id
WHERE
(
(v.model = 'keyword1'
OR v.variant = 'keyword1'
OR v.category = 'keyword1'
)
AND
(v.model = 'keyword2'
OR v.variant = 'keyword2'
OR v.category = 'keyword2'
)
)
AND v.type = 4
Is there a better way to do it?
This is a whole code that may helps you." You can modify query according to database or requirements.
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'test');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
if(isset($_POST['submit']))
{
$search_text = $_POST['text'];
$values = explode(" ",$search_text);
$query = "";
foreach($values as $value)
{
$query .='concat(model,varient,category) like "%'.$value.'%" AND ';
}
$query .= "1=1";
$query = "select * from tbl_items where ".$query."";
$result= mysqli_query($db,$query);
echo '<table>';
while($row=mysqli_fetch_array($result))
{
echo '<tr>';
echo '<td>'.$row['model'].'</td><td>'.$row['varient'].'</td><td>'.$row['category'].'</td>';
echo '</tr>';
}
echo '</table>';
}
?>
<form method="post" name="form">
<input type="text" name="text" />
<input type="submit" value="submit" name="submit">
</form>