i need to set up a rather complicated system using mysql. my idea is sound, but the implementation is quite tricky.. i was wondering if anybody knew some kind of trick or knows how to deal with a problem like this..

i plan on using two indexes to organize the data. both will be set to AUTO_INCREMENT, except that one has to depend on the other. that might sound a bit confusing.. so heres a little example:

COL1 COL2
1 1
1 2
1 3
2 1
2 2
3 1
3 2

in general, each item in COL1 has a unique value in COL2, and both are set to auto_increment. i can't figure out a way to implement this, though.

if you still have no idea what i'm saying, reply to this and i'll get into the specifics.

please help. thanks! :)

You can't use more than one auto increment field in a table (you are using MySQL right?). But, to maintain integrity, I think you should use a primary key that spans those two fields (primary key (col1, col2)).

It is not clear what you are trying to do though. I might be able to help you more if you give me more information.

thats impossible how you are trying to do it. Mainly since there can't be 3 1's in an auto_increment column. You have to do some sort of function outside of mysql to place those numbers in there I would think.

I'll take a stab at this. I did something similar to keep track of different types of ticket numbers for a request system by using a table to keep track of my sequences.

Create a table like this:

Table name: counter
field: counter_name char(10)
field: sequence int(10)

Initialize the table with the first record record (col1,0).

Then any time you need to increment the col1 counter use the following sql code.

UPDATE counter
SET sequence = LAST_INSERT_ID(sequence+1)
WHERE counter_name = 'col1';

SELECT LAST_INSERT_ID() as col1_counter;

Any time you increment the col1 counter you will need to add a record to the table to track the col2 values. You'll need to build the counter_name in whatever language you're using. I'll assume PHP.

$col2_counter_name = "col2_" . "$col1_counter";

INSERT INTO counter
VALUES ('$col2_counter_name',0);

Then to increment the col2 counter.

UPDATE counter
SET sequence = LAST_INSERT_ID(sequence+1)
WHERE counter_name = '$col2_counter_name';

SELECT LAST_INSERT_ID() as col2_counter;

What that should do is give you a table with a record called col1 that has the current col1 sequence and others records called col2_1, col2_2, col2_3, etc that has the current col2 sequences.

Hopefully that makes sense and hopefully someone that is better at this than me will check my logic :stickout

Richard

that's not a bad idea, but if a row is deleted, I want it to continue incrementing the numbers.

to organize the data, i want to use two columns (for simplicity I'll call them "super" and "sub"). neither column will be UNIQUE, but there would be unique items in "sub" for each unique item in "super". i want to have it so that if a new row is added, it is given a unique "super" if none is specified, or an incremented "sub" if a "super" is specified. i know there isn't a built-in way to do this, but how should i set up the table so that the script has to do the least amount of work?

I personally wouldn't use a scheme like that. I would try to split it into two tables, like this:


create table parts (
parts_id int unsigned not null auto_increment,
parts_desc varchar(200),
primary key (parts_id)
);
create table subparts (
subparts_id int unsigned not null auto_increment,
subpart_of int unsigned not null,
subparts_desc varchar(200),
primary key (subparts_id)
);


That subparts.subpart_of is a foreign key to parts.parts_id. Another advantage here you can have fields that describe the parts.

This won't give you what you want, but it is the right thing to do to better manage the data.

if ($super) { # user provided value for $super. get the next unique sub for that super
$sub_counter_name = "sub_" . "$super";
$sql = "UPDATE counter
SET sequence = LAST_INSERT_ID(sequence+1)
WHERE counter_name = '$sub_counter_name'";
$result = mysql_query($sql, $connection);
$sql = "SELECT LAST_INSERT_ID()";
$result = mysql_query($sql, $connection);
$sub_num = mysql_result($result,0,0);
$sql = "INSERT into super_sub_table
VALUES ($super,$sub_num,'$other_stuff')";
$result = mysql_query($sql, $connection);
} else { # no value given for $super. get the next unique super and create new sub counter
$sql = "UPDATE counter
SET sequence = LAST_INSERT_ID(sequence+1)
WHERE counter_name = 'super'";
$result = mysql_query($sql, $connection);
$sql = "SELECT LAST_INSERT_ID()";
$result = mysql_query($sql, $connection);
$super = mysql_result($result,0,0);
$sub_counter_name = "sub_" . "$super";
$sql = "INSERT INTO counter
VALUES ('$sub_counter_name',1);
$result = mysql_query($sql, $connection);
$sql = "INSERT into super_sub_table
VALUES ($super,1,'$other_stuff')";
$result = mysql_query($sql, $connection);
}


Maybe?

Richard

If you really want to do the logic within the application (like some other posts say), be sure to lock the tables before any transaction!! (and of course, release it afterwards)

Failing to do so will make a mess whenever queries don't arrive in the expected order, as the case when more than one client using the application simultaneously.

I agree with what you are saying about locking if you have mutiple database transactions in a logical transaction. However, with the code that I posted above each logical transaction is a single database transaction, so locking should not be necessary.

That being said, I also have not tested it, so........:cool:

Also, you should probably add some error checking in there :D

Richard

Originally posted by Noldar
I agree with what you are saying about locking if you have mutiple database transactions in a logical transaction. However, with the code that I posted above each logical transaction is a single database transaction, so locking should not be necessary.


Well... no... :D

You still need to use lock tables. What if another query on another session get executed between two queries? That still can happen if you don't use lock tables.

That's true, but in this case it doesn't matter. Locking tables is only needed if you have mutiple database transactions within a logical transaction. If I were to do something like this:


$sql = "SELECT * FROM counter WHERE counter_name = 'super'";
$result = mysql_query($sql, $connection);
$row = mysql_fetch_object($result);
$super = $row->sequence + 1;
$sql = "UPDATE counter
SET sequence = $super
WHERE counter_name = 'super'";
$result = mysql_query($sql, $connection);


I'd better darn well put locks around that or I'll be asking for trouble. In the above I am reading a value from the database, changing the value and then updating. 2 database transactions that make one logical transaction.

However, the following code does everything with one database transaction:


$sql = "UPDATE counter
SET sequence = LAST_INSERT_ID(sequence+1)
WHERE counter_name = 'super'";
$result = mysql_query($sql, $connection);


This retrieves the value and updates in one step. There is no chance for another query to interfere. I already have my unique counter, so any other query that occurs will also get a unique counter. This next step:


$sql = "SELECT LAST_INSERT_ID()";
$result = mysql_query($sql, $connection);
$super = mysql_result($result,0,0);


Simply retrieves the value from the LAST_INSERT_ID() function and does not actually query the database.

Same thing is true for the sub counter.

The insert into the super_sub_table also does not require a lock since the super+sub key will always be unique and it by itself is a complete logical transaction.

You'll have to break it to prove to me that it won't work :D

Richard

I guess you are right. I wasn't aware that LAST_INSERT_ID do that thing since last time I looked at MySQL manual :D.