Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Unique Product Id


ms35d4

Recommended Posts

Hello

 

This is a problem I am facing at the moment.

 

We have different suppliers, we use there data feed to import products into our site automatcially via ftp or html link.

 

Now, we use the supplier SKU number in there file as product ID however there are many occasions when 2 or more suppliers use the same SKU number. So to avoid this we add an expression to the product id hoping to make them all different However we still come across situations where we end up with the same product ID

for example

SKU value added to SKU to try and create unique Product ID each time

Supplier A = sku = 5948 + 51935 = 57883

Supplier B = sku = 5950 + 51933 = 57883

 

As you can see in the example although the SKU are different and with different values used to add to the sku we still end up with the same 57883 calculated for the product id.

 

So after Supplier B feed loads it ends up changing the products for supplier A

and when Supplier A feeds loads it ends up changing the products for supplier B

 

We are getting this issue with 2 suppliers we have many more to add so can anybody please suggest some way how this can be resolved.

 

The application used to upload the feed allows expressions to be added to the Product ID column so I tired using the following

 

=((x*x/2+15))

 

x = product id in supplier feed.

 

However this creates negative values for Product ID - which I think will cause errors in database -

 

Other Mathematical operations supported by the application are: * - multiplication; / - division; - subtraction; + addition; < less than; < more than; = equal List of functions: sqrt, div, mod, int, frac, random, trunc, round, sin, sinh, arcsin, arcsinh, cos, cosh, arccos, arccosh, tan, tanh, arctan, arctanh, cotan, cotanh, arccotan, arccotanh, sec, arcsec, sech, arcsech, csc, csch, arccsc, arccsch, abs, ln, lg, log, pi, exp, ! (factorial), ^ (degree)

 

 

would appreciate if anyone can make any suggestions or workaround. PLEASE PLEASE PLEASE

 

Kind Regards

Link to comment
Share on other sites

I'm getting a little confused as to why you need mathematical formula

 

You are manually adding a number to the product ID

 

you give the following example:

 

Supplier A = sku = 5948 + 51935 = 57883

Supplier B = sku = 5950 + 51933 = 57883

 

is there any reason why it can not be:

 

Supplier A = sku = 5948 + 51935 = 5948051935

Supplier B = sku = 5950 + 51933 = 5950051933

My store is currently running Phoenix 1.0.3.0

I'm currently working on 1.0.7.2 and hope to get it live before 1.0.8.0 arrives (maybe 🙄 )

I used to have a list of add-ons here but I've found that with the ones that supporters of Phoenix get any other add-ons are not really neccessary

Link to comment
Share on other sites

I'm getting a little confused as to why you need mathematical formula

 

You are manually adding a number to the product ID

 

you give the following example:

 

Supplier A = sku = 5948 + 51935 = 57883

Supplier B = sku = 5950 + 51933 = 57883

 

is there any reason why it can not be:

 

Supplier A = sku = 5948 + 51935 = 5948051935

Supplier B = sku = 5950 + 51933 = 5950051933

OR even use the suppliers initials

Supplier ABC = sku = 12345 + ABC = ABC12345

Supplier xyz = sku = 12345 = XYZ12345

Excel can add the 2 values together like this. I forget the name of the function, but I have used it before.

 

Tim

Link to comment
Share on other sites

Thank you both for your replies

 

 

 

I am not adding them manually, basically the csv files from the suppliers have like 10000 or 18000 products

there are many suppliers, and we pick up there feed automatically using third party application

 

There is feature in there to add expression, so for sku I tired the things I mention in my original post. However I am stil getting stuck.

 

@ knifeman

 

I tried using letters but Product ID requires int (11)

 

Can this be changed to VARCHAR (25) to accept charachters and numbers, as I have tried this but then the application I use shows error saying values dont match with database, it then doesnt allow to proceed any further, I spoke to the developers and they told me that product ID should not be made varchar as it will crash database and cause issues.

 

The thing with doing it in excel is that the feeds will be updated automatically so if we need to first download and edit things in excel and then upload it will become too time consuming and defeat the purpose of automation, which is a must for us. with so many different suppliers and products

 

 

 

 

Please can you let me know how you did it in the past.

 

thank you once again for your replies much appreciated

 

 

 

Regards

Link to comment
Share on other sites

You said in your first post that different suppliers sometimes use the same SKU numbers.

 

That means you can't use a "simple" formula to insure a unique ID.

 

If you run the same number thru the same formula no matter who the supplier is you end up with the same result.

 

That's just how math works (thankfully :) )

 

To ensure a unique ID it has to be based on the supplier in some fashion.

 

And example might be like this.

 

Since you have 11 digits to work with:

 

If from supplier A your SKU = SUPPLIER_SKU + 10100000000

If from supplier B your SKU = SUPPLIER_SKU + 10200000000

If from supplier C your SKU = SUPPLIER_SKU + 10300000000

.

. (and so on)

.

 

Until you can differentiate between suppliers you will never be able to ensure a unique SKU number.

 

