How can I compare two arrays of data retrieve from database?

Question

I have two arrays. In one array, I store data from one table. I store data from another table in the other array. I wanted to compare data from both of the arrays. If the data of the first array is in the second array, I want to proceed. How can I perform this?

I tried the following code but it's not working event though the array1 number exists in array2:

$x = "SELECT * FROM table1";
$data1 = mysqli_query($link, $x);
$dat1 = array()
while($row1= mysqli_fetch_array($data,MYSQLI_ASSOC))
{
  $dat1[] = $row1;
  $f1 = $row1['fid'];
}

$y = "SELECT * FROM table2";
$data2 = mysqli_query($link, $y);
$dat2 = array()
while($row2= mysqli_fetch_array($data2,MYSQLI_ASSOC))
{
  $dat2[] = $row2;
  $f2 = $row2['fid'];
}

if(in_array($dat1,$dat2))
{
  // if exists proceed
}
else
{
  // if not show error
}

Please provide a positive and negative example. Note that you've misspelled `$dat2` as `$dta2` in your code. — ljacqu, Jul 07 '14 at 07:10
Asker, is the `$dta2` in `if(in_array($data,$dta2))` a typo? — TribalChief, Jul 07 '14 at 07:11
haa its just a typo...let data1 = 1, 2,3 and data2= 1 when i check for `if(atleast one of data2 matches with data1) { //procesed }else { //show error}` — user3801544, Jul 07 '14 at 07:13
You can't compare them, using in_array function. Because it is supposed to deal with 1-dimensional arrays. And you have 2-dimentional arrays. You need to loop though them and compare. But this will work only if your arrays are sorted in the same manner — user4035, Jul 07 '14 at 07:14
I would suggest to optimize it. You can check sizes and other features of its elements before running heavy operations. This might increase performance. — Rolice, Jul 07 '14 at 07:15
I changed $data to $dat1 in the last `if` to fix the 2-nd typo — user4035, Jul 07 '14 at 07:16
possible duplicate of [in\_array() and multidimensional array](http://stackoverflow.com/questions/4128323/in-array-and-multidimensional-array) — user4035, Jul 07 '14 at 07:17
http://stackoverflow.com/questions/13459315/php-compare-2-multidimensional-arrays-and-output-values-if-equal-fields-in-arr — Vysakh, Jul 08 '14 at 07:00

score 1 · Answer 1 · answered Jul 07 '14 at 08:06

This can be done by SQL.

To check if all fids in table1 are in table2:

SELECT COUNT(a.fid) FROM table1 AS a WHERE a.fid IN (SELECT b.fid FROM table2 AS b)

SELECT COUNT(*) FROM table1

If the two values are equal, then all fids in table1 are in table2.

dxritchie · Answer 2 · 2014-07-07T13:24:22.833

According to your last comment your logic is wrong in your code then. You are currently pulling all rows into your $dat1 variable from your mysql return so you would not be able to check if a specific value exists in the 2nd array by using the entire array as a single value. You are also overwriting $f1 on each iteration of your while loop.

You could check this for each ID returned from your first array like this.

$x = "SELECT * FROM table1";
$data1 = mysqli_query($link, $x);
$dat1 = array()
while($row1= mysqli_fetch_array($data,MYSQLI_ASSOC))
{
  $dat1[] = $row1;
  $f1 = $row1['fid'];
  $y = "SELECT * FROM table2 where fid = ".$f1;
  $data2 = mysqli_query($link, $y);
  $dat2 = array();
  while($row2= mysqli_fetch_array($data2,MYSQLI_ASSOC))
  {
    //data exists in table2
  }
}

no they are not exactly identical just wanted to check if $dat1 if existing $dat2 — user3801544, Jul 07 '14 at 07:31

score 0 · Answer 3 · answered Jul 07 '14 at 07:15

0

if(in_array($data,$dat2))
{
  //if exists proceed
}
else
{
  //if not show error
}

why do you put $data here ? should be $dat1 no ?

answered Jul 07 '14 at 07:15

Eagle1

782
2
10
28

score 0 · Answer 4 · answered Jul 07 '14 at 07:53

You can use this way

 $bool=0;
 foreach ($dat1 as $a) {
    foreach ($dat2 as $b) {
        if($a==$b)
        {
            $bool=1;
            break;
        }
        else
        {
            $bool=0;
        }
    }
    if($bool==1)
    {
        break;
    }
 }
 if($bool==1)
 {
   //proceed
  }
 else
  {
     //error
   }

Here if anyone of the value in dat2 is present in dat1 then it will be true and you can proceed.

score 0 · Answer 5 · answered Jul 07 '14 at 13:34

As sotirojo just pointed out, don't try to do data stuff in PHP when you can do it in a database!
So any answer that tells you how to solve your issue within PHP in my eyes will be a wrong answer.

SELECT table_1.* 
   FROM table_1
   JOIN table_2 USING(fid)

Will return you all rows that exist in both table_1 as table_2

SELECT table_1.* 
   FROM table_1
   LEFT JOIN table_2 USING(fid)
WHERE table_2.fid IS NULL

Will return you all rows in table_1 that are not in table_2

And so on.

Use your database for doing data things.

score 0 · Answer 6 · answered Jul 07 '14 at 18:30

putting aside the sql part, use this function:

function compare_array($arr1,$arr2)
{
    foreach($element as $arr1)
    {    
        foreach($element2 as $arr2)
        {
            if($element2 == $element)
            {
               return true;
            }
            else
            {

            }
        }
 return false;
}

pass it your two arrays obtained with your queries and act upon its return it might be a bit heavy but if I understand well what you need it should work

score 0 · Answer 7 · edited Apr 26 '15 at 08:53

0

According to: SQL for Dummies, the INNER JOIN discards all rows from the result table that DO NOT have corresponding rows in both source tables.

Try:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

edited Apr 26 '15 at 08:53

halfer

18,701
13
79
158

answered Jul 08 '14 at 18:47

user3121354

1
2

How can I compare two arrays of data retrieve from database?

7 Answers7