Odd question. Odd design. Database need.

[Boom]

Ok I'm going to do my best to explain this, but bear with me.

 

We have a standard OSCommerce site. Every item has it's own page. I'm currently trying to add a new line of products to our site that just don't lend themselves well to a "page per item" setup.

 

I'm adding 800+ routerbits. Instead of creating a standard page for each, I'm actually using an STS template, and creating a "Category" page with the products listed. I'm manually creating an "add to cart" button to each line item.

 

Here's a screenshot of the setup:

 

 

I'm building these pages by hand, one by one. I removed the "$content" tag so it doesn't display the items in a grid underneath. Instead, I encode each "add to cart" button to actually add that item to your cart.

 

Works great... except after creating 3 of these new sections, it suddenly dawned on me that I FORGOT TO DISPLAY THE PRICE. >.<

 

So here's the thing. I can manually put the price into the page, but that means when our system has prices changes, I'll have to updated each one manually. Bleh.

 

So here's my question. Is there an easy way of pulling the price for a specific item (by item number) from the database and telling it to display in a specific cell. (I'll make a new column for prices)

 

Does this question even make sense?

 

Thanks in advance for your wisdom.

 

Boom

[MrPhil]

I'm trying to figure out what you're doing here. Normally in osC, each individual product (a record in the database) gets its own page created on the fly when that product display is requested. I'm not sure what STS does to this model, if anything. You do not need to create any product pages manually.

 

What it sounds like you're trying to do is to consolidate a group of related products together onto one page, rather than having them appear on individual pages. Could you treat these as "attributes" to a central product, each with an add-on price increment? Look into some of the "product attribute" add-ons. Again, no need to manually create pages or manually add to pages.

[NodsDorf]

Hi Boom,

 

I think I maybe able to help.

 

Even though you are using STS and making your own buttons you still need the products in your database for this to work. You know you can disable products via the admin panel so they aren't displayed in the categories section and search so this isn't a big deal.

 

First you'll want to create each product in your products table in your database. There is php code in oscommerce stock that shows you how to display the products price in any given cell. If you're not sure how to do that, just install a stock oscommerce in another section of your site and see how the products price is displayed when you select a category that contains products.

 

 

 

Use that code to populate your cells.

 

Once you have your products table with prices in your database you then can use SQL and Excel CSV files to manage prices much more easily.

 

First create your master CVS file

 

Open cpanel and go to myPHPadmin

 

Run this statement.

 

SELECT products_model, products_price FROM products

Order by products model

 

You then should have a list of all your products_model numbers and prices sorted numerically.

 

Next click the Export button on your Cpanel and save as CSV for MS Excel

 

Now you have your master CSV file

 

When prices change you can use a site like this: http://csv2sql.evandavey.com/

 

Update your prices in CSV file to reflect the current price, then use the converter to format the CSV file by Model Number and use the UPDATE command at this site. It will spit a big ole SQL update statement. Then run it via myPHP admin and all your prices are updated.

[♥kymation]

I did something similar to this, but automated it using the Products Specifications addon (and a bit of custom code.) The "product" page is actually a Category page listing the products in that category. The product pages exist, but they are not used since there is no link to them. The only difference is that there is only one such table per page. Example: http://www.adss.net/670320-760385-c192_193_203.htm

 

I'll provide the modifications used if anyone is interested.

 

Regards

Jim

[Boom]

Hi Boom,

 

I think I maybe able to help.

 

Even though you are using STS and making your own buttons you still need the products in your database for this to work. You know you can disable products via the admin panel so they aren't displayed in the categories section and search so this isn't a big deal.

 

First you'll want to create each product in your products table in your database. There is php code in oscommerce stock that shows you how to display the products price in any given cell. If you're not sure how to do that, just install a stock oscommerce in another section of your site and see how the products price is displayed when you select a category that contains products.

 

 

 

Use that code to populate your cells.

 

Once you have your products table with prices in your database you then can use SQL and Excel CSV files to manage prices much more easily.

 

First create your master CVS file

 

Open cpanel and go to myPHPadmin

 

Run this statement.

 

SELECT products_model, products_price FROM products

Order by products model

 

You then should have a list of all your products_model numbers and prices sorted numerically.

 

Next click the Export button on your Cpanel and save as CSV for MS Excel

 

Now you have your master CSV file

 

When prices change you can use a site like this: http://csv2sql.evandavey.com/

 

Update your prices in CSV file to reflect the current price, then use the converter to format the CSV file by Model Number and use the UPDATE command at this site. It will spit a big ole SQL update statement. Then run it via myPHP admin and all your prices are updated.

 

Thanks so much for the suggestion Nods! I'm actually leaving for vacation today, but I'll be digging into this next week when I get back. Didn't want you leave you hanging without a "thanks". :)

 

Thanks!

 

Boom

[♥geoffreywalton]

You could bas*ardise http://addons.oscommerce.com/info/7286 to update your site from a csv file.

 

It is written to collect a file from a supplier and process it.

 

G