Update product prices using PHP/mySQL?

Question by Mark C: Update product prices using PHP/mySQL?
We have a website with about 100 products that can be bought online using a shopping cart (which uses html forms and perl scripts).

The site is static HTML so we have full control over search engine optimisation.

However, as you can imagine it becomes a right pain in the ass updating prices.

Can anyone give me any pointers on how to update the prices using PHP and a mySQL database.

The way I envisage it working is that each of our 100 product pages (currently html) will become php. Where the price should be in the source code I will put a php function echo to insert the price for the product. If it helps all products have a unique number that could be used to reference the mySQL database.

Although I sort of understand how it should work, I could do with a few pointers on how to implement this. Any pointers would be greatly appreciated.
I also presume that I will be able to up/download data to the mySQL database using a correctly formatted CSV file, rather then having to code/design an admin section as well (as it will be only myself doing the updating)?

Best answer:

Answer by J P
I think it would make more sense, to have each of the products (name, description, price, photos) stored in the database, then just use PHP to query the database and display the information. If you are going to use dynamic tools like PHP and MySQL, it seems like a waste to hard-code information (like prices) into the PHP page, that wouldn’t be much different that hard-coding them into an HTML page.

To get started, I would create a database table that contains your products. Then I would write some PHP to query that database and retrieve that product information. After you’ve got the data retrieval part down, then think about how to restructure the HTML as PHP, to use the dynamic data.

It sounds like you’ve got a lot of work ahead of you, good luck 🙂

Add your own answer in the comments!

Revisions

There are no revisions for this post.

Tags: , , , ,

5 Responses to “Update product prices using PHP/mySQL?”

  1. byteofk July 3, 2011 at 6:06 PM #

    The CSV parsed product file sounds like a great option.

    Get a cheap and easy book about PHP and MySQL and you’ll be all set.

    As far as the database goes, find some basic help for designing databases, it’s a whole lot easier when you think it through. Each table needs a unique identifier (even if you don’t use it in display), and make sure any one-to-one, many-to-one, one-to-many and many-to-many options are covered.

    The easiest way to do it though is a denormalised database (basically a single Excel file that you convert to .csv). You can pretty much do that without learning anything special on the database front.

    Umm yeah, you won’t have 100 product pages in PHP, you have one PHP page that produces 100 different outputs based on the product ID.

    myshop.com/displayitem.php?pid=34 would show item #34. The same page can show any of the items.

  2. socheapcomputers July 3, 2011 at 6:51 PM #

    I had the same problem with this some time ago, i went to downloads.com and found a free program that will change the code in csv or regular excel file into mysql code. Which helped me alot. I will have to get on later and inform you about the program.

    Search for Excel to MySql, this program allow you to tell it how many columns and row that are in the file and generates the code for it. You will also need to go to MySQL web site to get the ODBC Connector that will this program to create the code.

  3. fretless July 3, 2011 at 7:20 PM #

    You’d better design carefully your database and keep all separate fields in different columns, including price.
    Then amongst other information you will retrieve from your database you will also the price data.
    This way it will be easier to get and update the data (phpmyadmin and other tools are available for viewing and manipulating mysql).
    Simple queries (update) will then let you easily update the prices from a form.
    You’d better keep data level away from php as it will be difficult to update.

  4. Gonzo July 3, 2011 at 8:06 PM #

    I have a suggestion, you have a lot of work ahead of you and another solution may be the best option. What you want to do will select the price dynamically from the database, but you are still going to have a lot of static elements, and you will have to hand code the product id’s into every product on every page.

    I suggest setting up a new shopping cart that you can use for your catalog pages. I recommend OScommerce http://www.oscommerce.com, it is opensource and free to use. There are a lot of indepndantly written conributions/modifications you can install to customize the store. You will have to learn a little bit about PHP to install contributions, but most of them have well written instructions. With OScommerce, there is a contribution called ezpopulate. After you install it, you can modify every product in the store (and add aditional products) by uploading an excel spreadsheet. That will make it much easier to manage your store in the long run. You can completely redesign it to fit your website by modifying files in /includes (header, footer, left nav, right nav). Catalog pages are created in the store dynamically from the database. So as soon as you edit your database the changes will appear on you site, so you don’t have to make manual html changes. There is a pretty big learning curve depending on how much PHP you already know.

    As far as search engine optimization, I would not try to optimize the dynamic catalog pages. What I have always done in the past is set up OScommerce in a directory like /cart or /store and set up static html pages in the site root. These pages will be your search engine landing pages, and they will direct your visitors into the catalog pages. So instead of listing every product in X category on the X category landing page, have detailed information about X category on that page and then direct users into the X category catalog.

    OScommerce does have a couple search engine friendly contributions, but creating HTML landing pages has always worked out better for me.

    PM me if you want any live examples.

  5. princessil0523 July 3, 2011 at 8:08 PM #

    USE OSCOMMERCE

Leave a Reply