I am trying to split a string into an array and use these as keywords to make an sql query. I have made a sample of splitting the array and building the sql query. It sort of works but it is giving every table as a result but when I hard copy the built query it comes up with expected results.
This is what I have so far -
The string of keywords split into array and the query is built.
The db is called 'clients_personal' and the table is called 'likes'
$my_search = "paper, glue, discount, bulk";
$new_search = preg_split("/,/", $my_search);
$mmsql = "SELECT * FROM clients_personal WHERE likes LIKE '%offers%'";
foreach ($new_search as $value) {
$mmsql = $mmsql." OR likes LIKE '%$value%'";
}
No that results something like :
$mmsql="SELECT * FROM clients_personal WHERE likes LIKE '%offers%' OR likes LIKE '%paper%' OR likes LIKE '%glue%' OR likes LIKE '%discount%' OR likes LIKE '%bulk%'";
Now if I search like this, I get all the rows in the db?
$sql = "$mmsql";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$id=$row["id"];
echo $id;
}
}
But if I query is as hard coded it gives predicted results?
$sql = "SELECT * FROM clients_personal WHERE likes LIKE '%offers%' OR likes LIKE '%paper%' OR likes LIKE '%glue%' OR likes LIKE '%discount%' OR likes LIKE '%bulk%'";
$result = $conn->query($sql);
I have a feeling its to do with quotes and i have tried removing them but no good? Any advice?
Also I an using this type of search as I found it on here.
My Answer:
"apple, banana, orange";
$keywords = explode(", ", $my_search);
$query = "SELECT * FROM likes WHERE ";
foreach($keywords as $key => $keyword) {
$query .= "keyword LIKE '%$keyword%'";
if($key < count($keywords) - 1) {
$query .= " OR ";
}
}
echo $query;
// Output: SELECT * FROM likes WHERE keyword LIKE '%apple%' OR keyword LIKE '%banana%' OR keyword LIKE '%orange%';
// Now you can execute this query in your PHP code to get the desired results.
Rate this post
5 of 5 based on 4325 votesComments