Auto increment based on a column value?

[Mounty]

I did a bit of searching but couldn't find anything, so:

If I have a table structure similar to this:

table: productImages

id	filename	productId	order

1	10234.jpg	12		1
2	13702.jpg	12		2
3	23674.jpg	12		3
4	98373.jpg	15		1
5	44126.jpg	15		2

Would it be possible to create an auto increment value in the 'order' column, based on the 'productId' column?

As an example, if I insert another record with filename = 12345.jpg and productId = 12, then it would set order = 4 based on the previous three entries for that product?

cheers

[corbyboy]

If I am not mistaken, auto-increment fields must also be unique. Somebody will be able to confirm or deny my suspicions.

[Mounty]

hmm probably right, ah well worth a shot


cheers

[Hierophant]

update productImages set order = (select (max(order) +1) where productid = $x) and id = $y

Would only work in MySQL 5+ though.

[r937]

Would it be possible to create an auto increment value in the 'order' column, based on the 'productId' column?

yes, it is possible

please look it up in da manual under "using auto-increment"

[Mounty]

Thanks guys you put me on the right track to figuring it out

Here's my solution if anyone is interested

INSERT INTO productImages(order) SELECT (MAX(order)+1) FROM productImages;

[r937]

no no noooooo.....

the "select max" technique has several deep flaws

and besides, you aren;t getting a new number by product, that just gets you a new number overall

did you look up the page in da manual as i suggested? they give a very clear example of exactly what you want

[BrandonK]

I love this place. I never knew you oculd do that!