Little DB Neophyte with BIG DB Problem

Maybe you can help me find out what's going on

[neophyte]

Hello All -

Let me preface the following by saying that, even though I've been doing DB-driven sites for a number of years, I would hardly consider myself more than barely competent regarding the ability to properly structure a mysql database. I've learned what I know strictly from the net and the good folks here at WebmasterWorld. The projects I've done previously were simple "content" databases - primary key field for a section/page index and a "content" field for, well, simple text content.

A few months ago, I got a client who needed a site that would accept fairly extensive job application data - personal detail, position information, background info, CV stuff, etc., etc.

I built a 7-page form section to accept this information, tested it to death and everything finally works fine. Then I built a mysql DB to hold all submitted information. The DB includes 7 individual tables for each of the 7 pages of information, tested it to death and finally everything worked fine. I completed the job, then handed the application over to the client who then hired 5 people to encode over 500 hard-copy resumes that had come in during the time of development.

I'd call the client every day to make sure everything was working properly - no problem.

Then, I got a call last week saying that two applications that had been encoded didn't show up. I looked at the live DB via MyPhpAdmin, and - sure enough - there were rows for these two applications in each table, but the fields for each row (with the exception of the Primary and Foreign Key fields) were ALL empty.

I deleted each empty row, called the client back and asked him to re-encode those applications. He did, and no problem: everything was there. Hummm.

Just today, the same thing happened... and I don't know where to begin tracking this problem down.

As mentioned, I've tested the PHP code over and over and over and each time I put in "Sample" application data, it's all there.

So... what could be causing this? Could it be that:

1. the encoders are accidentally pressing the back or refresh buttons on the browser somewhere within the form section during encoding? If this is the case, should I be using a header-redirect when taking the user from one form page to another - I'm not right now.

2. Are more than one encoder submitting an entire completed application at the exact same time and two records are trying to be added to the same row at the same time? If this is the case, do I need to lock the tables on an add/update? - Never done that before, but have read about it.

3. Am I using the correct table type for this DB? Right each table in this DB are MyISAM.

What else could I be possibly overlooking that could cause this issue?

I'm more than happy to post my INSERT code if anyone thinks that could be the problem - or anything else ANYONE would like to see to assist with this issue.

HUGE thanks to any and all in advance!

Neophyte

[coopster]

1. The back buttons in the browser may cause issues with POST data, so yes, redirection is always a good conceptual model to follow in posting multi-page form data. Storing data on the server via session or some other method is also wise. Of course, you could always carry it from page to page in the forms themselves but that is a lot of bandwidth consumption. The good part about session management is that the data is stored in a temporary solution on the server until the form is complete at which point you write out your entire transactional data in a single connection, which means there is no chance of trouncing on another's primary key data across the relational tables.

2. No, this should not be an issue, but that depends on how you have written the code. If you are using AUTO_INCREMENT on the primary table for inserting the first record and then using the LAST_INSERT_ID from that table to write to the other tables you should be fine. Once again, this process works per connection so there is no way anybody could trounce on another's data.

3. MyISAM can work just fine in these situations.

I am guessing you have a logic issue somewhere. Somehow, one or more of the form page data is being purged before an attempt to write the records is complete. To insure that all data is complete you could run your final edits one last time before your initial INSERT is attempted. Use your normal form process/edit logic and if a certain field/column is empty or invalid, redirect to that form page.

[neophyte]

Coopster -

Thanks so much for your reply. To respond to your answers:

1. I'll try php's header("Location") function to see if that fixes the problem. When a user goes to the next page in a form set (or goes back to a previous page) I am already storing each page's post data in session vars for safe-keeping. When the user lands on the "thank you" page for the form-set, a few final "data-checking" functions are run on each session var which is then followed by the insert.

2. The primary key of the first insert table is set AUTO_INCREMENT and then I'm using mysql_insert_id() to set the foreign keys of each additional table like this:

$sql = "INSERT INTO
tbl_a //Primary key on this table is set to auto increment
(
fld_first,
fld_last,
fld_middle,
fld_vision,
fld_height,
fld_weight,
fld_hair,
fld_eye
)
VALUES
(
'$dbaseData[First]',
'$dbaseData[Last]',
'$dbaseData[Middle]',
'$dbaseData[Vision]',
'$dbaseData[Height]',
'$dbaseData[Weight]',
'$dbaseData[Hair]',
'$dbaseData[Eye]'
)";

$stat = mysql_query($sql,$db);

checkResult($stat, $sql);

$crewId = mysql_insert_id();

$sql = "INSERT INTO
tbl_b
(
fld_id_FK,

fld_skill,
fld_vesselExpTypes,
fld_position
)
VALUES
(
'$crewId',

'$dbaseData[Skill]',
'$dbaseData[VesselExpTypes]',
'$dbaseData[Position]'
)";

$stat = mysql_query($sql,$db);

checkResult($stat, $sql);

Thanks again for you input...I sincerely hope that the error of my ways was not initially using redirect. I'll be back if anything else crops up.

Neophyte

[coopster]

and then I'm using mysql_insert_id() to set the foreign keys of each additional table

I would advise against this practice. There is a note on the mysql_insert_id [php.net] function manual page that is worth heeding. Search [google.com]over the PHP forums too and you will see that I mention this quite often and you will find the workaround too.

And what is your

checkResult

function doing? I am very cautious about running any functions against the resource until I have first grabbed the LAST_INSERT_ID() value and stored it locally in my script. Of course, you have to be sure the query executed successfully, but after that you are wise to grab and store that new AUTO_INCREMENT value before running other query statements etc. if at all possible.

[neophyte]

Hello Coopster -

Thank you for the follow-on advise. I'll search your threads regarding this issue to see how I can implement the LAST_INSERT_ID() query rather than the PHP equivalent.

I did fix my forms validation work flow to trigger page-changes with Location (no more awful "undefined index" warnings when you hit the back or refresh buttons!) but the same problem still (occasionally) happens where the client will call me up regarding blank records which really has me scratching my head.

Maybe I've allowed a text field to slip through without escaping it in real_escape_quotes (I HAVE been very careful about this, but maybe there's one that I missed) and the encoders are using an occasional apostrophe in a comments field or something. Or, maybe it's this mysql_insert_id thing that is throwing an unexpected wrench into things.

This is driving me a bit crazy, but I'm looking forward to finding the right solution. To that end, I really do appreciate the extra advice Coopster!

Neophyte