| home / programming / carts / chap7 / 2 |    [previous] [next] |
|
|
|
Next, we need to update our inventory, so that we don't try to oversell our stock. We start by retrieving all the items in the user's cart:
$i_query = "SELECT item_type_id,product_or_event_code,quantity ";
$i_query .= "FROM carts WHERE session_id='$s_id'";
$i_result = mysql_query($i_query)
or email();Now we need to run an UPDATE query for each
item that was just paid for. Because the structures for storing albums and
tickets are different, we'll need to assemble this query on the fly, depending
upon the item type. If the item's an album, the query will look something
like this:
UPDATE product_codes
SET product_code_inventory = product_code_inventory - $i_quantity
WHERE product_code_id = $i_code;where $i_quantity
is the quantity ordered and $i_code is the item's product_code_id.
If the item is a ticket to a show, our query will be in the form:
UPDATE events
SET event_seats_sold = event_seats_sold + $i_quantity
WHERE event_id = $i_code;Of course, in this case, $i_code
stands in for event_id.
We can boil these two queries down to something like:
UPDATE $table SET $change WHERE $id = $i_code;where $table,
$change and $id take the place of those portions of the
two queries that we show in italics.
So, for each item in the user's cart, what we need to do in assembling the SQL is to determine if the item in question is an album or a ticket and substitute the corresponding values into the string making up the query that we actually want to send to MySQL:
while($i_row = mysql_fetch_assoc($i_result))
{
$i_type = $i_row["item_type_id"];
$i_code = $i_row["product_or_event_code"];
$i_quantity = $i_row["quantity"];If the item is an album, those values will be those relating to the
product_codes table:
if($i_type == 1)
{
$table = "product_codes";
$id = "product_code_id";
$change =
"product_code_inventory = product_code_inventory - $i_quantity";
}Otherwise, we need to interpolate the values relating to the events table:
else
{
$table = "events";
$id = "event_id";
$change = "event_seats_sold = event_seats_sold + $i_quantity";
}We then put together the complete query and submit it using the
mysql_query() function.
$u_query = "UPDATE $table SET $change WHERE $id = $i_code";
$u_result = mysql_query($u_query);
}
?>| home / programming / carts / chap7 / 2 | [previous] [next] |
Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Shopping | E-mail Offers | Freelance Jobs 
Created: December 26, 2002
Revised: December 26, 2002
URL: http://webreference.com/programming/carts/chap7/2/3.html
