Page 1 of 1
Getting the common values from 5 SQL queries?
Posted: Wed Aug 20, 2008 7:04 am
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
Re: Getting the common values from 5 SQL queries?
Posted: Wed Aug 20, 2008 8:01 am
by dml
Re: Getting the common values from 5 SQL queries?
Posted: Wed Aug 20, 2008 8:09 am
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.

Re: Getting the common values from 5 SQL queries?
Posted: Thu Aug 21, 2008 2:33 am
by Cronos
Corvin wrote:
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.
Re: Getting the common values from 5 SQL queries?
Posted: Fri Aug 22, 2008 5:30 am
by Cronos
Anyone?
Re: Getting the common values from 5 SQL queries?
Posted: Fri Aug 22, 2008 6:22 am
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().
Re: Getting the common values from 5 SQL queries?
Posted: Mon Aug 25, 2008 3:05 am
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...
Re: Getting the common values from 5 SQL queries?
Posted: Mon Aug 25, 2008 8:50 am
by Bill H
if such a thing as (condition) ? phrase1 : phrase2 exists in PHP...
it does.
Re: Getting the common values from 5 SQL queries?
Posted: Tue Aug 26, 2008 4:28 am
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?