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

Original Message
"Integrating with MySQL"

Posted by Brian Levine [blevine@cfl.rr.com] on at 07:34 AM
I know that this thread has existed in the past, but I thought that I'd bring it up again, as the need has arisen.

Does anyone have a good basic example of integrating uShop with MySQL? My goal is to develop an order applet that reads its parameters from a database, and can also disable itself (or display an out of stock message) when quantity on hand in the database is 0.

In short, I'm tired of having to edit HTML pages (and my store has hundreds of 'em) everytime I run out of stock on an item.

Thanks for your help.


Table of contents

Messages in this discussion
"RE: Integrating with MySQL"
Posted by Joseph Pishgar [support@uburst.com] on at 01:22 PM
Hello Brian,

We don't have any SQL interfaces or examples of successful inventory tracking handy in the Support Archives currently.
The uShop Developers Guide is quite extensive concerning applets and compilation..

Additionally, if you are going to be developing an interface, we can provide a few pointers to get you started in the right direction.

Regards,
Joseph Pishgar
Microburst Technologies, Inc.
www.uburst.com


"One idea for dynamically defining your uShop applets"
Posted by Christine on at 02:09 PM
Do you know anything about PHP? It is an open-source language that can work with a MySQL database. Many web-hosting providers who support MySQL will have PHP installed as well, and there is a good reference site at php.net.

Anyhow, depending on your programming skills, you could consider writing yourself some PHP code that reads your producttable and dynamically populates your applets.

For example here is a loop written in PHP which connects to your MySQL database, accesses your product table looking only for products with a quantityavailable greater than zero, and then only prints out an applet for each of those products. You should also notice that in this example I jump in and out of PHP with the <?php and ?> symbols. FYI caution - this example does not include any error checking/handling:


<?php

// connect to your MySQL database
$db_socket = mysql_connect("yourhost.com","user","password");

// select only products which have a quantity
// available greater than 0 in your database
// (This assumes you have a table which is keeping
// track of your inventory, of course.)
$q = "select * from yourproducttable where qtyavailable > 0";
$row = mysql_db_query("yourdatabasename",$q,$db_socket);

while($row = mysql_fetch_object($q))
{

// here is a snippet of an applet, i left the
// rest of the applet out for brevity and made
// up some field names for your product table
?>

<PARAM NAME="id" VALUE="<?php echo $row->productid;?>">
<PARAM NAME="name" VALUE="<?php echo $row->productname;?>">
<PARAM NAME="description" VALUE="<?php echo $row->productdesc;?>">
<PARAM NAME="option1" VALUE="<?php echo $row->productoption1;?>">
<PARAM NAME="qty" VALUE="1">
<PARAM NAME="price" VALUE="<?php echo $row->price; ?>">


<?php
} // end while loop

?>


By the way, I've also seen this done with ColdFusion and an Access database.

Christine Edmunds
Microburst Technologies Support Team


"PHP MySQL Suggestion"
Posted by Christine [support@uburst.com] on at 09:09 PM
I made a mistake in my example, it should have said:

$q = "select * from yourproducttable where qtyavailable > 0";
$qresult = mysql_db_query("yourdatabasename",$q,$db_socket);

while ($row = mysql_fetch_object($qresult))
{
...


Sorry for the typo!

-- Christine


"PHP MySQL Suggestion Works!"
Posted by Brian Levine [blevine@cfl.rr.com] on at 04:34 PM
Wow! This is fabulous! I keep all of the tag info for my catalog in one table, along with image path and size, and using just a few basic page templates, I only have to change the id data in the query for each item. If qty = 0 in the table then it displays the out of stock message instead of the item applet.

Not only will this help me to update my catalog a lot easier, but it makes it simple to add new pages.

You need to include something about this in your docs. Almost all Unix hosts support PHP and MySQL.

Check out this link for a sample page:
http://www.animecollectibles.com/acnew/ac_sm_epfigs.php3


"Building on Brian's example"
Posted by David Futcher [david@futcher.com] on at 00:56 AM
Thanks for the tips here on MySql integration. I got a copy of Brian's notes and tips on creating pages which will read from a database to create pages on the fly. It really helped get me going! (I especially appreciate the tip on using MyODBC to link MS Access to MySql!)

I made a couple adjustments to make this implementation work with my site, and I thought I'd run through some of them. You can go to http://www.flagline.com and check out "ART BANNERS" at the bottom to see my implementation. Eventually, we'll get the whole site converted over to MySql, but for now, it's just the Art Banners.

First, I didn't use the linkdefs database for page generation. I created another field for each item that contained keywords so all items matching a keyword query would all show up together.

Second, I also took this opportunity to begin using javascript instead of the Java applets to add items. With a bunch of individual applets on each page, instead of using a dropdown above 6-10 items, I didn't want to wait for all of them to load.

The other problem I ran into was that if a category had a bunch of items, I wanted to be able to have the script automatically limit the number of items on a page to a specified number, and generate links to pages 2,3,4, etc. I finally got all of that figured out, too...

Anyway, I've really enjoyed learning PHP over this process, and I'm starting to see the unlimited integration we could ultimately realize with uShop. Now, to get real time inventory control, we just need somebody working on integrating the checkout process into the database...if you already have, please let me know! Thanks for posting these kind of tips!

Thanks again,

Dave - david@futcher.com


"brians notes?"
Posted by webmissie [factorydirectcraft@yahoo.com] on at 07:26 PM
Just wondering where you found brians tip and notes. He seems like he and you have it going on but I am not really sure on how to put all the pieces together. I have read all the php manual and the sql manual and tutorials from both but im still not sure how to get my ushop to work with all this. Thanks in advance for any notes or assistance.

"Integrating uShop with PHP & MySQL - documentation"
Posted by Microburst Support Team [support@uburst.com] on at 02:27 PM
If you are looking for Brian's notes on how he integrated uShop with a MySQL database using PHP, just e-mail us at support@uburst.com and we'll send it out to you. Brian updated this document to include an ASP example on 4/29/02.

"Integrating uShop with PHP & MySQL - documentation"
Posted by Peter Corbello [pcorbell@bigpond.net.au] on at 07:03 AM
Is it possible for me to get a copy of this documentation

Thanks
PC


"RE: Integrating uShop with PHP & MySQL - documentation"
Posted by Bill Weiner on at 04:15 AM
As mentioned above, just go ahead and send an email to us at support@uburst.com and we'll send you the notes.