Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

inscrutable catalog and its numbers


Medworks

Recommended Posts

Posted

Does anyone know where exactly the catalog and or order information is actually stored, and how to modify them directly? Or if not, this is what I'm looking to do, and any way to do it would be appreciated:

 

I have noticed that when I add a new product to the catalog, it assigns it a number. Like let's say, 307. And to order item 307 from my website, you could go here:

 

http://medexamtools.com/oscommerce1/product_info.php?products_id=307

 

But let's say I delete item 307 and then I add a new item again. Surprise surprise, it's 308! The original item assigned to the number 307 is deleted all right, but what if I don't WANT my database of items to skip the number 307. How do I assign something to the number 307?

 

The same sort of thing happens with an order. If I test some new module or modification with a fake order, maybe I'll get an order number of 222. And if I delete my order, it's gone, but the next order will be assigned the number 223. How to I set the counter back to 222?

Posted

Such fields are "auto_increment" in the database definitions. When you add a new row, the next highest value is used. MySQL is designed not to reuse a value, so if you've created a record with field value 307, and then erased it, the next will be 308, even though a 307 does not exist.

 

If you can't stand that behavior, you should be able to go into phpMyAdmin, to the SQL tab, and use a SQL command to change the Auto_Increment value to whatever you want. I think it's something like

ALTER TABLE tablename Auto_Increment = 307

but I won't swear to it. It might be best to do some searching on this forum and on the MySQL reference manual before trying it...

 

Note that this is a one-shot deal. If you delete the latest record again, you'll have to repeat this SQL command (with the new value). If right in the middle of that, some customer action creates another new record, doing this will really screw up your data (duplicate field values), so be very careful -- at the least, disable the application so that there's no database activity going on, and use SQL commands to double check what the highest value in use is.

Posted

Such fields are "auto_increment" in the database definitions. When you add a new row, the next highest value is used. MySQL is designed not to reuse a value, so if you've created a record with field value 307, and then erased it, the next will be 308, even though a 307 does not exist.

 

If you can't stand that behavior, you should be able to go into phpMyAdmin, to the SQL tab, and use a SQL command to change the Auto_Increment value to whatever you want. I think it's something like

ALTER TABLE tablename Auto_Increment = 307

but I won't swear to it. It might be best to do some searching on this forum and on the MySQL reference manual before trying it...

 

Note that this is a one-shot deal. If you delete the latest record again, you'll have to repeat this SQL command (with the new value). If right in the middle of that, some customer action creates another new record, doing this will really screw up your data (duplicate field values), so be very careful -- at the least, disable the application so that there's no database activity going on, and use SQL commands to double check what the highest value in use is.

 

Sweet. A one shot deal is fine, since it looks like that means I can fill in the gaps from a ways back if it just involves changing the current counter to any number I choose. Well, I would only have to worry about customers coming in the middle if I'm screwing with the order numbers, not if I'm screwing with the product numbers. It's not like some customer is going to come in and add a new product to my catalog or something! Or are you saying this tablename Auto_Increment thing is only for the order numbers? I'm hoping to do both actually, but the more important is the products. Well, I never really understood what I was doing in the MySQL database so I may not even be able to find this thing in the first place, but just knowing that's what I'm supposed to use is a good enough hint, so thank you.

Posted

Sandor,

 

Tables such as products, orders, categories and more use the auto-increment command. As Phil said, you COULD change the increment number but it may also effect OTHER ID's or the correlation to the other ID's. IMO, don't mess with it, it really makes no difference if you have a product ID of 7 and then 20, as long as the cart functions properly.

 

Chris

Posted

Sandor,

 

Tables such as products, orders, categories and more use the auto-increment command. As Phil said, you COULD change the increment number but it may also effect OTHER ID's or the correlation to the other ID's. IMO, don't mess with it, it really makes no difference if you have a product ID of 7 and then 20, as long as the cart functions properly.

 

Chris

 

Did Columbus say "No, we don't need to find the Northwest Passage, it's good enough to go around the southern tip of Africa"? Did the Wright Brothers say "Hot air balloons are good enough for us"? And did Nick Holonyak say "sure, I could tweak a semiconductor diode to emit visible light, but we already have the incandescent light bulb and the gas discharge tube so why bother"? If everyone thought like that, no one in the history of the human race would have even bothered to ponder the question, "should we try to throw rocks at the buffalo or just keep beating them to death with our bare hands"? That's a loser's attitude. It's in my list of mindsets to never have, right under "well, the casino took all my money away, but I'm sure it's making someone happy even if it's not me".

 

Well, I couldn't find this counter. I don't see where it is defined, I don't see it anywhere, I don't know exactly what it looks like, wherever it is. But I did find, under "phpMyAdmin", the 3 categories: "products", "products_description" and "products_to_categories". And after some experimentation determined that if you change the product ID number desired in EACH of those 3 things (and you can even fabricate "products_date_added", which is a field under "products", so that it chronologically fits in the gap), you can fill in any gaps you want. It will be screwed up if you don't change ALL 3 though. Of course, this mysterious counter will end up too high at the end; if you take the last 20 products and fill in 20 gaps, the next product you add will by default be assigned a number 21 higher than the previous highest number in the catalog. So I'd like to fix that anyway.

Posted

True enough. You can change any value so long as you're careful to change all uses/references of it, and you're careful to lock out everyone else while you're fooling with the database. That said, I'm not sure why you would care that there are gaps in sequences (optionally insert wisecrack about OCD here). Most auto-incremented IDs can go up to 2.1 billion or so, there not being much danger of running out of numbers. It's also one thing to back up and reuse the highest value(s) of a counter with real data, but it's risky to try to fill in gaps -- you have to be very careful about not letting an ID get reused (there is no code check for that!).

Archived

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

×
×
  • Create New...