Pica Posted August 8, 2003 Share Posted August 8, 2003 Hi... 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 : ABCCCDDDDEEE 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: So... 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. P. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 8, 2003 Share Posted August 8, 2003 I could be wrong, but I think you need to change INT(12) to VARCHAR(12). Try & see? Quote Link to comment Share on other sites More sharing options...
Pica Posted August 8, 2003 Author Share Posted August 8, 2003 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. :D P. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 8, 2003 Share Posted August 8, 2003 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. Hth, Matt Quote Link to comment Share on other sites More sharing options...
Pica Posted August 8, 2003 Author Share Posted August 8, 2003 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 :D P. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.