Problem with mysql getting prices from table

[Nothadoth]

Hey guys,

 

I have two tables set up - tbl_order_item and tbl_product. I have session id's set and product id's to link the two tables. Each product has pd_price1, pd_price2 and pd_price3. The tbl_order_item has a pd_type field (1, 2, or 3) which is working and is filled with the relevant number when the user has completed the checkout.

 

I am trying to get the final price and send it to Paypal. So it uses the session id to find the right rows in tbl_order_item and give my the pd_type and then use that variable to find the correct price from tbl_product.

 

Here is my code:

 

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

$sql = "SELECT pd_type
        FROM tbl_order_item 
        WHERE od_id = $orderId
		";
$result = mysql_query($sql);
while($row = dbFetchRow($result)) {
	$type = $row['pd_type'];

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

		UNION

		SELECT od_shipping_cost
		FROM tbl_order
		WHERE od_id = $orderId";

		$result = dbQuery($sql);
if (dbNumRows($result) == 2) {
	$row = dbFetchRow($result);
	$totalPurchase = $row[0];

	$row = dbFetchRow($result);
	$shippingCost = $row[0];

	$orderAmount = $totalPurchase + $shippingCost;
}	

return $orderAmount;	
}


}

 

This returns this error:

Notice: Undefined index: pd_type in /home/wwwdist/public_html/elite/library/checkout-functions.php on line 88

Unknown column 'pd_price' in 'field list'

 

I dont understand why it isnt getting pd_type from the database table and putting it at the end of pd_price. Probably something stupid here. But ive been messing with code all day and my mind is just boggled now.

 

Thanks

[Pikachu2000]

print_r($row) and see what's in it. I'll bet it isn't indexed like you think it is.

[Nothadoth]

I cant really print anything as it is a function done on a page that auto redirects to paypal for the checkout.

 

Unless i script a test page.

 

What do you mean you bet it isn't indexed?

[Nothadoth]

I put in the code you gave me it returned this:

 

Array ( [0] => 60 [1] => 2 [2] => 1100 [3] => 30 [4] => 1 )

[Pikachu2000]

It's an enumerated array, not an associative array.

[Nothadoth]

You've lost me.

 

I want it to return a number anyway (either 1, 2, or 3) which is will attach to the next sql query as SELECT pd_price$pd_type

[Nothadoth]

As a test, I echo'd the orderId variable and it came up as 1106. I checked the database and that row did exist with that id. the pd_type field was assigned to '2'. Yet in this code it is trying to retrieve this field and fails.........

 

Im completely lost.

[denno020]

Any chance you can post the SQL code so I can build the database on my test server and try to replicate the error?