URL: http://www.uburst.com/cgi-bin/dcforum/dcboard.cgi
Forum: ushop_database_ideas
Thread Number: 7
[ Go back to previous page ]

Original Message
"Accessing MySQL from Perl"

Posted by Brian Levine [blevine@cfl.rr.com] on at 01:12 PM
Okay, I'm ready to tackle the next step in my uShop-Database Interface. What I want is to actually make the inventory quantities truly live, so that it is updated as people place orders. I plan to add code to the complete_order function in ushop.pl which will write the items from the cart to a temp file, and remove the quantity from the inventory file. The items will then be cleared from the temp file when I print the shipping list (or added back to inventory if it is out of stock during this process).

In order to do this, I will need access to the cart line items. I am assuming that they are in the following fields (and will require parsing):

$cart_product_lines = $lines[129];
$cart_data = $lines[130];

Can you enlighten me a little as to the structure of these fields so that I can do this (or let me know if I am totally off base).

Also, do you know of a good online reference for using Perl to access MySQL?

Thanks.


Table of contents

Messages in this discussion
"Perl Reference for MySQL"
Posted by Charlie on at 02:32 PM
I bought a book called "MySQL and Perl for the Web" - haven't really gotten into it yet, but it does have a website that goes along with it
http://www.kitebird.com/mysql-perl/

"RE: Accessing MySQL from Perl"
Posted by Bill Weiner on at 04:27 AM
That is correct. On the CGI side of things, the $cart_data contains a bar-delimited list of the products that the customer ordered. The format of $cart_data is:

ID-NAME-DESCRIPTION-WEIGHT-QUANTITY-PRICE-TAXABLE-ID2-NAME2-DESCRIPTION2-WEIGHT2-QUANTITY2-PRICE2-TAXABLE2

... and so on... 7 fields for each product.

NOTE: In the above string, all of the dashes (-) should actually be bars ( | ) except bars don't show up that well in this forum.

You may want to take a look at the "get_cart_html" subroutine and the "get_cart_text" subroutine in the ushop-lib.pl script. As an example, you could see how those subroutines handle parsing the fields of the $cart_data string.


"RE: Accessing MySQL from Perl"
Posted by Brian Levine [blevine@cfl.rr.com] on at 01:45 PM
Thanks. That should give me what I need. Heres my (pseudo)code:

# update quantities in inventory
# link to database
use DBI;
dbh = DBI->connect('dbi:mysql:mystore','userid','password');

# get each lineitem from shopping cart
$catalogid = ;

# get current qty, and sold qty for item
$sql = "select Qty, Sold from Inventory where CatalogId = '$catalogid'";
$sth = $dbh->prepare($sql);
$sth->execute // die "Could not execute SQL statement";
@row=$sth->fetchrow_array;

# update with current item order qty
$qty = $row[x] - $newqty;
$sold = $row[y] + $newqty;

# write updated numbers to inventory table
$sql = "update Inventory set Qty=$qty, Sold=$sold where CatalogId = '$catalogid'";
$sth = $dbh->prepare($sql);
$sth->execute // die "Could not execute SQL statement";

# TODO: write cart line to temp file for possible undo if out of stock

This should be the final step in making my inventory truly live.