remove duplicate in array foreach mysql

Question

Hi how to create array without duplicate name??

$sqla = "SELECT * from categoriesb";
$id = $mysql->Execute($sqla);
foreach ($id as $keys) {
    $sqlb = "SELECT * from productsb where parent = ".$keys["id"]."";
    $cac = $mysql->Execute($sqlb);
    foreach ($cac as $key) {
        $tab[] = array("name" => $keys["name_c"],
                        array("product" => $key["name"]));

    }
}

And my array looks like this

var_dump($tab);

array(2) {
[0]=> array(2) {
   ["name"]=> string(10) "Beer"
   [0]=> array(1) {
       ["product"]=> string(13) "Mild"
   }
}
[1]=> array(2) {
   ["name"]=> string(10) "Beer"
   [0]=> array(1) {
       ["product"]=> string(13) "Bitter"
   }
}

How to remove dupcliate name on my array ?
I would like to create array

array(1) {
[0]=> array(2) {
    ["name"]=> string(10) "Beer"
    [0]=> array(1) {
       ["product"]=> string(13) "Mild"
    }
    [1]=> array(1) {
       ["product"]=> string(13) "Bitter"
    }
}

I guess you want to make a [multimensional array](https://www.w3schools.com/php/php_arrays_multi.asp) from your database data. The first dimension would be your product ("Beer" in your sample) and the second dimension would be the type ("Mild", "Bitter"). So far this question seems more related to php rather than mysql, consider editing your question tags. — Cleptus, Aug 05 '19 at 12:23

score 0 · Answer 1 · answered Aug 05 '19 at 12:17

0

Use array_unique function to avoid Duplicates in list

answered Aug 05 '19 at 12:17

sreenivas t

1
2

apokryfos · Answer 2 · 2019-08-12T12:57:39.793

Note: Your query suffers from the N+1 problem.

It also might suffer from a second order SQL injection if the id is a user generated string.

Here's a potential solution:

$sqla = "SELECT * from categoriesb";
$categories = $mysql->Execute($sqla);
$ids = array_column($categories, 'id');
// Query with parameters
$sqlb = "SELECT * from productsb where parent IN (".implode(',', array_fill(0, count($ids), '?')).")";
// Not exactly sure what API is used here but this needs to execute the query that uses the $ids as the parameters
$products = $mysql->Execute($sqlb, $ids); 

foreach ($categories as $category) {
    $tab[$category['id']] = [ "name" => $category["name_c"] ];
}
foreach ($products as $product) {
    $tab[$product['parent']][] = array("product" => $product["name"]));
}

This will:

Perform one query to get all categories
Perform one query to get all related products but only for the categories retrieved (in your case a simple select * would work but if you later need to add filters to the first select you'd need to narrow it down
Build the array using the categories and setting the id as the key (for later lookup)
Update the array based on the products

It will always only do 2 queries instead of 1 + Number of products and if you use a prepared statement you can also avoid a second order injection (if that might be an issue, typically if the id is not user defined then it probably wouldn't be an issue)

remove duplicate in array foreach mysql

2 Answers2