Page 1 of 1
Unique identifiers
Posted: Wed Mar 26, 2008 1:36 am
by dhampson
Here's the setup: I created a small php/mysql/javascript application for teachers to create lesson plans. The program automatically generates data sheets, progress is tracked, and pdf reports generated automatically. (It sounds a little odd, but I don't want to get too long winded.)
Each lesson plan has a unique identifier. At first, the software created a new blank lesson plan, with mysql auto incrementing the unique identifier from the last one. But, people being what they are, would "accidentally" create one, and I started to get a ton of blank plans in the database. (This was actually pre-release, and I was doing all of this.

)
My fix was to ask mysql what the next identifier _should_ be. If the "back" button on the browser is pressed, no new row is created in the lesson plan table in mysql. When the lesson plan was saved for the first time, the unique identifier was saved as well. This worked fine, because I was the only one creating lesson plans for a while. (My boss had me doing all of hers.) When we hired a second person, a big flaw was noticed.
If the two of us wanted to create new lesson plans at roughly the same time, each of our separate browsers would hold the same "unique" identifier. Lesson plans could be hijacked by accident. My work around for the past 2 years has been to tell people to "create and save right away." We now have 6 people writing lesson plans, and I seriously need to figure out a new strategy.
I took a week off to write major software upgrades, so I hope I get some good advice on this soon. Thanks
--Dave
Re: Unique identifiers
Posted: Wed Mar 26, 2008 2:01 am
by timvw
Since you are talking about create and save functionality, i'll presume that it's necesarry that users can start creating a plan today and only save it tomorrow (read: they don't save the plan in the same "session").
The real problem seems to be the fact that your software can't make the distinction between "tentative" and "validated" plans. Here is a low impact solution:
(1) Add a table tentative_plans and modify your creation logic so that newly created plans end up in this table. Add functionality which allows the user to "validate" the tenative plans and then transfers the tentative_plan record into the plan table. (Notice that you do not have to alter anything in your reporting logic

)
(2) Add a last modification field to the tenative_plans, this way you can create functionality that removes the tentative plans that have not been modified for a given amount of time (eg: one week).
An (imho) cleaner design would only require one plans table, but with an additonal "state" field. As soon as you add other "states" to your system using the previously mentionned solution you would need to add a table (thus #plan tables = #states). The problem with the cleaner design is that it also requires to change your reporting logic (because it needs to take into consideration the state field).
Re: Unique identifiers
Posted: Wed Mar 26, 2008 3:29 am
by onion2k
At first, the software created a new blank lesson plan, with mysql auto incrementing the unique identifier from the last one. But, people being what they are, would "accidentally" create one, and I started to get a ton of blank plans in the database.
Why do you need to save the plan before the user edits it? Just create it the first time it's saved.
Re: Unique identifiers
Posted: Wed Mar 26, 2008 1:54 pm
by dhampson
Thanks for the advice, I think it helped be to come up with the follow solution. Please comment.
First, from the End User's perspective: After logging in, we get a pulldown list of our students. (In the field area of developmental disabilities we say "participants".) After selecting the student, we are then presented with another pulldown list, with their associated lesson plans. At the bottom of this list is an "Add New Program" selection.
Next to this pulldown list, is a dynamic table which gives selected information about the highlighted lesson plan (which we call program in this field.) This helps to make sure you've selected the right one. For the dynamic table, values is stored in a JS array, keyed to the unique ID of the lesson plan.
For "Add New Program" option, I needed a key for the JS array, and this is where I grabbed the unique identifier.
We haven't even gotten to the modify/save lesson plan yet, but the source of the problem is here. FYI, the Add to Database and Update buttons are the same. The text of the button is dynamic though, to further help users.
Finally, my proposed fix. (Please comment, I was trained on Apple][ Basic, pascal, C, and Fortran. I've learned all this from books and the web. Some other people want to buy this software, so it's important I get it right.)
Instead of asking MySQL for the highest "unique" identifier and adding 1 to it, I just use the same value, say 123. When the user presses the [Update, Add to Database] button, I then look at the identifier. If it's 123, then I ask mySQL for the ID largest (so far) and increment it by one. Now, I can INSERT into the lesson plan table.
I thought my last idea was sound at the time, does this have any obvious flaws?
Thanks for your help
--Dave
Re: Unique identifiers
Posted: Wed Mar 26, 2008 3:59 pm
by onion2k
You don't need to know the id before you insert the record. Insert it, then use mysql_insert_id() to find the id.
The basic logic is pretty simple:
1. User loads the plan editor page. If you know the id of the record they're editing put it in a hidden field, if it's a new record leave that blank or put 0 in it.
2. User edits/fills in the form and clicks Save.
3. Validate the incoming data and everything is ok. If it isn't reload the form and display any errors, info, whatever.
4. Once the incoming form is ok check the id from the hidden field. If it's 0 you use an SQL INSERT statement to save it (and thus create a new record at the same time). If the id has a value you use an UPDATE statement to update the existing record.
5. If the id was 0 you then fetch the id of the new record using mysql_insert_id().
6. At this point your record exists and you know the id whether it was a new record or an existing one, so do whatever you like.
Re: Unique identifiers
Posted: Wed Mar 26, 2008 4:36 pm
by pickle
~onion2k's pretty much got it, but I think it can be a bit simpler even.
4. A REPLACE statement can be used instead of determining whether to use an INSERT or UPDATE. A REPLACE statement will create a new row if the primary key isn't matched, and will update an existing row if it does. So, if you're creating a new lesson plan, your query will look like:
Code: Select all
REPLACE
INTO
plans
(id,
someotherfields)
VALUES
('',
'otherfieldvalues...')
An existing lesson plan would look similar obviously:
Code: Select all
REPLACE
INTO
plans
(id,
someotherfields)
VALUES
('existingID',
'otherfieldvalues...')
You should also look into doing a header redirect after a plan has been created and/or saved. Then, if the user hits the 'Back' button, they go back to the header redirect page rather than the form submission page.
Re: Unique identifiers
Posted: Wed Mar 26, 2008 4:51 pm
by dhampson
Ok Pickle, what data type do I make the ID? Currently it's a unique, unsigned, INT(10). Do I need to declare this as auto_increment as well, or is there a better way for mysql to assign unique IDs for me?
The problem I had before was there was a relatively high probability of 2 separate "unique" identifiers being generated. I like Onion2K's method (I can use If($PID){ }) but there is still a non-zero probability that 2 separate "unique" identifiers could be generated (although very small, but it's because php generates the numbers, and not mysql). I think your method reduces this to 0.
Thanks for your help
--Dave
Re: Unique identifiers
Posted: Wed Mar 26, 2008 5:09 pm
by pickle
There is zero probability that 2 rows will have the same unique ID if the primary key is set to an integer & is set to auto_increment (FYI - might as well make the column 11 digits instead of 10). MySQL handles that incrmenting & does it on a query-by-query basis. There is no chance two rows will have the same unique ID as that would violate the primary key & pretty much undermine how keys work.
mysql_insert_id() will return the last ID generated for an insert statement, on the passed connection. So, if user 1 & user 2 are both actively creating lesson plans, they'll still have different database connections & therefore if they both call mysql_insert_id() at the exact same instant, they're guaranteed to get 2 different results.
Your idea of retrieving what the ID *should* be has very limited life & will be/is likely prone to a bunch of problems. I'd drop trying to generate/guess a unique ID in PHP & just let your data source (MySQL) handle it.
Re: Unique identifiers
Posted: Sat Apr 05, 2008 9:07 am
by Benjamin
Be aware that there is a version of MySQL (5.0 I believe?) with a bug that will not update the table cache when using REPLACE INTO. This can cause you to get an out of date result on the next SELECT.