MYSQL auto inc question...

Jack99uk

Hi guys,

One of the PHP/MYSQL sites I've built uses a child_id / parent_id system for categories.

So, to put the category 'goats' (child_id=20) in the category 'animals' (child_id=2), I assign to category 'goats' parent_id=2.

This system works perfectly.

I recently added a page to the database that I didn't want to sit within any categories, so I manually assigned this page a parent_id=999 (the highest child_id at this point was 270).

Since I did that, a couple of new categories have been added by the owner of the site (using content management tools) and but rather than continue from 270 for their child_ids, these new categories started from child_id = 1000, and then 1001.

the child_id field was set to 'unique' and 'auto increment' in MYSQL, but having turned unique off, and then back on again, I've not been able to re-create the child_ids being bumped up in this way, e.g.

I changed the parent_id from 999 to 9999 and experimented with adding new categories, they followed on from the last entry at 1002, 1003 and so this time ignored the 9999 in the parent_id field. As detailed turning unique off and on had no effect.

Any ideas?

mrhappiness

Any problems? 😉

lth2h

This is really a MySQL question not a PHP question....

http://dev.mysql.com/doc/mysql/en/example-auto-increment.html
Some of the comments describe a proceedure for reseting the value. My main concern would be that if you do this, what happens when you reach 999 again. Will it be overwritten? Unless there is a compelling reason, you really should leave auto_increment fields alone.

Jack99uk

That's the thing - I am and have left the auto increment field alone.

The field I manually adjusted was 'parent_id', that isn't auto increment or unique, it picks the child_id of whatever category I assign to it.

So if:

animals is child_id=2
dogs is child_id=3
cats is child_id=4

Then dogs and cats would both have a parent_id=2;

The child_id field was ticking up automatically from 0 to 270.

I added a new category that I didn't want to have a parent category (so it wouldn't show up when users browse through the category), by giving it a parent_id of 999, but this shouldn't effect the auto incrementing child_id field.

Indeed, having tried to re-create the problem, it hasn't effected it.

mrhappiness

would you mind showing the CREATE TABLE statement?

btw: why is that a problem anyway?

Jack99uk

Here's the create table snip:

CREATE TABLE cat (
child_id int(4) NOT NULL auto_increment,
parent_id int(4) NOT NULL default '0',
label text NOT NULL,
copy text NOT NULL,
PRIMARY KEY (child_id),
UNIQUE KEY child_id (child_id)
) TYPE=MyISAM AUTO_INCREMENT=1004 ;

And the add category snip:

$sql = "INSERT INTO cat (parent_id,label,copy) VALUES ('$parent_id','$label','$copy')";
$result = mysql_query($sql);

The first field of the table is child_id, and i've not included that in the above, might that be it?

It's not a big problem, but I'd like to increase the values of the static pages (that have no parent category associated them), from 999 to something larger - in case the current number of categories (270) gets as high as 999, which is a remote possibility, but I don't want to do that if the child_id auto increment will jump to the higher number - there's no real reason why, it just looks messy and I want to understand why its happened - and why I can't re-create it.

I've been trying to re-create the problem on my local development server, which is perhaps why I can't recreate it - I'm thinking that maybe it's a problem with my code, and the live server (different PHP/MYSQL install etc.) is handling the code differently, hence the weird change that i've not been able to replicate offline?