Off hand it sounds like you need an application to massage the feed before importation into the DB by your current application.

 

That is if you can tell which supplier uploaded the feed.

If I suggest you edit any file(s) make a backup first - I'm not perfect and neither are you.

 

"Given enough impetus a parallelogramatically shaped projectile can egress a circular orifice."

- Me -

 

"Headers already sent" - The definitive help

 

"Cannot redeclare ..." - How to find/fix it

 

SSL Implementation Help

 

Like this post? "Like" it again over there >

Link to comment
Share on other sites

Hey Germ

 

Thank you for your reply,

 

As I mention in my first post and as you rightly point out, having simple solution like

sku + 293488 for each supplier can not work - as if the 2 or more supplier have the same sku I will always get the same value for products id.

 

 

 

 

If I understand your post correctly you are basically saying I should have a unique value for each supplier, if I have misunderstood I apologise and would appreciate if you can further clarify please, works to a certain extent but is not full proof

 

This is what I have already attempted but I found many occasion where as per my example

 

Supplier A = sku = 5948 + 51935 = 57883

Supplier B = sku = 5950 + 51933 = 57883

 

 

Even though the supplier sku are differnt and the numbers I am using to add to the sku are different there are manay occasions where the both add up to the same value.

 

I have already suggested to the people who made the application for the feed about a message of some kind when this happens and before it actually loads products to the database or attempts to make changed so I can change the number I use to add the sku too to avoid conflicts. But they can not do this right away and they are taking into consideration until they do this I am rally stuck,

 

If I try more complex calculations I end with negative values for product id which is not good aswell. So I was wondering if there would be solution expression that would always give me a positive value.

 

It would be a caluculation based Mathematical operations supported by the application which are: * - multiplication; / - division; - subtraction; + addition; < less than; < more than; = equal List of functions: sqrt, div, mod, int, frac, random, trunc, round, sin, sinh, arcsin, arcsinh, cos, cosh, arccos, arccosh, tan, tanh, arctan, arctanh, cotan, cotanh, arccotan, arccotanh, sec, arcsec, sech, arcsech, csc, csch, arccsc, arccsch, abs, ln, lg, log, pi, exp, ! (factorial), ^ (degree)

 

So I was hoping if they were any mathematical genius amongst us who could solve this problem

 

 

Regards

Link to comment
Share on other sites

Until you can differentiate between suppliers you will never be able to ensure a unique SKU number.

That is a mathematical fact you cannot overcome with a formula based solely on the SKU supplied by the feeds alone if SKU's by different suppliers are equal.

 

If the field was larger and you have access to date/time functions it might be based on the date/time the feed was imported, but 11 digits isn't enough to accommodate a formula like that.

 

If you always want as postive answer try something like:

 

=(abs(x*x/2+15))

(uses absolute value)

 

But that alone will not guarantee a unique ID.

If I suggest you edit any file(s) make a backup first - I'm not perfect and neither are you.

 

"Given enough impetus a parallelogramatically shaped projectile can egress a circular orifice."

- Me -

 

"Headers already sent" - The definitive help

 

"Cannot redeclare ..." - How to find/fix it

 

SSL Implementation Help

 

Like this post? "Like" it again over there >

Link to comment
Share on other sites

Thanks Germ

 

The only option for me at moment is to try and limit the possibilties of duplicate entried being created.

 

If only the application would allow lettes too it would make everything sweet.

 

Anyways I tried the abs you kindly provided

=(abs(x*x/2+15))

 

but I am still getting - negative values

 

for example I have sku 255608

 

if we do the above calculation for it we end up with

very long number, so I think that must be why it is showing -

 

So I dont think this can work unless we can get it to make the value after the calculation has been performed to 11 didgits

but I am not sure which of the options I could use and how to create the expression

 

Kind Regards

Link to comment
Share on other sites

If you can't take the supplier into account in the formula you might as well just use the SKU supplied in the feed.

 

If Supplier A has an SKU of 12345 and so does Supplier B you'll end up with the same number no matter what formula used.

 

You're just banging your head against a mathematical wall here...

:blush:

If I suggest you edit any file(s) make a backup first - I'm not perfect and neither are you.

 

"Given enough impetus a parallelogramatically shaped projectile can egress a circular orifice."

- Me -

 

"Headers already sent" - The definitive help

 

"Cannot redeclare ..." - How to find/fix it

 

SSL Implementation Help

 

Like this post? "Like" it again over there >

Link to comment
Share on other sites

Thanks Germ

 

The good old mathematical wall, I've been long banging my head against that wall

 

Got worse when they started throwing algebra into the equations wasn't for long time after that I discovered about the inconspicuous X find the value of X, how could you when it was different everytime.

 

Anyway as for puzzle I am finding myself in now, I was wondering if I could set a rule or code in the database using mysql or php not sure which one it would be, so when the application sends data to it and finds a same product ID already exists, it creates a unique one itself.

 

Also, if the application did allow me in future to add text and numbers for product id - would it cause any problems for the database? as this is the first thing the developers mentioned

 

