product ID format question - real easy one :)

[CLRH2O]

This is a super quick one,

 

My store sells music, some songs have remixes. These songs have the same catalog number but with an R attached to the end. Can I change the product ID number to have a number and a letter in it? I know I can put the letter there using phpmyadmin no problem - but will OSC parse it and permit sales or otherwise work properly if a product ID number also has a number in it?

 

Example:

 

Song One - catalog number FORMP3-001 - OSC product ID is "001"

Song Rmx - catalog number FORMP3-001R - OSC product ID is "001R"

 

Before I go and potentially kill my OSC install - will OSC's PHP parse these product ID numbers that also have a letter in them?

[CLRH2O]

this might help you undertand what I'm meaning:

 

INSERT INTO `products` VALUES (014, 1000, 'FORMP3-014', 'mp3_aflame.gif', 1.9900, '2006-01-23 23:28:44', '2006-03-15 20:53:22', NULL, 0.00, 0, 0, 15, 0);

INSERT INTO `products` VALUES (014R, 1000, 'FORMP3-014R', 'mp3_aflame_pr.gif', 1.9900, '2006-01-23 23:29:26', '2006-03-15 20:53:11', NULL, 0.00, 0, 0, 11, 0);

 

That is two songs - the original and the remix. Those entries I've modified by hand and saved to a .sql file which I can use to add those products into the mySQL database for my shop.

 

If I were to do that - would OSC know how to use that remix properly as a saleable item for DOWNLOADS in particular (which is what the site sells...MP3 files)

[custodian]

This is a super quick one,

 

My store sells music, some songs have remixes. These songs have the same catalog number but with an R attached to the end. Can I change the product ID number to have a number and a letter in it? I know I can put the letter there using phpmyadmin no problem - but will OSC parse it and permit sales or otherwise work properly if a product ID number also has a number in it?

 

Example:

 

Song One - catalog number FORMP3-001 - OSC product ID is "001"

Song Rmx - catalog number FORMP3-001R - OSC product ID is "001R"

 

Before I go and potentially kill my OSC install - will OSC's PHP parse these product ID numbers that also have a letter in them?

 

Well maybe this will answer your question. Here is one my MY product codes

 

gmfgr9156-u2S3

[CLRH2O]

Well maybe this will answer your question. Here is one my MY product codes

 

gmfgr9156-u2S3

 

 

Ah EXCELLENT - I think :)

 

so an example of an item you might add to your store could be something to this effect:

 

INSERT INTO `products` VALUES (gmfgr9156-u2S3, 1000, 'GMFRG-U2S3', 'gmfrg_u2s3.gif', 1.9900, '2006-01-23 23:28:44', '2006-03-15 20:53:22', NULL, 0.00, 0, 0, 15, 0);

 

would that be sorta right?

 

That would mean your adding 1000 items of your product GMFRG-U2S3 and telling OSC that it's product ID "gmfgr9156-u2S3" right? In which case if for example you had a hard coded link to go to that products description page the url would be:

 

http : // www . yourstoreurl . com/product_info.php?products_id=gmfgr9156-u2S3 (dont mind the spacing at the sart of that link, I just wanted it to display everything for you)

 

 

If I'm all right about all of that - WIKKID MAN!

[custodian]

Ah EXCELLENT - I think :)

 

so an example of an item you might add to your store could be something to this effect:

 

INSERT INTO `products` VALUES (gmfgr9156-u2S3, 1000, 'GMFRG-U2S3', 'gmfrg_u2s3.gif', 1.9900, '2006-01-23 23:28:44', '2006-03-15 20:53:22', NULL, 0.00, 0, 0, 15, 0);

 

would that be sorta right?

 

Well... I add all my items through the oscommerce admin - catalog.

 

SQL syntax is not my strong point therefore I while that looks right I can't guarantee it is accurate.

 

Also, there is a limit to the characters for a product id that is preset in by osc in the db - I increased these sizes in order to be able t take my long product id's.

 

In order to increase the product id length you need to do the following

 

alter table osc_products change products_model products_model varchar(12);

 

alter table osc_orders_products change products_model products_model varchar(12);

 

Change the (12) in both those lines to whatever you require. Mine are

 

alter table osc_products change products_model products_model varchar(20);

 

alter table osc_orders_products change products_model products_model varchar(20);

[CLRH2O]

Hmmm - it seems that it's not going to work for me. Even though it appears you are able to do it, if I place FOR-001R or even 001R in the "products_id" field by manually editing the talbe data with phpmyadmin - it just spits bakc out a '0' in the products_id field once the apply button is pressed. And conversely if I try to add the info into the database using a .sql file mySQl just throws me an error saying:

 

Error

SQL-query:

 

INSERT INTO `products`

VALUES (

 

002L, 50, 'FOR-002L', 'vinyl_mulletcut.gif', 9.9900, '2006-01-20 16:20:32', '2006-03-15 21:05:21', NULL , 1.00, 0, 0, 22, 0

)

 

 

MySQL said:

 

#1054 - Unknown column '002L' in 'field list'

 

 

 

