Combining result set arrays?

NeoGeo

Is there a way to combine result set arrays in PHP?

something like this:

$query1 = "select * from products where product_type_id  = 1";
$result1 = mysql_query($query1);

$query2 = "select * from products where product_type_id = 2";
$result2 = mysql_query($query2);

$combined_result = $result1 . $result2;

$data = mysql_fetch_array($combined_result);

or perhaps something like this:

$query1 = "select * from products where product_type_id  = 1";
$result1 = mysql_query($query1);
$data1 = mysql_fetch_array($data1);

$query2 = "select * from products where product_type_id = 2";
$result2 = mysql_query($query2);
$data2 = mysql_fetch_array($data2);

$combined_data = $data1 . $data2;

Horizon88

You should be able to do

$query = "SELECT * FROM products WHERE product_type_id = 1 OR product_type_id = 2";
$result = mysql_query($query);
$data = mysql_fetch_array($result);

I don't think I've ever actually done it that way, but I've used AND in SQL queries, so I would assume OR is also supported.

Edit: Of course, that's on the SQL side of things - you could look into http://us.php.net/array_merge or similar functions for adding two php arrays together.

NeoGeo

Hi Horizon,

Yes that would work just fine, but my real query is actually quite a bit more complex than this, I just simplified so I could explain the real problem i'm having without going into the specific details of my script.

I also tried the array_merge function and it does not work with result set arrays... I think they must be different than normal arrays somehow...

Neogeo

NeoGeo

Actually, I had forgotten exactly what I tried, i looked back at my code and this is what I found out:

if I run this code:

$query1 = "select * from products where product_type_id  = 1";
$result1 = mysql_query($query1);
$data1 = mysql_fetch_array($data1);

$query2 = "select * from products where product_type_id = 2";
$result2 = mysql_query($query2);
$data2 = mysql_fetch_array($data2);

$combined_data = array_merge($data1, $data2);

Then it screws up the array $combined_data because it overwrites column values where a duplicate name exists, which is what you get in result sets.

So really I need to be able to use the mysql_fetch_array($combined_RESULT_array) for this to work for me at all.

laserlight

Why not explain your more complicated query and see if it can be combined as in Horizon88's suggestion?

Other than that, it looks like you are merging the wrong arrays. Instead of merging the rows from the first result set with the rows from the second result set, you are merging the first row of the first result set with the first row of the second result set.

If you do not go for Horizon88's suggestion (which would be better), then you want something along these lines instead:

$query1 = "select * from products where product_type_id  = 1";
$result1 = mysql_query($query1);
$data1 = array();
while ($row = mysql_fetch_assoc($result1)) {
    $data1[] = $row;
}

$query2 = "select * from products where product_type_id = 2";
$result2 = mysql_query($query2); 
$data2 = array();
while ($row = mysql_fetch_assoc($result2)) {
    $data2[] = $row;
}

$combined_data = array_merge($data1, $data2);