Build an e-commerce application with PHP4 and PostgreSQL

This article demonstrates the application of PHP and PostgreSQL in e-commerce through a simple web application. Not long ago, building a serious Web application would mean buying expensive ColdFusion licenses and adding a commercial database service program like Sybase to a Sun server. Fortunately, such days are gone forever. With PostgreSQL e-commerce

This article demonstrates the application of PHP and PostgreSQL in e-commerce through a simple web application.

Not long ago, building a serious Web application would mean buying expensive Cold Fusion licenses and a commercial database service program like Sybase with Sun servers. Fortunately, such days are gone forever. With the growing maturity of the free database market and the increasing number of Apache users, some alternative products have already exceeded the capabilities of these proprietary software.

One of the better open source software is PHP, a script language similar to Perl, PostgreSQL, and a very powerful object-oriented database. If you combine the two, you can design a simple message book to a huge Web-based financial software. PHP provides the brain while IPVs provides developed muscles.

The following describes a very basic PHP shopping cart and inventory application, making full use of the S transaction function. Source code push source? Download PHPBuilder.com.

The first thing to mention is the structure of the application. in my PHP Web application, I always set up a comprehensive library, which is used on every page of the website and called common. php is stored in the include directory.

This database processes routine tasks, such as database connection, user identification, and site header/tail files. Put these functions in one place, and our application looks clean and easy to maintain.

Table 1: Demo library code

Common. php:

// Connect to the IPVs database
$ Conn = pg_pconnect ("user = tim dbname = db_example ");

// Check whether the connection is successful
If (! $ Conn ){
// Report an error if it fails
Echo pg_errormessage ($ conn );
Exit;
}

// Site header file

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 code

Function query ($ SQL ){
Global $ conn;
Return pg_exec ($ conn, $ SQL );
}

// Automatically enable or save the session status on each page

Session_start ();

?>

Therefore, our first version of the library is ready for use. it connects to the database and provides a simple HTML
Code.

Every page on our site includes:

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 logic from the actual representation (HTML here) when building an application. Therefore, I put the logic in the function. However, PHP uses the function call method. The disadvantage is that there is no standard error handling process. if a function has an internal error, the calling function program cannot pass the error information to the user. In other languages, such as Java, you can use try/catch statements for processing.

My solution is that each function always returns true or false and sets a $ feedback global variable. in this way, the result can be tested. There is now a project called PEAR (http://pear.php.net/) working on standardized error handling and database access efforts,
However, it cannot run stably until now.

The following is an example of using my true/false methods to call a function:

$ Result = function_call_name ();

If (! $ Result ){
// Display error
Echo $ feedback;
} Else {
// No error. continue
}

?>

Now let's start thinking about the shopping cart! We need some basic data structures to store shopping cart data. For example, we need an inventory database to list the item name, part number, price, and quantity. at the same time, we
It is too complicated to record the items purchased by the customer.

Table 2 shopping cart data structure

Cart. SQL:

# Create an order table to generate customer numbers.
# Separate each id with a random number to prevent others from guessing the shopping cart number.

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
);

This structure gives us a basic shopping cart. in order to standardize the database mode, I create an independent table to list the contents in the customer's shopping cart. In this way, the customer's shopping cart can have multiple items, and it is easy
Connect to the inventory database.

Now, we need to consider the effect of the moles on the moles of the moles. what are the differences between the numbers of the numbers? Item and quantity adjustment. This is done by yourself.

I started with a simple feature to generate a customer. All these are actually getting the information of the next customer from the customers in the queue and inserting the customer table, register the customer number in PHP4's built-in session management.

Table 3. create 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 _ mermer_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 with the session
Session_register ('customer _ id ');

// Insert a new customer
$ Res = query ("insert into mers (customer_id)
VALUES ('$ mermer_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, it demonstrates how to correctly start and end Postgres transactions and how to check the query statement errors. I want to use the same error monitoring program in all the code. I think you should do the same.

You need to plan the solution to query errors. are you directly terminating the program? Or re-run the query statement or continue to execute it. nothing happens? Carefully consider the results of each choice. For example, if you cannot get the customer_id of the next customer, the new customer record will be ruined. Next, you cannot update her address and add items to the shopping cart, right?

Now, let's take a look at the process of adding an item. This step is relatively easy. before adding an item, check whether the item is in the database. This is safer because the item number is from the browser and may be tampered. Once we know that an item exists, we can test whether it is already in the shopping cart. if it has already been put in, we can add one to the number, instead of inserting another row. Otherwise, we can insert a record with one quantity to the shopping cart.

Table 4. add an item 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 whether the item is placed in the shopping cart. If yes, increase the quantity.
// Start the 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 this item does not exist, insert a new entry
$ 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 best to roll back the transaction.
Query ("ROLLBACK ");
Return false;
} Else {
Query ("COMMIT ");
Return true;
}
} Else {
// This 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 {
// Submit the change to officially update the database.
Query ("COMMIT ");
Return true;
}
}
}
}

?>

Now we can create new customers and add items. Now we need to check out and reduce the inventory. This part is the most complex, making full use of IPVs's transaction functions and advanced lock mechanisms.

We start with the SELECT... for update syntax of IPVs. this statement can effectively lock the selected row so that you can UPDATE and commit changes in a transaction.

By using this statement in a transaction, you can ensure data consistency. In other databases, such as MySQL, the specified data row cannot be locked to obtain incorrect data and useless inventory statistics.

This statement can also use subqueries and the standard features of another database. Subqueries allow you to combine two queries easily,

After locking the row, we need to reduce the inventory according to the items in the shopping cart. For simplicity, we do not report errors for inventory insufficiency and change inventory to negative. You can write a management page to view items in the negative inventory and order them.

Finally, we updated the credit card and purchase information in the customer's table, total purchase amount, and removed the customer's session.

Table 5. checkout and inventory reduction

Function cart_checkout ($ credit_card, $ address, $ name ){
Global $ conn, $ customer_id, $ feedback;

// Start the transaction
Query ("begin work ");

// Lock the corresponding row of the database and table and use a simple subquery for processing.

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

// Several rows in stock have been locked, and items and quantity are obtained from the shopping cart.
$ 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 );

// Update the inventory balance

For ($ I = 0; $ I <$ rows; $ I ++ ){
// Read the shopping cart data
$ 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;
}
}
// The inventory update is complete. obtain the total amount of the order and update the 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 the customer table
$ Total = pg_result ($ res, 0, 0 );
$ Res = query ("update mers ".
"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 {
// The change takes effect.
Query ("COMMIT ");

// Delete the session
$ Customer_id = 0;
Session_destroy ();
Return true;
}
}
}
}
}

?>

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

If you do not use the transaction processing in this example, it will be too much trouble if the update fails. Maybe you only updated part of the inventory. 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 shopping cart (if so, as long as I have time, I can write a book). However, this article demonstrates the most basic design and operation methods, we recommend that you use each Web developer. For more in-depth discussions, visit PHPBuilder.com.

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

Note:
Author Tim Perdue (tim@perdue.net) is the builder of SourceForge.net, as well as PHPBuilder.com and
The founder of Geocrawler.com.