So maybe you cant acutally use letters in the product ID for OSC's mySQL database.... Is there posibly anything I'm missing that I can do to make this work?

[custodian]

Ah EXCELLENT - I think :)

 

 

That would mean your adding 1000 items of your product GMFRG-U2S3 and telling OSC that it's product ID "gmfgr9156-u2S3" right? In which case if for example you had a hard coded link to go to that products description page the url would be:

 

umm.. not sure about the 1000 item thing you mentioned.

 

I know you can add duplicate product codes (I've tested it) since the db product id is unique. But that would make it hard to tell the different in the items.

 

Maybe I'm not fully grasping what you need / are looking t do.

[CLRH2O]

Basically what it all comes down to is - as a record label we release songs with a catalog number. That number also includes a bit of letters too. The label is named FORCE Recordings so an average catalog number for a single release would be:

 

FOR-001

 

In fact that's the catalog number for the very first piece of vinyl we ever put out. In the case of what we sell now on the website the MP3 for that release would be catalog number:

 

FORMP3-001

 

now, the way OSC is set up - as I add items into the database using the admin console, osc increments the product ID one at a time from 1 to however many items you're adding to your store. For most purposes this is great and fine. But for us - we have to create a lot of offsite links to product pages, and banner links to specific products and just in general need easy cognatively straigh forward access to the product ID number for a particular song. In our store right now FORMP3-001 is actually product ID 32...... Which for me makes it had to remember if 97 is the song Dying or Become the Shine.... And what was 72 again? which track was that.. and what songs were 21 24 and 112?

 

See what I'm getting at? if I can have the OSCommerce "Product_ID" be the same exact letters and number as the catalog number we assign to each release I can then just tell who ever might be doing a message board post, or making a banner, or even for myself for onsite static links that one number and a link can be made to get to the right song in our store.

 

Instead of a link that is:

 

http : // www . forcerecordings . com/product_info.php?products_id=32

 

meaning the song FORCE RECORDINGS 001... I can have a link that is:

 

http : // www . forcerecordings . com/product_info.php?products_id=FOR-001

 

 

And then even more important for me as an owner and manager is remixes. If I can use letters and numbers (and dashes / special character) then I can have FOR-001 and FOR-001R for the original song and that song's remix product_ID instead of 32 and 97 (because the remix would have been added to the catalog at a later date after many other songs would have been added to the store).

 

 

Does this make any sense?

 

and btw - BIG THANK YOUS to YOU for jumping on this and helping me out :) And to any of the rest of you out there that might also have info, please do chime in :)

[CLRH2O]

And just to avoid confusion I know OSC uses the word CATALOG too - but in our case the word "catalog" in the definition "Catalog Number" actually has notihng to do with OSC or it's "catalog". the CATAOG NUMBER is our analog or real world paper trail definition for a single release or song, and the Product_ID is what OSC defines that item by.

[CLRH2O]

Ah yes - and about the 1000 items thing. For us since were selling MP3 files as downloads - I've just choosen a 1000 downloads limit for each song as a sort of level of song sucsess. If any particular song sells 1000 downloads - it will end up getting a Vinyl release as well.

 

The number 1000 could have been 20... or 2 million. I just happend to choose 1000. So that is what's up with that.

[Guest]

I do believe that Henry is talking about the 'products_model' column - why not use this ('Model' in admin).

 

You cannot use 'products_id' - this needs to be an integer and is autoincrement in the database :)

 

Matti

[Guest]

You can redirect to your product page using the model number by using this file:

 

<?php
/*
 $Id: product_redirect.php,v 1.00 2006/04/01 Johnson Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 copyright (c) 2006 Suomedia
 http://suomedia.com

 Released under the GNU General Public License
*/


 require('includes/application_top.php');

 $product_query = tep_db_query("select products_id from " . TABLE_PRODUCTS . " where products_model = '" .  $_GET['products_model'] . "' limit 1");
 $result = tep_db_fetch_array($product_query);
 $products_id = $result['products_id'];

 tep_redirect(FILENAME_PRODUCT_INFO . '?products_id=' . $products_id);
 ?>

 

Here is an example URL: http://localhost/stock/product_redirect.ph...s_model=MSIMEXP

 

Enjoy :)

 

Matti

[CLRH2O]

Into which document should I inject that block of code? Or should that code be placed into a completely new file named "Product_redirect.php" and saved to the root of my catalog folder for use in some way?

 

Also, the link you provided doesn?t go anywhere / is broken.

 

I've actually in the meantime come up with a completely original all integer (for the auto increment feature of OSC in the MySQL database) based numbering system for all our releases. It works in the meantime and I'm sufficiently happy with it if I do end up sticking to it for the WEB store.

 

The last thing to sort out now is the FRIGGIN downloads.

 

It would seem - that with my 9 different Product Attributes and my 12 different Product values.... Downloads are NEARLY IMPOSSIBLE to get working right.... I have half a mind to delete ALLLLL my product options and values and only leave one of each (download and yes). Sort of defeats the purpose of having product options of course - but hey... downloads in OSC seem to be one of the single hardest things to make work.