Combine multiple SQL query results

Question

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:

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.

Any chance you may refactor your DB? Just create ProductCategory (ProductId, Category) table and let DBMS do the job with proper query, — Serg
– Serg, Commented May 10, 2016 at 8:09
A like '%something%' query in a loop? that's a database killer. As others have suggested before me you need to rethink your database design. — e4c5
– e4c5, Commented May 10, 2016 at 8:42
@Strawberry I was having an array that I was supposed to insert in ProductCategoryID, so I used the function implode() to separate the different elements of the array, so that they can be insert in the table — Lei Lionel
– Lei Lionel, Commented May 10, 2016 at 16:11

Lei Lionel · Accepted Answer · 2016-05-10 18:08:44Z

2

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

edited May 10, 2016 at 18:08

Lei Lionel

1,27313 silver badges20 bronze badges

answered May 10, 2016 at 8:20

Pinke Helga

6,7722 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

André Schild · Accepted Answer · 2016-05-10 09:32:28Z

0

$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

edited May 10, 2016 at 9:32

André Schild

4,7845 gold badges31 silver badges46 bronze badges

answered May 10, 2016 at 9:25

Nadeem Nellicka

11 bronze badge

Collectives™ on Stack Overflow

Combine multiple SQL query results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related