mysql help

BeanieMan

i have a table
id, name, type
i want the id to be unique to the type but be consecutive

so for example

id name type

1 item1 head
2 item2 head
3 item3 head
1 item4 body
2 item5 body
3 item6 body

how would i do this please?

Ashley_Sheridan

I think you meant you want the name field to use consecutive increments?

Why are you doing this? There's no default way to do this in MySQL (I'll make that assumption here as you didn't even say which DB you're using) . Explain a bit about why you want to do this, as someone will probably be able to offer a better way of doing what you need.

BeanieMan

Ashley Sheridan;11003090 wrote:
I think you meant you want the name field to use consecutive increments?

Why are you doing this? There's no default way to do this in MySQL (I'll make that assumption here as you didn't even say which DB you're using) . Explain a bit about why you want to do this, as someone will probably be able to offer a better way of doing what you need.

hi, thanks for your reply..

yes im using mySQL, the name field is not consecutive, they are just examples.
I want id to be unique to with the type field because i need to be able to select the row by $currentID +1

so for example the player already has id= 1, name= item1, type= head..
now i want to give them the next item with the type= head
so SELECT * FROM <table> WHERE id= $currentID +1 AND type=head

so to clarify my question, i am trying to find a way to insert the items in the 1st place.
As you say there is no default way to do this in mySQL i guess i need to just SELECT MAX(id) WHERE type=head
before i insert

Ashley_Sheridan

I still don't get what you're trying to achieve here. Type looks like some kind of text field, not something which can be incremented at all.

At the moment, the example you have would give you a unique key across the id and type field, and you can enforce this by adding a unique index to the table that includes those two fields. The id would remain the primary (and I assume uses an auto_increment?)

Weedpacket

Further to what Ashley Sheridan says, there's nothing here that couldn't be achieved by (a) using a WHERE clause to filter queries by "type", and (b) using ORDER BY on a (table-unique) id field. You can throw in "AND id>$current_id" and "LIMIT 1" clauses too, if desired.

Unless "SELECT * FROM this_table WHERE id=3" is supposed to be meaningful, the design above breaks Second Normal Form.

vivek151189

Divide the work in different table depending upon type

tomhath

As I understand it you want to find the sequence number for the next row of a given type, something like this (untested):

insert into theTable (id, name, type)
select   max(id) + 1,   'itemxxx',  type
from theTable
where type = 'head'
group by type

Ashley_Sheridan

tomhath;11003347 wrote:
select   max(id) + 1,   'itemxxx',  type

Please don't ever do this. While it may appear to work fine during testing, said testing is usually only done by one person at a time. As soon as the script gets hit by several users at once, then you've got a race condition on your hands, and you'll likely end up with lots of mismatched records.

If you ever need to increment a field, use the auto increment feature of the database. If you have more than 1 field that needs incrementing, you probably need to look at splitting that data to a secondary table and normalising it.

Derokorian

http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table.

So there should be no fear of a race condition IMO. You can further reduce this fear by using transaction safe table engines (such as InnoD😎 and, obviously, transactions.

Weedpacket

vivek151189 wrote:
Divide the work in different table depending upon type

Ugh, horrible idea! An entire new table every time a type is added? Rewrite all the queries to use unions of these tables? Rewrite them again every time the list of types changes?
Horizontal partitioning has its uses, but this isn't one of them.

tomhath

Ashley Sheridan;11003350 wrote:
As soon as the script gets hit by several users at once, then you've got a race condition on your hands, and you'll likely end up with lots of mismatched records.

Agreed that locking should always be considered in a multi-user environment. I thought about autoincrement, but then things get complicated if you ever need to rearrange the rows. To me an autoincrement column is a unique key to that row with no other meaning.

Weedpacket

One potential problem with consecutive ids per type is that if an item is deleted it can leave a hole in its corresponding IDs ([1, item1, head], [3, item3, head], ...). If consecutiveness is needed, all the subsequent items will need updating.

If the actual value of the ID is significant, then I think it should be supplied explicitly, and to not rely on the database picking the right one. If all that matters is the ordering, then there's no need to require each type to have its own sequence of IDs independent of the others.