<?php
//连接 postgres 数据库
$conn=pg_pconnect(\"user=tim dbname=db_example\");
//看连接是否成功
if (!$conn) {
//如果失败则报告出错
echo pg_errormessage($conn);
exit;
}
//站点的头文件
function site_header ($title) {
return \'<HTML>
<HEAD>
<TITLE>\'.$title.\'</TITLE>
</HEAD>
<BODY>\';
}
// 页面结尾的 HTML 代码
function site_footer () {
return \'</BODY></HTML>\';
}
//一个简单的查询执行函数，用来减少代码
function query($sql) {
global $conn;
return pg_exec($conn,$sql);
}
//让每一个页面自动启动session或者保存 session 状态
session_start();
?>
因此，我们的第一个版本的库已经可以用了，它连接数据库，提供了简单的 HTML
代码。
我们站点上每一个页面都包括：
<?php<n>
require ($DOCUMENT_ROOT.\'/include/common.php\');
echo site_header(\'示范页面\');
/*
页面逻辑处理
*/
echo site_footer();
?>
create sequence seq_customer_id increment 26 start 1;
create table customers (
customer_id int not null default 0 primary key,
name text,
address text,
credit_card text,
total_order MONEY DEFAULT \'{CONTENT}.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_id);
create table item_inventory (
part_number serial,
name text,
price float,
inventory int
);
<?php
function cart_new() {
global $conn, $customer_id, $feedback;
// 启动一个事务
query(\"BEGIN WORK\");
//查询下一个顾客号码
$res=query(\"SELECT nextval(\'seq_customer_id\')\");
//检查错误
if (!$res || pg_numrows($res)<1) {
$feedback .= pg_errormessage($conn);
$feedback .= \' Error - Database didn\'t return next value \';
query(\"ROLLBACK\");
return false;
} else {
$customer_id=pg_result($res,0,0);
// 登记到 session
session_register(\'customer_id\');
// 插入新顾客
$res=query(\"INSERT INTO customers (customer_id)
VALUES (\'$customer_id\')\");
//检查错误
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;
}
}
}
?>
<?php
function cart_add_item($item_id,$quantity=1) {
global $customer_id, $feedback, $conn;
$res=query(\"SELECT * FROM item_inventory WHERE part_number=\'$item_id\'\");
if (!$res || pg_numrows($res)<1) {
$feedback .= pg_errormessage($conn);
$feedback .= \' Error-item not found \';
return false;
} else {
// 检查物品是否放入购物车，如果是，增加数量
// 开始事务
query(\"BEGIN WORK\");
$res=query(\"SELECT * FROM cart_items \".
\"WHERE part_number=\'$item_id\' AND customer_id=\'$customer_id\' FOR UPDATE\");
if (!$res || pg_numrows($res)<1) {
//如果没有该物品，新插入一条
$res=query(\"INSERT INTO cart_items \".
\"(customer_id,part_number,quantity)\".
\"VALUES ($customer_id,$item_id,$quantity)\");
if (!$res || pg_cmdtuples($res) < 1) {
$feedback .= pg_errormessage($conn);
$feedback .= \' Error-couldn\'t insert into cart \';
//尽管没有东西被改变，但是最好还是回滚事务
query(\"ROLLBACK\");
return false;
} else {
query(\"COMMIT\");
return true;
}
} else {
//购物车中已经存在该物品
$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 \';
query(\"ROLLBACK\");
return false;
} else {
// 提交改变，正式更新数据库。
query(\"COMMIT\");
return true;
}
}
}
}
?>
<?php
function cart_checkout($credit_card,$address,$name) {
global $conn, $customer_id, $feedback;
// 事务开始
query(\"BEGIN WORK\");
// 锁住库存表的对应行，用一个简单的子查询来处理。
$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);
if (!$res || pg_numrows($res)<1) {
$feedback .= pg_errormessage($conn);
$feedback .= \' Error - no items locked \';
query(\"END WORK\");
return false;
} else {
// 库存的某几行已被锁定，从购物车取得物品以及数量。
$sql=\"SELECT part_number,quantity \".
\"FROM cart_items \".
\"WHERE
customer_id=\'$customer_id\' \".
\"ORDER BY part_number DESC\";
$res2=query($sql);
if (!$res2 || pg_numrows($res2)<1) {
$feedback .= pg_errormessage($conn);
$feedback .= \' Error - no items in cart \';
query(\"END WORK\");
return false;
} else {
$rows=pg_numrows($res2);
// 更新库存余额
for ($i=0; $i < $rows; $i++) {
// 读取购物车数据
$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\'\");
if (!$res3 || pg_cmdtuples($res3) < 1) {
$feedback .= pg_errormessage($conn);
$feedback .= \' Error - updating inventory failed \';
query(\"ROLLBACK\");
return false;
}
}
// 库存更新结束，得到这个订单的合计金额并更新顾客记录
$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\");
if (!$res || pg_numrows($res) < 1) {
//couldn\'t get order total
$feedback .= pg_errormessage($conn);
$feedback .= \' Error - couldn\'t get order total \';
query(\"ROLLBACK\");
return false;
} else {
// 更新顾客表
$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\'\");
if (!$res || pg_cmdtuples($res) < 1) {
$feedback .= pg_errormessage($conn);
$feedback .= \' Error - updating customer information \';
query(\"ROLLBACK\");
return false;
} else {
// 改变正式生效
query(\"COMMIT\");
// 删除 session
$customer_id=0;
session_destroy();
return true;
}
}
}
}
}
?>