Getting the common values from 5 SQL queries?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Cronos
Forum Newbie
Posts: 5
Joined: Wed Aug 20, 2008 6:14 am

Getting the common values from 5 SQL queries?

Post by Cronos »

I've tried this on other forums without success.

I have up to 5 SQL queries, giving up to 5 results (stored in arrays).
I need to find the values that are present in every result (that is, the common values for up to all 5 results).

Help with only the logics/pseudo code is ok.

example:

Code: Select all

 
1:{1,2,3,4,18}
2:{2,6,7,8}
3:{2,7,8}
4:{NULL} 
5:{2,39,80}
 
The only value present in every array that have a value is: 2
 
dml
Forum Contributor
Posts: 133
Joined: Sat Jan 26, 2008 2:20 pm

Re: Getting the common values from 5 SQL queries?

Post by dml »

How about array_intersect?
Corvin
Forum Commoner
Posts: 49
Joined: Sun Dec 03, 2006 1:04 pm

Re: Getting the common values from 5 SQL queries?

Post by Corvin »

Code: Select all

<?php
// ...
// SQL Query
// ...
$row1 = array( "value1" => 1, "value2" => 2, "value3" => 3, "value4" => 4, "value5" => 18 );
$row2 = array( "value1" => 2, "value2" => 6, "value3" => 7, "value4" => 8 );
$row3 = array( "value1" => 2, "value2" => 7, "value3" => 8 );
$row4 = array( );
$row5 = array( "value1" => 2, "value2" => 39, "value3" => 80 );
 
// Check how many arrays there are which are not empty 
// and create an array which contains all numbers
$numbers = array();
$nonEmptyArrays = 0;
 
for( $i = 1; $i <= 5; $i++ ) {
    $array_name = "row" .$i;
 
    if( count($$array_name) > 0 ) {
        $nonEmptyArrays++;
    }
 
    // Add numbers
    foreach( $$array_name as $value ) {
        $numbers[] = $value;
    }
}
 
// Check how often each number occurs
$o = 0;
for( $z = 0; $z < count($numbers); $z++ ) {
    // Search in all 5 arrays
    for( $u = 1; $u <= 5; $u++ ) {    
        $array_name = "row" .$u;
        if( in_array($numbers[$z], $$array_name) ) $o++;
    }
 
    if( $o == $nonEmptyArrays ) {
        echo $numbers[$z] ." is in every array";
        exit;
    } else {
        $o = 0;
    }
}
?>
Maybe this is not the best solution, but it works and should help you. :)
Cronos
Forum Newbie
Posts: 5
Joined: Wed Aug 20, 2008 6:14 am

Re: Getting the common values from 5 SQL queries?

Post by Cronos »

Corvin wrote:

Code: Select all

<?php
...
?>
Maybe this is not the best solution, but it works and should help you. :)
Thanks a lot for your effort. I could be used, but I think it's a little bit messy, and I'd rather use array_intersect.
I did use that function myself, but my logics was flawed, and it didn't work.
I think I'm looking for an elegant solution with array_intersect.
Cronos
Forum Newbie
Posts: 5
Joined: Wed Aug 20, 2008 6:14 am

Re: Getting the common values from 5 SQL queries?

Post by Cronos »

Anyone?
Corvin
Forum Commoner
Posts: 49
Joined: Sun Dec 03, 2006 1:04 pm

Re: Getting the common values from 5 SQL queries?

Post by Corvin »

Code: Select all

array_intersect($row1, $row2, $row3, $row4, $row5);
is not possible because a row can be empty.

This could be a solution:

Code: Select all

<?php
// ...
// SQL Query
// ...
$row1 = array( "value1" => 1, "value2" => 2, "value3" => 3, "value4" => 4, "value5" => 18 );
$row2 = array( "value1" => 2, "value2" => 6, "value3" => 7, "value4" => 8 );
$row3 = array( "value1" => 2, "value2" => 7, "value3" => 8 );
$row4 = false;
$row5 = array( "value1" => 2, "value2" => 39, "value3" => 80 );
 
// Create list of non empty arrays
$list = array();
 
for( $i = 1; $i <= 5; $i++ ) {
    $array_name = "row" .$i;
 
    if( $$array_name != false ) {
        $list[] = '$' .$array_name;
    }
}
 
$list = implode(", ", $list);
 
// Generate code
$code = '$intersection = array_intersect('.$list.');';
eval($code);
 
// Print number
foreach( $intersection as $value ) echo $value;
?>
It's less code than my previously posted solution but it may be slower because of eval().
Cronos
Forum Newbie
Posts: 5
Joined: Wed Aug 20, 2008 6:14 am

Re: Getting the common values from 5 SQL queries?

Post by Cronos »

Thanks. You're definitely onto something here.

I'm just wondering if there could be a more elegant solution?
Is it possible to do this on-the-fly?
Something like:

Code: Select all

$finalResult = array_intersect((!isempty($row1)) ? $row1:NULL, (!isempty($row2)) ? $row2:NULL, etc...  );
if such a thing as (condition) ? phrase1 : phrase2 exists in PHP...
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Getting the common values from 5 SQL queries?

Post by Bill H »

if such a thing as (condition) ? phrase1 : phrase2 exists in PHP...
it does.
Cronos
Forum Newbie
Posts: 5
Joined: Wed Aug 20, 2008 6:14 am

Re: Getting the common values from 5 SQL queries?

Post by Cronos »

Bill H wrote:
if such a thing as (condition) ? phrase1 : phrase2 exists in PHP...
it does.
Ok, thanks.

Then I have the following possible solutions:
Possible solution 1:

Code: Select all

$finalResult = array_intersect((!isempty($row1)) ? $row1:NULL, (!isempty($row2)) ? $row2:NULL, etc...  );
I will get trouble with NULL, because intersecting with NULL breaks the intersecting (gives NULL as a result, I guess). But maybe something similar?

Possible solution 2:
I'm able to determine earlier in my code which arrays are set and not: Can I then add the arrays to $arraysWithVaules[], and then pass this array of arrays to array_intersect() somehow?
Post Reply