help with mysql code

[Nothadoth]

Hey. I would like someone to tell me how to code this as I cannot figure it out. I've pretty much taken it down t to scratch as I can't get it to work. I am putting a shopping cart together where each product has 3 different options (eg. single, double, full kit). I have the following database tables (table --> fields)

 

tbl_product --> pd_id, pd_type, od_id

tbl_order_item --> pd_id, pd_price1, pd_price2, pd_price3

 

      - pd_id will match pd_id from the tbl_order_item

      - pd_type is defined as 1, 2, or 3 which will then (hopefully) assign onto "pd_price" . $type; to get the price

      - od_id is the current order id for the shopping cart and will match to $orderId which is already defined

 

I am trying to run a code to get the final shopping cart price to send to Paypal. Assume there are multiple products (ie. 2 x product id 72 where one is price_1 and one is price_2 (single and double both being ordered).

 

So i need it to basically....

 

SELECT pd_type FROM tbl_order_item WHERE od_id = $orderId

result = $type

 

then...

 

SELECT pd_price.$type FROM tbl_product WHERE pd_id = pd_id

 

 

 

Thats for each product in the cart. Then take the pd_price.$type value (eg. ?29.99) for each product and add them together to get a finalised price.

 

Here is what I have.... not much...

 

function getOrderAmount($orderId)
{

// Perform Query
$result = mysql_query("SELECT * FROM tbl_order_item
				  WHERE od_id = '$orderId'");
echo $orderId;
while ($row = mysql_fetch_assoc($result)) {

$type = $row['pd_type'];
$pricetype = "pd_price" . $type;
echo $pricetype;


}
return $orderAmount;


}

 

in the above code, assuming my order has two products of the same pd_id where one is pd_price1 and one is price_2, it echos the current orderid (tested and works) and then the following:

 

price_1price_2

 

So ive managed to get that far. Now i need it to select from the tbl_product table using $pricetype and get the relavant price, then add them all together as $orderAmount.

 

 

 

Sorry thats long. Is it possible?

[Nothadoth]

I tried changing it to this where pd_price1 was already pre-defined without using a variable for the number and then suddenly it stopped displaying two $pricetype (instead of pd_price1pd_price2 it just shows pd_price1) and just displayed: pd_price119.99. So it seams that once i put a second mysql query inside it and create a new WHILE loop inside it, it stops displaying the second result.

.

function getOrderAmount($orderId)
{

// Perform Query
$result = mysql_query("SELECT * FROM tbl_order_item
				  WHERE od_id = '$orderId'");
echo $orderId;
while ($row = mysql_fetch_assoc($result)) {
$pd_id = $row['pd_id'];
$type = $row['pd_type'];
$pricetype = "pd_price" . $type;
echo $pricetype;

$result = mysql_query("SELECT * FROM tbl_product
				  WHERE pd_id = '$pd_id'");
while ($row = mysql_fetch_assoc($result)) {
echo $row['pd_price1'];

}

}
return $orderAmount;


}

[g__ink]

In your second post - the reason you are only getting the first row (pd_price1) from your first query is that you are overriding the value of the $result variable when you run your second query.

 

// Perform Query
$result = mysql_query("SELECT * FROM tbl_order_item
				  WHERE od_id = '$orderId'");

//this overrides $result as it is within your first while loop
$result = mysql_query("SELECT * FROM tbl_product
				  WHERE pd_id = '$pd_id'");

[Nothadoth]

Thanks. I changed the names and it is showing both answers again.

 

How can i make it so that it shows $newrow['$pricetype']; (eg. $newrow['pd_price1'] or $newrow['pd_price2'] etc.)

 

and then I need to assign each to a variable and add them all together for a final price.

[Nothadoth]

I got it working. Thanks