Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

CHANGING field : products_id INT(12)


Recommended Posts



I looked around to see if I could find something on this before I post but... NADA !!!


Here's my problem :


I have a huge product database... some 15,000 items with photos, etc...


It would best suit my needs to change the products_id field in order to input my own references instead of some abstract auto_increment value.


My reference is built on 12 characters wich are structured as follows :




A - country of origin

B - supplier code

C - manufacturer code

D - product category code

E - product index


(Yeah, I know it may seem weird but... I am sure I will never have more than 999 items in the same category, from the same manufacturer, from the same supplier....)


The question is : I am able to upload with phpMyAdmin various records to both products and products_description tables as well as to products_to_categories BUT... way too often I get this error message saying :


Duplicate entry '2147483647-1' for key 1


The thing is : there is NO duplicate value in my SQL code. Plus, the "products_id" for the record is :


INSERT INTO products_description VALUES (121751113001, 1, 'blah,blah,blah...', 'PROD_REF', 0);


BUT I get this in the DB :


2147483647   -   1   -   blah,blah,blah...   -   PROD_REF   -   0


I just can't figure out where does that 2147483647 comes from !!! :crazy:




Does anyone know IF and WHAT are the restrictions for the products_id field ?!


Could I use 12 alphanumeric characters in stead if numbers ?


Any help on this would be appreciated !!!


Thx in advance.



Link to comment
Share on other sites

Thanks for your reply...


I have been doing quite a lot of mods to the initial snapshot and... quite frankly... I am really happy with the results !!!


I did changed the products_id to VARCHAR(12) everywhere in the database structure BUT... I noticed osC started to act strangely after that.


I can't remember exactly what/how but... I guessed it should remain a number value so things wouldn't mess up. On the other hand, I have also installed and adapted (... this is me !!!) the "domain_search" contribution which requires the auto_increment parameter to work as I need it.


The only thing bugging me is really that INT(12) thing...


Feel free to post more ideas.





Link to comment
Share on other sites

The product_id is intended to be a unique number identifying the row. If you need a different identifier for the product that is string based, the best thing to do is to use the products_model field as that is what it is designed to do.


Instead of inserting a product_id value, you are better off inserting a null and letting the database assign a product_id (with the auto-increment). Or if it already exists, update instead of inserting.


2147483647 is MAX_POS_INT for a 32 bit signed integer. This will appear anytime you try to insert a value equal to or greater than it in the database, because it is the largest positive number that PHP understands natively. In other words, any time your number is more than 10 digits and most of the time when it is 10 digits, it will exceed PHP's base capabilities.




Link to comment
Share on other sites

GOT IT !!!


Here's why (from http://www.mysql.com/doc/en/Numeric_types.html)



For example, the range of an INT column is -2147483648 to 2147483647. If you try to insert -9999999999 into an INT column, the value is clipped to the lower endpoint of the range, and -2147483648 is stored instead. Similarly, if you try to insert 9999999999, 2147483647 is stored instead.


If the INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift up to 0 and 4294967295. If you try to store -9999999999 and 9999999999, the values stored in the column become 0 and 4294967296.




So I just need to use BIGINT in stead...


Type Bytes From To

TINYINT 1 -128 127

SMALLINT 2 -32768 32767

MEDIUMINT 3 -8388608 8388607

INT 4 -2147483648 2147483647

BIGINT 8 -9223372036854775808 9223372036854775807





Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...