Build an e-commerce application with PHP4 and PostgreSQL _php

PostgreSQL e-commerce

This paper demonstrates the application of PHP and Postgressql in e-commerce through a simple Web application.

Not long ago, if you were to architect a serious web application, it would mean buying a costly cold Fusion license, as well as a commercial database service program like Sybase plus Sun server. Fortunately, such a day is gone. With the growing number of free databases in the marketplace and the growth of Apache users, some of the alternatives have been quite, even beyond the capabilities of these proprietary software.

One of the better open source software is PHP, a scripting language like Perl, and PostgreSQL, a very powerful object-oriented database. If you combine the two, you can design from a simple guestbook to a huge web-based financial software. PHP provides brains while postgres provides developed muscles.

Here's a very basic PHP shopping cart and inventory app that takes full advantage of Postgres's transactional capabilities. SOURCE Push Lu Liling Persimmon gorge source? phpbuilder.com download.

The first thing to mention is the structure of the application, in my php Web application, I always first set up a comprehensive library, every page of the site will use it, named common.php stored in the Include directory.

This library handles daily tasks such as database connections, user identification, site header/trailer files, and so on. Putting these functions in one place, our application looks very clean and easy to maintain.

Table one: Sample library code

common.php:

Connecting the Postgres Database
$conn =pg_pconnect ("User=tim dbname=db_example");

See if the connection is successful
if (! $conn) {
Report an error if it fails
echo Pg_errormessage ($conn);
Exit
}

The header file for the site

function Site_header ($title) {
Return '

'. $title. '

';
}

HTML code at the end of the page

function Site_footer () {

Return ';

}

A simple query execution function to reduce the code

function query ($sql) {
Global $conn;
Return Pg_exec ($conn, $sql);
}

Let each page automatically start session or save session status

Session_Start ();

?>

So our first version of the library is already available, it connects to the database and provides a simple HTML
Code.

Each page on our site is comprised of:

Require ($DOCUMENT _root. ' /include/common.php ');

Echo Site_header (' demo page ');

/*
Page logic Processing
*/

Echo Site_footer ();

?>

Generally speaking, it is wise to separate the logical and actual representations (HTML in US) when building the application. So, I put logic in the function. However, PHP uses the method of function calls, the disadvantage is that there is no standard error handling process, if there is a mistake inside the function, call function program can not pass the error message to the user. In other languages, such as Java, you can use the Try/catch statement to handle it.

My solution is that each function always returns TRUE or false, setting a $feedback global variable so that the result can be tested. Now there is a project called pear (http://pear.php.net/) that is doing standardized error handling and database access efforts,
But so far, it has not been able to run stably.

Here is an example of calling a function using my True/false method:

$result =function_call_name ();

if (! $result) {
Display Error
Echo $feedback;
} else {
No errors, continue
}

?>

OK, now let's start thinking about the shopping cart! We need some basic data structure to store the shopping cart. For example, we need an inventory database to list the item name, part number, price, and quantity, and we
Also need to record customer purchases of items ... It's too complicated, just write this.

Table II, shopping CART data structure

Cart.sql:

# Create a sequential table to generate customer numbers.
# Separate each ID with a random number in case someone guesses the cart number.

Create sequence seq_customer_id increment-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
);

This structure gives us a basic shopping cart, in order to standardize the database schema, I create a separate table for listing the contents of the customer's shopping cart. This allows the customer's shopping cart to have multiple items and can be easily
To the inventory database connection.

Now we need to consider Huan bass well versed in phlegm steamer mother Quiff Δ Ji R Huan Horseshoe 罨耷 mirror tomb δ parsley fade overseas) irresolute He Yi-fines-road, the relief of the 5 than gray-Huan calender guide What the Zhang brain is familiar with phlegm earwigs scale wares beak δ Haiyun happy? Items, adjusted quantity, etc. That's all for you to finish.

I start with a simple function of generating a customer, all of which is in line with the customer to get the next customer information, insert the Customer table, the customer number in the PHP4 built-in session management register.

Table III, the establishment of a new customer

function Cart_new () {

Global $conn, $customer _id, $feedback;

Start a transaction
Query ("BEGIN work");

Query the next customer number
$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 {
$customer _id=pg_result ($res, 0,0);

Register to session
Session_register (' customer_id ');

Insert New Customer
$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;
}
}
}

?>

This code is long, although I don't like it very much, but it shows how to properly start and end postgres transactions and how to check query statements for errors. I want to use the same error monitoring program in all the code, and I think you should too.

