CODEWITHSUNDEEP
phphtmlmysql
Ichida
Ichida

Reputation: 349

Foreach mysql select result

I want to create a search field like if I search "Spinach, Watermelon", it will explode the input by ",", and run MySQL search database. That means the SQL will run as 

SELECT * FROM table
  WHERE vegetable LIKE '%Spinach%' OR fruits LIKE '%Spinach%'

SELECT * FROM table
  WHERE vegetable LIKE '%Watermelon%' OR fruits LIKE '%Watermelon%'

My database table data looks something like this :

id  Vegetable  fruits
----------------------
1   Spinach    Apple
2   Cucumber   Orange
3   Spinach    Watermelon

The result of each id only can come out once.

<php>
$keywords = trim($_REQUEST['keyword']);
$keyword = explode(",", $keywords);
foreach($keyword as $keys=>$select)
{
   $sql = "SELECT * FROM table WHERE vegetable LIKE '%keyword %' OR fruits LIKE '%keyword %'";
   $result = mysqli_query($conn, $sql);
   while ($rs = mysqli_fetch_array($result))
   {    
       $vege = $rs["vegetable"];
       $fruits = $rs["fruits"];
   }
}

<html>
<form method=post>
    <input type="text" class="form-control" placeholder="SEARCH..." value="<?=$keywords?>">
</form>
</html>

Upvotes: 2

Views: 1889

Answers (4)

m ba
m ba

Reputation: 134

I think your probelm is "*The result of each id only can come out once.".

So better make one Query:


$keywords = trim($_REQUEST['keyword']);
$keyword = explode(",", $keywords);
$where = [];
foreach($keyword as $keys)
{
   $where[] = "vegetable LIKE '%".$keys." %' OR fruits LIKE '%".$keys." %'";
}
$sql = "SELECT * FROM table ".(empty($where) ? '' : implode(' OR ', $where));

so you have one query an one result set.

Dont forgett to escape your keywords.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

If you are open to using REGEXP with an alternation instead of LIKE, then here is a straightforward approach:

$keywords = trim($_REQUEST['keyword']);
$keywords = preg_replace("/,\s*/", "|", $keywords);
$where = "[[:<:]](" . $keywords . ")[[:>:]]";

$sql = "SELECT * FROM table ";
$sql .= "WHERE vegetable REGEXP '" . $where . "' OR fruits REGEXP '" . $where . "'";
$result = mysqli_query($conn, $sql);
while ($rs = mysqli_fetch_array($result)) {
    $vege = $rs["vegetable"];
    $fruits = $rs["fruits"];
}

Assuming you passed in the keywords search string "Spinach, Watermelon", the above script would generate this query:

SELECT *
FROM table
WHERE
    vegetable REGEXP '[[:<:]](Spinach|Watermelon)[[:>:]]' OR
    fruits REGEXP '[[:<:]](Spinach|Watermelon)[[:>:]]';

Honestly the best approach here would be to use LIKE or REGEXP with a prepared statement. If you do choose to use my approach, then you absolutely should sterilize the incoming CSV string to make sure it has only alphanumeric characters, comma, and whitespace in it.

Upvotes: 2

Rohit Ghotkar
Rohit Ghotkar

Reputation: 803

Hitting database in for loop is not a good approach, You can build you query like this: 

<?php
$keywords = trim($_REQUEST['keyword']);
$keyword = explode(",", $keywords);
$sqlWhere = '';
foreach($keyword as $keys=>$select)
{
   $sqlWhere .= "LIKE '%$select%' OR";
}
$sqlWhere = rtrim($sqlWhere, "OR");

$sql = "SELECT * FROM table " . $sqlWhere;
$result = mysqli_query($conn, $sql);
while ($rs = mysqli_fetch_array($result))
{    
       $vege = $rs["vegetable"];
       $fruits = $rs["fruits"];
}    
?>

Upvotes: 1

Abdul Rehman Sheikh
Abdul Rehman Sheikh

Reputation: 939

Try this, It will print your records must.

<php>
$keywords = trim($_REQUEST['keyword']);
$keyword = explode(",", $keywords);
for($i=0; $i<count($keyword); $i++)
{
   $sql = "SELECT * FROM table WHERE vegetable LIKE '%" . $keyword[$i] . "%' OR fruits LIKE '%" . " . $keyword[$i] . " . "%'";
   $result = mysqli_query($conn, $sql);
   $rs = mysqli_fetch_array($result); 
   echo "<pre>"; print_r($rs); exit; 
}

?>
<html>
<form method=post>
    <input type="text" class="form-control" placeholder="SEARCH..." value="<?=$keywords?>">
</form>
</html>

Upvotes: 0

Related Questions