CODEWITHSUNDEEP
phpmysqlsql
Lei Lionel
Lei Lionel

Reputation: 1273

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.

Upvotes: 0

Views: 506

Answers (2)

Pinke Helga
Pinke Helga

Reputation: 6682

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();

Upvotes: 2

Nadeem Nellicka
Nadeem Nellicka

Reputation: 1

$pdo->query(' SELECT GROUP_CONCAT(ProductCategoryID) as  ProductCategoryID
                      FROM products 
                     WHERE ProductCategoryID LIKE "%'.$categories[$i].'%" 
                     ');

using this U'll get a single row with all productCategoryIDs with matching conditions.

Now you can use array_unique() function to get unique ProductCategoryID

Upvotes: 0

Related Questions