array_intersect within foreach loop

Question

this is my first time posting here, although I have gotten many great tips and techniques reading the posts here.

Here is my objective:

I have 2 somewhat similar tables to compare. For each row of each table, I am pulling the fields I want into into an array.

I basically want to echo out the values of any array from one table that has matching values in the other array.

Here is my code, maybe it will be easier to understand.

$sql = "SELECT * FROM $i_comp ORDER BY `manufacturer`";
$statement = $objDb->query($sql);
$c_skus = $statement->fetchAll(PDO::FETCH_ASSOC);

$sql = "SELECT `sku_one`,`sku_two`,`qty`,`manufacturer`";
$sql .= "FROM $i_gas ORDER BY `manufacturer`";
$statement = $objDb->query($sql);
$d_skus = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach ( $c_skus as $c_sku ) {
    // i want to see if any values of this array exist in the array created hy
    // the foreach loop below (yes, repeat for each row)
    $c = array($c_sku['sku'],$c_sku['sku_one'],$c_sku['sku_two'],$c_sku['sku_three']);
    foreach ( $d_skus as $d_sku ) {
        $d = array($d_sku['sku_one'],$d_sku['sku_two']);
        $intersect = array_intersect($c, $d);
        echo '<pre>', print_r($intersect), '</pre>';
    }
}

Here are the results I receive for each iteration of the code:

Array
(
)
1

It should also be noted that I am not concerned with the Keys, just the Values. Eventually, those values will be works into an INSERT statement but for the time being I just need to get the right results.

Anyways, thanks for any and all help!

are you asking what to do with the output that you are getting? — thescientist, Oct 12 '12 at 23:57
If we understand your logic am sure is something that can be done with SQL — Baba, Oct 12 '12 at 23:58
I really think you should just use an SQL join statement for this task, unless you need something that is not stated in your question. — Sven, Oct 13 '12 at 00:00
What my goal is: to use d.qty in an INSERT statement. But while I am teating, I want to echo out for all matching rows c.sku & d.qty and then I will manually check a few of them to make sure its giving the right results. All of the code that Imre L posted below were working nucely at first but now it seems that they are going slow. Is it possible that I could have my tables not indexed properly? — Shawn Abramson, Oct 13 '12 at 13:56

Imre L · Accepted Answer · 2012-10-13T00:41:37.693

This is usually done in SQL

if you want whole row that has at least 1 matching SKU in another table:

$sql = "
SELECT c.* FROM $i_comp AS c 
  JOIN $i_gas AS d 
    ON d.sku_one in (c.sku, c.sku_one, c.sku_two, c.sku_three) 
    OR d.sku_two in (c.sku, c.sku_one, c.sku_two, c.sku_three) 
 ORDER BY c.`manufacturer`";
$statement = $objDb->query($sql);
$c_skus = $statement->fetchAll(PDO::FETCH_ASSOC);
// all rows that have at least 1 matching sku on another table
print_r($c_skus);

If you want only SKUs

$sql = "
SELECT d.sku_one FROM $i_comp AS c 
  JOIN $i_gas AS d 
    ON d.sku_one in (c.sku, c.sku_one, c.sku_two, c.sku_three) 
UNION
SELECT d.sku_two FROM $i_comp AS c 
  JOIN $i_gas AS d 
    OR d.sku_two in (c.sku, c.sku_one, c.sku_two, c.sku_three) 
 ";
$statement = $objDb->query($sql);
$c_skus = $statement->fetchAll(PDO::FETCH_ASSOC);
// all skus that have at least 1 matching sku on another table
print_r($c_skus);

In PHP intersect variant you need to build the arrays separately and then use array_intersect

$c = array();
foreach ( $c_skus as $c_sku ) {
    $c[] = $c_sku['sku'];
    $c[] = $c_sku['sku_one'];
    $c[] = $c_sku['sku_two'];
    $c[] = $c_sku['sku_three'];
}
$d = array();
foreach ( $d_skus as $d_sku ) {
    $d[] = $d_sku['sku_one'];
    $d[] = $d_sku['sku_two'];
}

$intersect = array_intersect($c, $d);
echo '<pre>', print_r($intersect), '</pre>';

Great work with all 3! The first is the most practical to me now that I have tried them all out. So now I need to loopify this query and for the most part thats the easy part. The ridiculous queries I was testing out were really taxing the database so my next round of testing involved pulling all of the data into php and using either array_intersect or a pretty complex loop of if(in_array(.......) Thanks for the great advice! — Shawn Abramson, Oct 13 '12 at 01:26

array_intersect within foreach loop

1 Answers1