Need to plan well if the query error handling method, you are directly terminate the program? Or do you rerun the query statement, or continue execution, as if nothing happened? Carefully consider the results of each choice. For example, if you can't get the next customer's customer_id, then the record of creating a new customer will be ruined, and then you can't update her address and add items to your shopping cart, right?

Now, let's look at the process of adding items, which is relatively easy, and check that the items are in the database before you add them. This is more secure because the item number is from the browser and may be tampered with. Once the item is known to exist, we can test if it is already in the cart, if it is already in, then add one instead of inserting a row, or insert a record of one into the cart.

Table IV, add items to the shopping cart

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 {
Check if the item is placed in the shopping cart, if yes, increase the quantity
Start a transaction
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) {

If you do not have the item, insert a new one
$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 ';
Although nothing is changed, it is better to roll back the transaction.
Query ("ROLLBACK");
return false;
} else {
Query ("COMMIT");
return true;
}
} else {
The item already exists in the shopping cart
$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 {
Commit changes and formally update the database.
Query ("COMMIT");
return true;
}
}
}
}

?>

Now we can build new customers, and they add items. We need to checkout now and lose the inventory. This section is the most complex, taking full advantage of Postgres's transactional capabilities and advanced locking mechanisms.

We use Postgres's SELECT ... For update syntax to start, this statement effectively locks the currently selected row so that you can update and commit changes in a transaction.

By using this statement in a transaction, you can guarantee the consistency of the data. In some other databases, such as MySQL, you cannot lock a specified row of data and get incorrect data and useless inventory statistics.

This statement can also take advantage of the subquery, another standard feature of the database. Subqueries can make it easy for you to combine two of queries

After locking the line, we need to reduce the corresponding inventory amount according to the items in the shopping cart. For the sake of brevity, we do not report errors on inventory and turn inventory into negative. You can write a management page yourself, view negative inventory items, and go to order.

Finally, we update the credit card in the customer form, purchase the information, sum up the purchase amount, and remove the customer's session.

Table Five, checkout, reduce inventory

function Cart_checkout ($credit _card, $address, $name) {
Global $conn, $customer _id, $feedback;

Transaction start
Query ("BEGIN work");

Locks the corresponding row of the inventory table and processes it with a simple subquery.

$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 {

A few lines of inventory have been locked, and items are taken from the shopping cart and quantity.
$sql = "Select Part_number,quantity".
"From Cart_items".
The WHERE
Customer_id= ' $customer _id ' ".
"ORDER by Part_number DESC";
$res 2=query ($sql);

if (! $res 2 | | pg_numrows ($res 2) <1) {
$feedback. = Pg_errormessage ($conn);
$feedback. = ' error-no items in cart ';
Query ("END work");
return false;
} else {
$rows =pg_numrows ($res 2);

Update inventory Balances

for ($i =0; $i < $rows; $i + +) {
Reading Shopping Cart Data
$quantity =pg_result ($res 2, $i, ' quantity ');
$item _id=pg_result ($res 2, $i, ' part_number ');

$res 3=query ("UPDATE item_inventory".
"SET inventory =inventory-$quantity".
"WHERE part_number= ' $item _id '");

if (! $res 3 | | pg_cmdtuples ($res 3) < 1) {
$feedback. = Pg_errormessage ($conn);
$feedback. = ' error-updating inventory failed ';
Query ("ROLLBACK");
return false;
}
}
End of inventory update, get the total amount of this order and update customer records
$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 {
Update Customer table
$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 {
Change is formally in force
Query ("COMMIT");

Delete session
$customer _id=0;
Session_destroy ();
return true;
}
}
}
}
}

?>

Theoretically, this is a very complex transaction, and each step must be executed correctly, otherwise the entire transaction must be rolled back to the correct order.

If you do not use this example of transaction processing, then in case of failure in the update process, the trouble is big. Maybe you just updated the part of the inventory and if visitors refresh the page, how do you know that inventory needs to be reduced?

This article does not want to provide a comprehensive solution for a shopping cart (if so, as long as I have the time to write a book completely), however, this article demonstrates the most basic design and running methods, recommended for each web developer to use. More in-depth discussions can be accessed phpbuilder.com.

All the code in this article can be downloaded from http://www.phpbuilder.com/columns/linuxjournal200009.php3.

Translator Note:
Author Tim Perdue (tim@perdue.net) is the builder of SourceForge.net, as well as phpbuilder.com and
Founder of the geocrawler.com.

