[RESOLVED] Help creating separate associated records in a given script

Chris_96_WS6

I work for a large medical center, we submit a lot of grant applications to the federal government.

Each of these applications comes into my dept, where we process certain kinds of information. There are multiple personnel on each grant, each of whom need to submit certain paperwork to my office.

I have developed an administration tool for all of this. the application information is keyed and stored as a mySQL record. I want each person to be stored as a separate record in a separate table, but with an identifier to link each person record back to the grant application record.

This all needs to be done off of one submission form, rather than in multiple steps.

So right now, before my PHP script inserts the new grant record, it goes in and queries the index field and finds the record with the highest index #, then adds one. This is then an assumption of the value of the index field for the new grant record we're about to insert.

So then we insert the new grant record, and then we insert the persons (parsed from a text area field) each as a new person record, with a field for the grant record index field so you can tell what person goes with which grant (also because the same person can be on dozens of grants, so we may have dozens of person records with the same name).

The problem is this only works about 1/2 the time. I think what is happening is where I may have gaps in the grant index key values due to deleting records, etc, the calculated index key value isn't always the actual value once the grant record is inserted. I assumed auto_increment always added one to the highest existing value, but it doesn't appear to work that way. I need a way to index both tables that is infallible. Looking for other ideas.

here's my code:

// get number of rows in grants "Items" table
$queryROWS = "SELECT LogIndex FROM Items ORDER BY LogIndex DESC LIMIT 1";
$resultROWS = mysql_query($queryROWS) or die ("Error in queryROWS: $queryROWS. " . mysql_error());
$rowROWS = mysql_fetch_object($resultROWS);
$CurrentLogIndex = $rowROWS->LogIndex;
$LogIndex = $CurrentLogIndex+1;

// add new grant record to 'items'
$query = "INSERT INTO Items (PI, Unit, Sponsor, Title, Work_Type, CurrentStatus, NAD, Next_Action, Due_Date, Due_GCM, GA, Comments, Prop_Num, Opp_Num, Entry_Date, LogIndex) VALUES ('$PI', '$Unit', '$Sponsor', '$Title', '$Work_Type', '$CurrentStatus','$NAD_Date', '$Next_Action', '$Due_Date', '$Due_GCM', '$GA', '$AsComments', '$Prop_Num', '$Opp_Num', NOW(), '')";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

// add additional personnel as new records to 'add_persons'

// set counter to zero
$i = 0;

// split the phrase by any number of commas or space characters,
// which include " ", \r, \t, \n and \f
$keys = preg_split("/[\s,]+/", "$Personnel");
$num = count($keys);

while($i < $num) {
    $name = $keys[$i];
    $queryPERS = "INSERT INTO add_persons (PerIndex, person_id, LogIndex) VALUES ('', '$name', '$LogIndex')";
    mysql_query($queryPERS)  or die ("Error in queryPERS: $queryPERS. " . mysql_error());
    $i++;
    }

Chris_96_WS6

Well, duh...

I just realized on option is that instead of auto-Incrementing the index key for new rows in the grants table, I can use the calculated value there too...

Still not sure that is ideal but I may test it at least.

Roger_Ramjet

This WILL NOT WORK - despite all the people that will tell you that you can predict the next id to be issued, you can not - for all sorts of reasons.

The simple solution is to insert the grant record and then use mysql_insert_id() to retrieve the id allocated so that you can then use it in the person records. Simple, efficient and above all, infallible.

Chris_96_WS6

Its working now because instead of predicting, I am actually setting/forcing the next value. I agree though it is still not ideal.

The reason I'm not inserting the grant record first and then retrieving the id is that I wasn't sure it could do all this in one script given that it would all be occuring nearly instantly.

Then there's the other, perhaps more legitimate concern, is that if two records are submitted nearly simultaneously by different users, and the first goes to get the insert ID right after the second one has just done the insert, then it would retrieve an incorrect result and the person records would not be associated correctly.

Sxooter

Chris 96 WS6 wrote:
Its working now because instead of predicting, I am actually setting/forcing the next value. I agree though it is still not ideal.

Much like putting a revolver to your head with one bullet in it, and pulling the trigger and not having it go off. That's how it's working right now.

Eventually, if you have any appreciable load, it will go wrong, and you will have incorrect data in your database.

Now, if you had sequences, like Oracle and PostgreSQL have, you could select the nextval from the sequence and use that safely. But you don't have sequences, and the way you're doing it is open to a race condition.

Chris_96_WS6

So what's your suggestion, same as Rogers?

Sxooter

yep.

Let me explain a bit why your way is a bad idea. Let's say we have two connects, C1 and C2.

C1: select max(id)+1 from table
C1: Insert into childtable using max(id)+1
C2: select max(id) from table
C1: Insert into master table using max(id)+1
C2: insert into childtable using max(id)+1
C2: insert into master table using max(id)+1 <- get an error on violation of unique primary key.

Note that with innodb and transactions, you can do this due to the way innodb locks the table during aggregates, but your performance with this method will be WAY below optimal.

Chris_96_WS6

Right, I realize there is a possibility for an error during simultaneous use. The main thing was to get it functioning first. I'll piddle with mysql_insert_id() some...

Thanks for the suggestions 🆒

Chris_96_WS6

Ahhh...I see that mysql_insert_id() is connection dependent, so another user with another connection is not relevant.

Just tested it, 'tis working perfectly. 🙂