www.uburst.com www.uburst.com

"Accessing MySQL from Perl"

Go back to the LobbyClick here to Go Back to Main ListingClick here to see helpClick here to Search the Forum

Database Ideas
Forum Type: Public
Moderator: edmunds
Time Zone: EST
Printer Friendly Format
Original Message
 
"Accessing MySQL from Perl"
Posted by Brian Levine on Apr-16-02 at 01:12 PM (EST)
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.

Click to Send Alert Message to the Administrator Click to edit this messageClick to EMail Click here to reply to this messageClick here to reply to this message with quotesClick to goto the Table of Contents

 Table of Contents

Perl Reference for MySQL, Charlie, Apr-16-02, (1)
RE: Accessing MySQL from Perl, Bill Weiner, Apr-17-02, (2)
RE: Accessing MySQL from Perl, Brian Levine, Apr-18-02, (3)

 

 
Click here to goto Click here to goto the Lobby
Messages in this discussion
 
1 . "Perl Reference for MySQL"
Posted by Charlie on Apr-16-02 at 02:32 PM (EST)
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/
Remove this Message: Administrator and Moderator onlyClick to Send Alert Message to the Administrator Click to edit this messageClick here to reply to this messageClick here to reply to this message with quotesClick to goto the Table of Contents
 
2 . "RE: Accessing MySQL from Perl"
Posted by Bill Weiner on Apr-17-02 at 04:27 AM (EST)
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.

Remove this Message: Administrator and Moderator onlyClick to Send Alert Message to the Administrator Click to edit this messageClick here to reply to this messageClick here to reply to this message with quotesClick to goto the Table of Contents
 
3 . "RE: Accessing MySQL from Perl"
Posted by Brian Levine on Apr-18-02 at 01:45 PM (EST)
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.

Remove this Message: Administrator and Moderator onlyClick to Send Alert Message to the Administrator Click to edit this messageClick to EMail Click here to reply to this messageClick here to reply to this message with quotesClick to goto the Table of Contents


Archive This Thread: Admin and Moderator OnlyRemove This Thread: Admin and Moderator Only
Click here to goto Click here to goto the Lobby

 

 

 

 

 

 

 

 

 

 

 

 
Questions or problems regarding this bulletin board should be directed to Webmaster
©1997-1999 by DCScripts. All rights reserved.