Outline

1. Introduction
2. The Situation
3. The Data – CSV to MySQL
4. HTML PayPal Button
5. MySQL Data into HTML forms using PHP

Introduction

Although this may seem very basic to most experienced Programmers, I thought this might help some others who were in the same situation I was.

What You’ll Need

• Know how to create a MySQL table
• Know how to perferm MySQL Queries
• Have a solid understanding of HTML
• Understand basic PHP

The Situation

I recently had to add an ecommerce store to a clients existing website. No problem, I have done that before. The difference was, I had either used Open Source Shopping Cart software, or used PayPal’s button creator. The client had over 100 items to sell with different sizes and colors for some. That ruled out PayPal’s button creator as manually creating 100+ buttons would take far too long. Also, the look of the site was to stay the same, so instead of implementing Shopping Cart software, I decided to try and figure out how to dynamically generate PayPal buttons with PHP.

The Data – CSV to MySQL

The client had provided me with an Excel Spreadsheet with Products, Prices, etc. The first step was to convert this spreadsheet to a .csv (Comma Separated Values) file. The .csv had a result like so: (Although there were more columns, I’ll keep it simple for the example)

productid,category,price,desc

001,cat1,50.95,example product one

002,cat1,40.95,example product two

003,cat2,10.00,example product three

I needed to import these products into a MySQL table that I had previously created. I found a very helpful tool that converts CSV to SQL Inserts.

INSERT INTO products (`productid`,`category`,`price`,`desc`) VALUES ('001','cat1','50.95','example product one');
INSERT INTO products (`productid`,`category`,`price`,`desc`) VALUES ('002','cat1','40.95','example product two');
INSERT INTO products (`productid`,`category`,`price`,`desc`) VALUES ('003','cat2','10.00','example product three');

HTML PayPal Button

Now I have my data stored and organized. Using PayPal’s button creator, I made a button so I could get the HTML I would need. The button code is as follows:

<form target="paypal" action="https://www.paypal.com/cgi-bin/webscr" method="post" />
<input type="hidden" name="cmd" value="_cart" />
<input type="hidden" name="business" value="youraccount@paypal.com" />
<input type="hidden" name="lc" value="US">
<input type="hidden" name="item_name" value="example product one" />
<input type="hidden" name="item_number" value="001" />
<input type="hidden" name="amount" value="50.95" />
<input type="hidden" name="currency_code" value="USD" />
<input type="hidden" name="button_subtype" value="products" />
<input type="hidden" name="shipping" value="0" />
<input type="hidden" name="handling_cart" value="4.95" />
<input type="hidden" name="add" value="1" />
<input type="hidden" name="bn" value="PP-ShopCartBF:btn_cart_LG.gif:NonHostedGuest" />
<input type="image" src="images/addcart.gif" border="0" name="submit" alt="PayPal - The safer, easier way to pay online!" />

These are the lines that will be focused on:
name=”business” value="youraccount@paypal.com" //This is just to link the button to your PayPal Account
name=”item_name” value=”example product one” //This will be the name of the item, this will be generated by the “desc” column
name=”item_number” value=”001″ //This is the productid, each item has a unique ID number
name=”amount” value=”50.95″ //This will need to be generated by the price column
name=”shipping” value=”0″ //I have shipping set to $0, as to keep it simple we will just charge a flat shipping rate in our example
name=”handling_cart” value=”4.95″ //This is the flat shipping rate for all orders

MySQL data into HTML forms using PHP

Now, you need to put the two together. You’ll need to:

1. Connect to the MySQL Database
2. Retrieve desired data
3. Have the data populate the correct fields
4. Loop the process until all products are listed

Connecting to the Database

The following is used to connect to the database:

<?php
$con = mysql_connect("mysql.yourdomain.com","login","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}mysql_select_db("databasename", $con);


Replace ("mysql.yourdomain.com","login","password"); with your login information. Next you need to connect to the table that your products are in.

$result = mysql_query("SELECT * FROM products WHERE category=''cat1");

This will allow you to select which data you wish to display. For this example, we just want to display products in the first category.

Displaying your Data from your MySQL table

As much as I hate HTML tables, I will be using them here to hold our dynamic data. You can however, use whatever suits your needs. For this example, add the following to your PHP file.

echo "<table border='0' width='600'>
<tr>
<th>Description</th>
<th>Picture</th>
<th>Price</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr class=\"itemname\">";
echo "<th colspan=\"3\">" . $row['desc'] . "</th></tr>";
echo "<tr class=\"imgrow\"><td class=\"imgcell\">" . "<img src=\"images/products/" . $row['id'] . ".jpg\" alt=\"Description if needed\" />" . "</td>";
echo "<td class=\"pricecell\">" . "$" . $row['price'] . "<br />plus Shipping" . "</td>";

Now we have our basic structure laid out. The while command will cycle through our data and display all the items that have a category value of cat1. Also note, all of the " need to be "add slashed," as the quotes are a delimiter in PHP. This means, wherever you want a " to appear, you have to add a / before it, even when you are echoing HTML.

We're More Than Halfway!

Check back within the next week to read how to Dynamically Generate PayPal Add to Cart Buttons! Or you can follow me on Twitter ( @MetacomCreative ) for updates, or subscribe to my RSS feed.

Related posts: