Linux Systems Administrator
Jupitermedia
US-AZ-Tucson

Justtechjobs.com Post A Job | Post A Resume

Linux Journal: Web Applications With PHP And PostgreSQL
Tim Perdue picture of Time Perdue
Here is the code from my Linux Journal article on how to build a business application using PostgreSQL's transactions and PHP4.
Download Code

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

*/

?>


Comments:
Transactions/lock and PHPmathrant06/02/07 12:56
concurrency?naveed 11/26/05 18:18
I am also pondugulaSirish Pondugula01/22/05 12:44
RE: postgresql + phpRodrigo Castellanos05/16/04 16:59
Frames in PhpJustin Sukumar09/26/03 05:20
RE: postgresql + phpAlexandru Botezatu12/10/02 03:40
RE: Transaction BEGIN/ENDAlexandru Botezatu12/10/02 03:29
Hit Counters Nick Chetty10/04/02 06:00
RE: error during copying .txt filequinkin05/13/02 23:46
RE: Imagesquinkin05/13/02 23:44
RE: Store Procedure in PostGreSQLAlban Médici04/17/02 10:25
session_set_save_handler for postgresRick04/13/02 13:49
error during copying .txt fileTulasi03/19/02 05:45
Transaction BEGIN/ENDRalf03/01/02 04:06
IT'S WRONG !RIcardo02/23/02 21:49
Store Procedure in PostGreSQLcaheo02/05/02 04:29
postgresql + phprakesh01/28/02 21:43
RE: converting MSAccess to PostgresqlLeandro M. Araujo01/24/02 14:46
NeTraMet and NeMaC values into postgresql reuben franklin rajkumar11/12/01 04:04
refreshing pages and duplicatesBraxton Beyer10/24/01 17:56
i need helpLuchooo09/17/01 13:30
RE: MSSQL to POSTGRESQLwclardy09/09/01 01:34
Imagesnash09/05/01 10:29
RE: converting MSAccess to PostgresqlAruel08/27/01 08:36
acessing Msacess data into postgresqlphani08/13/01 04:59
Postgres / PHP / ERPBharat Pondugula07/31/01 22:51
Installation HELPWalter Chu07/19/01 23:19
MSSQL to POSTGRESQLjeevan07/17/01 05:24
converting MSAccess to Postgresqlvenkat06/23/01 05:46
Apllying few design patternsexample05/24/01 07:27
RE: concurrency?Jonas Bengtsson03/26/01 11:26
concurrency?Bojan02/28/01 18:32
RE: LJ ArticleAlain Fontaine02/17/01 03:11
RE: stored proceduresmark Whiteley02/15/01 08:27
LJ ArticleArthur Watts12/20/00 20:20
"customer_number"??Calvin Dodge11/30/00 23:23
 

If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly.

Add A Comment:

Name:

Email:

Subject:

Message:

To reduce spam posts, messages are now manually approved

You are not [logged in]. That means your account will not get credit for this post.