Add id number based on row number

[richrock]

Hi all,

 

Having a little difficulty in modifying a php script, and I need to do the following - when an entry is inserted into the database, I need to generate an id number (call it item_num) based on the category number, so I can display the item numbers per category, not using the main id number...

 

So if I already have 20 items in category 1, and 15 in category 2, when I add another item to category 2 it has an id of 36, but also an item_num of 16.

 

I'm guessing this is done in the mysql command, but I'm a newbie at this (learning fast tho!) so any help is much appreciated.

 

Rich

[jana]

can u describe u r table structure

[richrock]

No probs -

 

id, userid, title, description, start_date, end_date, published, messages, blocked, cat, modified.

 

id is Auto-increment, start_date, end_date and modified are all datetime, the rest are varchar's of various lengths.  cat is inserted via $cat_id on the php side.

 

I want to add another row - call it item_num or something like that, and when new items are inserted into the DB, the cat_id would increment according to the cat number - as in my previous post.

 

This might be done in PHP - wasn't sure but thought I'd try the mysql section first.

 

Rich

 

[GingerRobot]

That's not the kind of thing you should be looking to store - just something to calculate when you should need it:

 

SELECT COUNT(*) FROM tbl WHERE cat=2

[gluck]

richrock: Delete an entry and you will end up screwing the logic.

 

[richrock]

I suppose this would be easier in a separate table - to have the cat number, id number and generate another item_id separately?

 

Just thinking out loud...  Part of the problem is is that I am modifying an existing software solution, which can be easily done.  It's just a requirement of the end user to have a separate item id based on category.  I could try inserting an ID by getting the cat number first, then generating the number based on any existing numbers already in that row. 

 

But would I be able to use auto_increment?

 

Rich

[fenway]

But would I be able to use auto_increment?

I'm confused... why can't you let the DB generate these numbers for you?

[richrock]

Sorry to re-open this, I got involved with other aspects of this project, but this has reared it's ugly head in a different way :

 

The client has now stated receipts, along with line numbers.  So my structure for the table is :

id, receipt_num, lot_num, lot_id, line_num, date.  lot_num is their reference number, and lot_id is the original table id from the other table.  So I need to have for example, recp_001 with 5 lines, so line_num will be 1 - 5, the recp_002 with 3 lines (1-3) and so on. 

But these may also be added after the date (receipts are kept open) - so I may go back to recp_001 and add line 6, but in the table it would be after the recp_002 lines.

 

I understand the process - add the line, get the last line_num, and then increment the value of line num by 1.  It gets a little tricky for me when they will be all over the place.  I'm thinking along the lines of "select line_num where receipt_num = "$receiptvalue"" then counting for the next one.  But being a complete n00b....  :-\

 

 

Confused?  I am!!!  If anyone can help me make sense of how to do this it would be great.

 

Rich ???

[fenway]

You could have an auto-increment across both columns... but otherwise, yes, get the MAX(line_num) for a given receipt_num, then add one -- this can be done in a single statement

[Barand]

richrock: Delete an entry and you will end up screwing the logic.

 

 

I agree with gluck. It's a recipe for disaster.

 

You could make the primary key (cat, id) then if id is auto_inc, it will do it for you and is safer than using MAX.

 

But even this way it won't give a count if items are deleted, but it will stop numbers being reused.