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
– thescientist, Commented Oct 12, 2012 at 23:57
If we understand your logic am sure is something that can be done with SQL — Baba
– Baba, Commented Oct 12, 2012 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
– Sven, Commented Oct 13, 2012 at 0: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
– Shawn Abramson, Commented Oct 13, 2012 at 13:56

Imre L · Accepted Answer · 2012-10-13 00:41:37Z

1

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>';

edited Oct 13, 2012 at 0:41

answered Oct 13, 2012 at 0:32

Imre L

6,29126 silver badges32 bronze badges

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

1 Comment

Shawn Abramson Over a year ago

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!

Collectives™ on Stack Overflow

array_intersect within foreach loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related