Building an e-business application with PHP4 and PostgreSQL

Source: Internet
Author: User
Tags commit error handling header include insert pear php and postgres database
E-commerce This paper demonstrates the application of PHP and Postgressql in e-commerce through a simple Web application.

Not long ago, if a serious web application was to be structured, it meant buying expensive cold Fusion licenses and a commercial database service program like Sybase plus a sun server. Luckily, the days are gone. With the growing market for free databases and the massive growth of Apache users, some alternatives already have the same capabilities, even more than those of proprietary software.

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

Here's a very basic PHP shopping cart and inventory application that leverages Postgres's transactional capabilities. Source code push LU liling  Persimmon Gorge shoot 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 day-to-day tasks such as database connections, user identification, site header/tail files, and so on. Put these functions in one place, our applications look very clean and easy to maintain.

Table One: Demo library code

common.php:
<?php

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

See if the connection was successful
if (! $conn) {
Error reported if failed
echo Pg_errormessage ($conn);
Exit
}

Header file for site

function Site_header ($title) {
Return ' <HTML>
<HEAD>
<TITLE> '. $title. ' </TITLE>
</HEAD>
<BODY> ';
}

HTML code at the end of the page

function Site_footer () {

Return ' </BODY></HTML> ';

}

A simple query execution function to reduce the code

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

Let each page start session automatically or save session state

Session_Start ();

?>


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

Every page on our site includes:

<?php<n>

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

Echo Site_header (' demo page ');

/*
Page logic Processing
*/

Echo Site_footer ();

?>

In general, it's wise to separate logical and actual representations (HTML here) when building applications. So I put the logic inside the function. But PHP uses the method of function calls, the disadvantage is that there is no standard error handling process, if the function inside the error, call function program can not pass the wrong message to the user. In other languages, such as Java, you can use Try/catch statements to handle them.

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's a project called Pear (http://pear.php.net/) that does the work of standardizing error handling and database access,
But so far, it has not been able to run stably.

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

<?php

$result =function_call_name ();

if (! $result) {
Display Error
Echo $feedback;
} else {
No mistakes, go on
}

?>

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 listing item names, part numbers, prices, and quantities, while we
You also need to record what the customer buys ... It's too complicated, just write this.

Table II, shopping CART data structure

Cart.sql:

# Create a sequential table to generate customer numbers.
# Each ID is separated by a random number to prevent others from guessing the shopping 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 model, I create a separate table for listing the contents of the customer's shopping cart. This allows customers to have multiple items in their shopping cart and can easily
To connect to the inventory database.

Now we need to consider. Huan Bass is familiar with the sputum of the mother quiff stop δ Ji-foment  mirror tomb δ-the irresolute He Yi  nga Zhang  slow earwigs Ridge 5 than Ash Huan 鍪 guide The 鞯 brain familiar with phlegm happy  the ping  du beak δ fascine  皲? items, adjusted quantity, etc. It's up to you to do it yourself.

I start with a simple feature that generates a customer, all of which is actually getting the next customer's information in the queue, inserting the Customer table, and registering the customer number in the PHP4 built-in session management.

Table Iii. Creating a new customer
<?php

function Cart_new () {

Global $conn, $customer _id, $feedback;

Start a transaction
Query ("BEGIN WORK");

Query 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 is a long code, though I don't like it, but it shows how to start and end Postgres transactions correctly and how to check for errors in query statements. I'm going to use the same bug-monitoring program in all the code, and I think you should do the same.

Need to plan if the query error processing method, you are directly terminate the program? Or do you rerun the query statement, or do you continue, just as 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 setting up a new customer is gone, 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 before adding items, check to see if the items are in the database. This is safer because the item number is from the browser and may be tampered with. Once we know that the item exists, we can test whether it is already in the shopping cart, if it has been put in, then add one to the number instead of inserting a different line, otherwise, insert a record number into the cart.

Table four, add items to the shopping cart
<?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 {
Check whether the item is 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 don't 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 best 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 {
Submit changes and formally update the database.
Query ("COMMIT");
return true;
}
}
}
}

?>


Now we can build new customers, and they add items. We need to pay the bill now and lose the stock. This part is the most complex, taking full advantage of the Postgres transaction function and advanced lock mechanism.

We use the Postgres SELECT ... For update syntax as a start, this statement effectively locks the currently selected row so that you can update and submit changes in a single transaction.

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

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

After locking the row, we need to reduce the corresponding inventory according to the shopping cart items. For the sake of simplicity, we don't report errors on inventory and turn inventory into negative numbers. You can write a management page of your own, view negative inventory items, and go to order.

Finally, we update the Customer table credit card, purchase information, total purchase amount, remove this customer session.

Table v. Checkout, reduce inventory
<?php

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

Transaction start
Query ("BEGIN WORK");

Lock the corresponding line of the inventory table and use a simple subquery to handle it.

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

Some of the lines in the inventory are locked, and items are taken from the shopping cart as well as the quantity.
$sql = "Select Part_number,quantity".
"From Cart_items".
"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 Balance

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;
}
}
Inventory update complete, 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");

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 officially in effect
Query ("COMMIT");

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

?>



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.