business application using PostgreSQL’s transactions and PHP4.
Common.php
<?php
//connect to postgres database
$conn=pg_pconnect("user=tperdue dbname=linuxjournal");
//see if our connection was successful
if (!$conn) {
//connection failed - exit the page with an error
//you could also try to proceed without the
//database - it's up to you
echo pg_errormessage($conn);
exit;
}
//now let's set up a common site header
function site_header ($title) {
return '<HTML>
<HEAD>
<TITLE>'.$title.'</TITLE>
</HEAD>
<BODY>';
}
//common HTML to be output at the end of the page
function site_footer () {
return
'</BODY></HTML>';
}
//a simple wrapper to reduce the code needed
//for each postgres query
function query($sql) {
global $conn;
return pg_exec($conn,$sql);
}
//have PHP4 set up/restore your session state automagically
//on every page
session_start();
/*
create sequence seq_customer_id increment 26 start 1;
create table customers (
customer_number int not null default 0 primary key,
name text,
address text,
credit_card text,
total_order MONEY DEFAULT '$0.00'
);
create table cart_items (
cart_item serial,
customer_id int,
part_number int,
quantity int
);
create index idx_cart_customer on cart_items(customer_number);
create table item_inventory (
part_number serial,
name text,
price float,
inventory int
);
*/
function cart_new() {
//make the database connection handle available
global $conn,$customer_id,$feedback;
//start a transaction
query("BEGIN WORK");
//query postgres for the next value in our sequence
$res=query("SELECT nextval('seq_customer_id')");
//check for errors
if (!$res || pg_numrows($res)<1) {
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - Database didn't return next value ';
query("ROLLBACK");
return false;
} else {
//set that value in a local var
$customer_id=pg_result($res,0,0);
//register the id with PHP4
session_register('customer_id');
//insert the new customer row
$res=query("INSERT INTO customers (customer_id) VALUES ('$customer_id')");
//check for errors
if (!$res || pg_cmdtuples($res)<1) {
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - couldn't insert new customer row ';
query("ROLLBACK");
return false;
} else {
//commit this transaction
query("COMMIT");
return true;
}
}
}
function
cart_add_item($item_id,$quantity=1) {
global $customer_id,$feedback,$conn;
//no need to start a transaction, as only one query
//will be updating the database
//query postgres for the next value in our sequence
$res=query("SELECT * FROM item_inventory WHERE part_number='$item_id'");
//check for errors
if (!$res || pg_numrows($res)<1) {
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - item not found ';
return false;
} else {
//item was legit - see if already in cart. If so, increment quantity
//start a transaction so we can lock the rows if they are found
query("BEGIN WORK");
$res=query("SELECT * FROM cart_items ".
"WHERE part_number='$item_id' AND customer_id='$customer_id' FOR UPDATE");
//check for errors
if (!$res || pg_numrows($res)<1) {
//insert it into the cart
$res=query("INSERT INTO cart_items ".
"(customer_id,part_number,quantity) ".
"VALUES ($customer_id,$item_id,$quantity)");
//check for errors on insert
if (!$res || pg_cmdtuples($res) < 1) {
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - couldn't insert into cart ';
//nothing was changed but it's good form to cancel the transaction
query("ROLLBACK");
return false;
} else {
query("COMMIT");
return true;
}
} else {
//item already in cart - increment quantity
$res=query("UPDATE cart_items SET quantity = quantity + $quantity ".
"WHERE part_number='$item_id' AND customer_id='$customer_id'");
if (!$res || pg_cmdtuples($res) < 1) {
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - couldn't increment quantity in cart ';
//again nothing was changed
query("ROLLBACK");
return false;
} else {
//commit the updated quantity to the database
query("COMMIT");
return true;
}
}
}
}
function
cart_checkout($credit_card,$address,$name) {
global $conn,$customer_id,$feedback;
//start a transaction
query("BEGIN WORK");
/*
lock the appropriate rows in the item_inventory table,
based on what is in the visitor's cart
We'll do this with a simple subselect
and Postgres' SELECT ... FOR UPDATE syntax
*/
$sql="SELECT * FROM item_inventory ".
"WHERE part_number ".
"IN (SELECT part_number FROM cart_items ".
"WHERE customer_id='$customer_id') ".
"FOR UPDATE";
$res=query($sql);
//check for errors
if (!$res || pg_numrows($res)<1) {
//no items matched or db failed
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - no items locked ';
//terminate the transaction
query("END WORK");
return false;
} else {
/*
Inventory rows are now locked
Get the items and quantity from the cart
*/
$sql="SELECT part_number,quantity ".
"FROM cart_items ".
"WHERE customer_id='$customer_id' ".
"ORDER BY part_number DESC";
$res2=query($sql);
//check for errors
if (!$res2 || pg_numrows($res2)<1) {
//no items selected from cart
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - no items in cart ';
//terminate the transaction
query("END WORK");
return false;
} else {
$rows=pg_numrows($res2);
/*
Inventory is locked and we have the cart contents
Iterate and update the inventory balances
*/
for ($i=0; $i < $rows; $i++) {
//fetch this row from our cart
$quantity=pg_result($res2,$i,'quantity');
$item_id=pg_result($res2,$i,'part_number');
$res3=query("UPDATE item_inventory ".
"SET inventory = inventory-$quantity ".
"WHERE part_number='$item_id'");
//see if query failed or no rows were affected
if (!$res3 || pg_cmdtuples($res3) < 1) {
//couldn't update this row
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - updating inventory failed ';
//rollback the transaction
query("ROLLBACK");
return false;
}
}
/*
Inventory now fully updated
Finally - let's get the total amount
of this order and update the customer record
*/
$res=query("SELECT sum(cart_items.quantity*item_inventory.price) ".
"FROM cart_items,item_inventory ".
"WHERE cart_items.customer_id='$customer_id' ".
"AND cart_items.part_number=item_inventory.part_number");
//check for errors
if (!$res || pg_numrows($res) < 1) {
//couldn't get order total
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - couldn't get order total ';
//rollback the transaction
query("ROLLBACK");
return false;
} else {
/*
We've got the order total, now
we just update the customers table
*/
//set the sum as a local variable
$total=pg_result($res,0,0);
$res=query("UPDATE customers ".
"SET address='$address',name='$name',".
"total_order='$total',credit_card='$credit_card' ".
"WHERE customer_id='$customer_id'");
//the usual error check
if (!$res || pg_cmdtuples($res) < 1) {
//update failed or did not affect any rows
$feedback .= pg_errormessage($conn);
$feedback .= ' Error - updating customer information ';
//rollback the transaction
query("ROLLBACK");
return false;
} else {
/*
We made it!
Now commit the changes to the database
*/
//commit all changes
query("COMMIT");
//erase the PHP4 session
$customer_id=0;
session_destroy();
return true;
}
}
}
}
}
/*
//
// Test code
//
$res=cart_new();
echo $feedback;
echo site_header('Linux Journal');
echo '<P>'.$customer_id;
$res=cart_add_item(1);
echo $feedback;
$res=cart_add_item(2);
echo $feedback;
$res=cart_checkout('1234 5678 9102 3456','1257 Lakeside Drive, #4228 SunnyVale, CA','Tim Perdue');
echo $feedback;
echo site_footer();
*/
?>
Download: linuxjournal200009.zip
