[RESOLVED] Combine SQL with PHP

crazylegseddie

Hi I originally posted this is the database forum but I think its more a PHP problem now. I recently had to seperate my two queries as the server that I need to use does not support the UNION function. I have been told to combine them but not sure how to. Can anyone please assist. Thank You

$sql = "SELECT SUM(pd_price * od_qty)
	        FROM tbl_order_item oi, tbl_product p 
		    WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId";

$result = dbQuery($sql);

$sql2 = "SELECT od_shipping_cost 
			FROM tbl_order
			WHERE od_id = $orderId";
$result = dbQuery($sql2);

Weedpacket

First of all, you'd need to use say, $result1 and $result2; otherwise the results from the second query will overwrite the results of the first.

And, depending on what dbQuery() returns, combine the two. If it returns an array of results, then array_merge or + would combine them. If they're result sets, then the easiest would be to copy their contents into arrays and combine those.

bogu

If u want to combine two arrays: array_merge
U can find more arrays functions here.

Or as a mysql combination could be something like this, dont know if is corect but u should get the idea ...

SELECT SUM(pd_price * od_qty) , od_shipping_cost
FROM tbl_order_item oi
LEFT JOIN tbl_product p USING(pd_id)
LEFT JOIN tbl_order o ON oi.od_id = o.od_id
WHERE oi.od_id = $orderId

crazylegseddie

thx for your iput will try a few things when I get home 🙂

crazylegseddie

Hi i tried the few things you suggested and still no luck: changing the $result variables as shown below still came up with 0 when processed:

function getOrderAmount($orderId)
{
	$orderAmount = 0;

$sql = "SELECT SUM(pd_price * od_qty) 
        FROM tbl_order_item oi, tbl_product p 
        WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId"; 

$result1 = dbQuery($sql); 

$sql2 = "SELECT od_shipping_cost 
            FROM tbl_order 
            WHERE od_id = $orderId"; 
$result2 = dbQuery($sql2); 

if (dbNumRows($result1) == 2) {
	$row = dbFetchRow($result1);
	$totalPurchase = $row[0];

	$row2 = dbFetchRow($result2);
	$shippingCost = $row2[0];

	$orderAmount = $totalPurchase + $shippingCost;
}	

return $orderAmount;	
}

?>

PS all dbQuery() returns is

function dbQuery($sql)
{
	$result = mysql_query($sql) or die(mysql_error());

return $result;
}

I also tried the query and no luck 'Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal'

Anything im doing wrong with the code? I am so lost and tired with this one and only because I cant use UNION with an order SQL version 😕

bogu

After u execute the query u need to grab the results:
mysql_fetch_assoc, mysql_fetch_array

crazylegseddie

thx for everyones replies but it was as simple as adding:

if (dbNumRows($result1) == 1 && dbNumRows($result2) == 1) {

because previously if (dbNumRows($result1) == 2) {
this means that id expect there to be 2 rows as a result of the UNION query. by separating the queries each should have at least 1 record.