Lei Lionel Lei Lionel - 6 months ago 12
SQL Question

Combine multiple SQL query results

I am working with PHP and I have made this code:
`

$categories = array('casual','dinner', 'kids');
$numberOfCategories = count($categories);

for ($i=0; $i < $numberOfCategories; $i++) {

$req = $pdo->query('
SELECT ProductID
, ProductCategoryID
FROM products
WHERE ProductCategoryID LIKE "%'.$categories[$i].'%"
');

while (${"relatedProduct" . $i} = $req->fetch()) {
var_dump(${"relatedProduct" . $i});
}
}


`

After running the code I got the following result:
Query Results

If you look at it closely, you will notice that certain products repeat them self (which is normal).

What I want to do now his to combine the result of each loop stored in the variable
${"relatedProduct" . $i}
and filter that result(result after combining the result of each loop) to avoid repetition of products based on the column
ProductID


Kindly help me solve this problem.

Answer

Avoid inclusion of strings directly into SQL (potential danger of SQL injection attacks). Use parameterized prepared statements instead.

DISTINCT will give you unique rows (Prod.ID / Cat.ID combination). You can combine the search needles with | and compare it as a regular expression by RLIKE. The result is the sum of what you get with LIKE %needle% on each.

$req = $pdo->prepare('
  SELECT DISTINCT
    `ProductID`,
    `ProductCategoryID`
  FROM
    `products`
  WHERE
    `ProductCategoryID` RLIKE :pattern
  ');

$categories = array('casual','dinner', 'kids');
$cats = implode("|", $categories);

$req->bindParam(':pattern', $cats, PDO::PARAM_STR);
$req->execute();