Regards

Link to comment
Share on other sites

I think I have understood what you're trying to accomplish here. That is you need to make sure all products have a unique id when importing them.

 

Your attempts at using a math formula were flawed by one factor that is you're applying the same formula to each id. There is a simple math solution to give each supplier a unique id, that will prevent any duplicates.

 

Since you're dealing with a ton of products I'm going to assume they are coming to you via a CSV file so you could simply add a new column in an Excel sheet and cascade the number 1 down the column for the first manufacture

 

So you have id's like

51935

51936

51937

 

Ect.

 

Add your column

1

1

1

...

 

Then run a =(CONCATENATE,A1,B1) and cascade that down

So now you have a column with

151935

151936

151937

 

Copy that and (paste special) as text into your product ids.

 

Then on manufacture 2,3,4,5,6 etc.. do the same replacing 1 with 2.3.4. etc..

 

You'll never have the same product_id that way. I think others have stated this already but the idea is that its not 1+51935 it is 1 pre-pended to 51935.

Link to comment
Share on other sites

Thanks Germ

 

The good old mathematical wall, I've been long banging my head against that wall

 

Got worse when they started throwing algebra into the equations wasn't for long time after that I discovered about the inconspicuous X find the value of X, how could you when it was different everytime.

 

Anyway as for puzzle I am finding myself in now, I was wondering if I could set a rule or code in the database using mysql or php not sure which one it would be, so when the application sends data to it and finds a same product ID already exists, it creates a unique one itself.

 

Also, if the application did allow me in future to add text and numbers for product id - would it cause any problems for the database? as this is the first thing the developers mentioned

 

Regards

Let's be sure we're using the same terms here.

 

You're talking about the SKU number, NOT the product_id which is already in the DB and controlled by it.

 

Being the MYSQL illiterate that I am my best guess is that the SKU field in the DB should be created with the "int NOT NULL auto_increment," attributes that way all the overhead for it is taken care of by MYSQL (it starts at 1 and gets incremented by 1 at each additional entry).

 

Then again the fact that things are getting updated by feeds may throw that idea completely out the window!

:lol:

 

Like I said, I'm the LAST person you want to take MYSQL advice from....

:blush:

If I suggest you edit any file(s) make a backup first - I'm not perfect and neither are you.

 

"Given enough impetus a parallelogramatically shaped projectile can egress a circular orifice."

- Me -

 

"Headers already sent" - The definitive help

 

"Cannot redeclare ..." - How to find/fix it

 

SSL Implementation Help

 

Like this post? "Like" it again over there >

Link to comment
Share on other sites

Thank you for your kind reply

 

As you correctly say we get information in csv files but these are loaded automatically into the application that is used to upload products to the site, so if we need to pull data from supplier html or ftp location ourselves and then edit the csv file then upload onto site it is not going to be practical and for many suppliers it will be too time consuming to do every day, but say we do this first time, then the following day the supplier updates there files the suppliers file will have the newly created produuct id's so we will be back to square one.

 

The main reason for using the application is automate the whole process, so I think only option if possible would be some code on the database itself - I dont even know if this is possible or not to automatically create new unique product id whenever it finds same value already exists.

 

Kind Regards

Link to comment
Share on other sites

Hi Germ

 

The sku is provided by the supplier. I basically choose this to create the product id.

What you are saying makes sense I am going to check the auto increment.

 

I'm terrified of going into database from cPanel its ok, but the ones I use to see before the ones that run from commande prompt, just think it will all get deleted if I mess something up. I will make backup and test this see if it works.

 

I can see this

products_attributes_id int(11) No None AUTO_INCREMENT

products_id int(11) No None

 

I think the appliction must be writing to the second one, as you see the first one is already set to auto increment, so might as well try and do same for products_id too. Will this automatically create new value if it finds same value, by basically adding 1 ?

 

 

@ germ How can you be the last person to take advice from, an advice from you is truely apprecaited always really overwhelming by the time and support you provide on this forum, helping so many

 

Kind Regards

Link to comment
Share on other sites

OK

 

I tired setting the products_id to auto increment however I get this error

 

Error

 

SQL query:

 

ALTER TABLE `products_attributes` CHANGE `products_id` `products_id` INT( 11 ) NOT NULL AUTO_INCREMENT

 

MySQL said: Documentation

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

 

 

this is another message that is shown.

The indexes products_id and idx_products_attributes_products_id seem to be equal and one of them could possibly be removed.

 

When I check the products I can only see products_id and no value for idx_products_attributes_products_id

so should i take the auto increment from idx_products_attributes_products_id and give it to products_id

 

I have backed up the database so should I try this I dont know where idx_products_attributes_products_id is being used

 

Regards

Link to comment
Share on other sites

I tried making the products_id auto increment but it just add latest supplier product and deletes any previous products with matching products_id

 

Just to clarify please once I select the auto increment checkbox do i need to enter in some code or is that it done?

 

Any other suggestions please?

 

Kind Regads

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...