uShop English (U.S.) for 179!

uStorekeeper English (U.S.) for 149!

 Products
       uTest
       uReserve
       uShop
       uStorekeeper
       uGolf
       uDirectory
       uSignIn
 Tech Support
       Support Policy
       Knowledge Base
            uTest
            uReserve
            uShop
            uStorekeeper
            uGolf
            uDirectory
            uSignIn
       Documentation
       Reference Sites
 Legal
       Software Piracy
       Legal Notices
       Privacy Policy
       Licensing
 Miscellaneous
       Reseller Info
       Contact Us
       Site Map
Accessing MySQL from Perl

Knowledge Base Lobby : uShop Support Conference : Database Ideas
Dec-14-17 01:18 AM EST
Original Message
Accessing MySQL from Perl
Author Brian Levine on 04-16-2002 at 13:12 (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.
E-MAIL AUTHOR | TABLE OF CONTENTS

Table Of Contents
  Perl Reference for MySQL Charlie, 2002-04-16 14:32:47 (1)
  RE: Accessing MySQL from Perl Bill Weiner, 2002-04-17 04:27:50 (2)
            RE: Accessing MySQL from Perl Brian Levine, 2002-04-18 13:45:01 (3)

Messages In This Discussion
         1. Perl Reference for MySQL
        Author Charlie on 04-16-2002 at 14:32 (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/
TABLE OF CONTENTS
         2. RE: Accessing MySQL from Perl
        Author Bill Weiner on 04-17-2002 at 04:27 (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 ( &pip; ) 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.
TABLE OF CONTENTS
                 3. RE: Accessing MySQL from Perl
                Author Brian Levine on 04-18-2002 at 13:45 (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.
TABLE OF CONTENTS

© 2003 Microburst Technologies, Inc.