Welcome to WebmasterWorld Guest from 34.207.78.157

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Empty DB Tables - What could cause it?

An on-going mystery

     
1:32 am on Aug 26, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


Hello All -

I've posted a similar question in the DB forums and have gotten some clues but wanted to cover all of my bases so I'm reiterating my question here.

I've built a fairly large php application for a client which accepts job applications. The actual application section - which a job candidate would fill-in - is 7 pages long.

During pre-deployment testing, I hammered on this section to death and finally got everything working: when all required fields were filled, it would go to a "thank you" page which would then process all submitted details (all held in various $_Session vars) using mysql_real_escape_string(), and then insert the data into each of seven different tables in a mysql database. If non-required fields were left empty, a zero was placed into those database fields.

When I deployed the application, the client had hired a number of encoders who began - using the application forms I built - inserting a backlog of paper applications into the database.

Everything appeared to be going fine until I got a call from the client saying that - after the first 27 or so applications had been encoded - there was one DB record that was blank: the primary key and foreign keys for all seven tables were there, but all other fields in all seven tables (for this single record) was BLANK - no zeros - just blank.

I deleted that record across all 7 tables and asked the client to continue encoding. All was well again for a week or so, then it happened again - one record (across all seven tables) for one applicant was blank except for the Primary and Foreign Keys.

Since this has happened, I've altered the coding to ensure that when a user (or encoder) goes from one application page to another, header("Location: "); presents the next page so if the user presses the backward/forward/refresh buttons on the browser, nothing gets screw-up. Then - from a suggestion from the DB forum - I've stopped using the mysql_insert_id() function after data insertion into the first of the 7 tables in favor of LAST_INSERT_ID() to determine the proper FK to enter for the next 6 tables.

After all that, the problem is STILL happening - but only occasionally and without any hint as to what's causing it.

*****************************

Here's a quick summary of the workflow.

1. Seven pages of form data: check boxes, select lists, single-line text fields, multiple-line text fields
2. When user goes to the next page in the section, all form info is validated and, if successful, all information is stored in $_Session vars for that page
3. Transitioning from one form page to the next is handled via header("Location: ");
4. Local testing confirms that all form values submitted from the previous page IS captured in the appropriate $_Session vars
5. On the final (Thank You) page, all $_Session Vars are processed (empty non-required field values are set to zero "0") and text input values are escaped using mysql_real_escape_string().
6. After all data had been processed, an INSERT is preformed on the 1st of the 7 DB tables
7. After data had been inserted into the first table, the last inserted ID would be captured using LAST_INSERT_ID(); that value is then used for the FKs of the next 6 tables.
8. After the full insert has been completed, an email would be sent to both client and applicant notifying each of the submission of the application.

Database is MYSQL
All tables indicate: ENGINE=MyISAM DEFAULT CHARSET=latin1
One table (the one that's holding the most data) indicates ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC - I don't know where the Row_Format Dynamic came from - Admittedly - (indeed, shamefully) - I know very little about DBs

*****************************

So, can anyone see what am I missing here?

The only thing that I can think of at this point is that I've:
1. Missed wrapping the data of some text field in mysql_real_escape_string() and the encoders are occasionally inputting an apostrophe in that field - or some other character the DB doesn't like, or
2. I've missed validating the length of some text data and it's longer than the target field will accept

But, would either of these two problems trigger empty records across 7 tables?

Anyway, before I go off on a search for what could be possibly wrong (a fairly significant task at this point), I'd like the input of the experts here on where else I should check.

Has anyone else had this problem - blank fields across numerous tables on INSERT?

Is there a check-list of sorts of things that I MUST check for before an insert happens to ensure that all data gets inserted?

Sorry for the long, long post but I wanted to give as much background/information as possible.

HUGE appreciation to anyone to can suggest the possible error(s) of my ways!

Neophyte

3:56 am on Aug 26, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:June 6, 2005
posts:109
votes: 0


ROW_FORMAT=DYNAMIC is not a problem. Tables will automatically be dynamic if you're using varchar or text fields anyway.

In step 5 - Are you re-validating all the data at this stage? It's quite possible users get to the last or second last stage, sit for a while, the session times out and they finish with no data. It might be too late now, but if I was you I would think about using temporary DB tables to store session data instead of PHP's $_SESSION.

Check the DB table's default values. Are they set, what are they set to and is that the way they're meant to be?

4:28 am on Aug 26, 2008 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11681
votes: 205


how are the session variables being maintained?
perhaps cookies have been turned off.
5:58 am on Aug 26, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


MattAU -

Thanks for your response!

Glad to hear the ROW_FORMAT=DYNAMIC isn't in the list of suspects.

To answer your question, in step 5 my pre-insert-routine DOES NOT re-validate information as such. it does step through each session value (which are MANY) and if a value is blank then a zero "0" is inserted into the session var.

Would appreciate additional information (or a link would be fine) regarding the timing-out of $_Sessions: I didn't know they were time sensitive! I was under the impression that $_Sessions just hang around until they're programatically unset (which is done for all form sessions after the insert is complete), or until the user closes their browser.

I've also never heard of storing session values in a temp db table - would also be interested in knowing more about how this is done and if it's a "best practices" technique.

The default values for each and every field in all 7 tables is NULL with the EXCEPTION of the FK fields on tables 2, 3, 4, 5, 6, 7... these fields are set to NOT NULL. The PK field on table 1 is set to auto-increment NOT NULL, yet the default field is (interestingly) shown as NULL. I don't know if this is a problem or not.

I'm afraid that I'm really showing my ignorance regarding many of these issues - which I apologize for. I just have to admit that I live and work in Southeast Asia and the only source I have for learning is the Internet and peer-review from the good people at this site.

Neophyte

6:09 am on Aug 26, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


phranque -

Thank you for your response.

>> how are the session variables being maintained? <<

Er, ah, well, um (scratching head... ignorance showing... professional embarrassment rising) They're not "maintained" by any means that I'm aware of regarding cookies or any other means.

Each page in this application does, however, have it's own sessions associated with each form field. For example, page one has sessions for all items on page 1. Page 2 has sessions for all information just on page 2, and so on.

So, cookies aren't being used... should they be? When you're mentioning cookies are you thinking in the same direction that MattAU was regarding when he mentioned using temp db tables to store data until all form pages had been completed? Is doing this - or using cookies - a time-tested-and-proven technique when capturing a lot of data over a series of form pages?

Neophyte

6:46 am on Aug 26, 2008 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11681
votes: 205


well i must admit that i am not a "php programmer" per se.
but i do know that sessions can only be maintained using 2 methods - cookies and url parameters.
as i suspected, it looks like php handles this for you:
[php.net...]
so it appears that php figures out if cookies are supported by the browser and uses url parameters if not.

however the reason i asked is that i am guessing that the visitor is returning your cookies without the chocolate chips, so to speak...

10:36 am on Aug 26, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Oct 15, 2004
posts: 942
votes: 0


have you checked on the session time-out? On those rare occasions when empty data were submitted, i suspect that a session timeout has occured.

maybe the person submitting the data took a break and continue later on (for any number of reasons)

I suggest you maintain your data into cookies or increase your session timeout duration (i think default time is 25 mins or something like that)

11:18 am on Aug 26, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:June 6, 2005
posts:109
votes: 0


To answer your question, in step 5 my pre-insert-routine DOES NOT re-validate information as such. it does step through each session value (which are MANY) and if a value is blank then a zero "0" is inserted into the session var.

Would appreciate additional information (or a link would be fine) regarding the timing-out of $_Sessions: I didn't know they were time sensitive! I was under the impression that $_Sessions just hang around until they're programatically unset (which is done for all form sessions after the insert is complete), or until the user closes their browser.

Sessions are definitely time-sensitive. Everything expires eventually... Otherwise servers would overload way too often.

Here's a couple of old threads with a bit of info on changething the time-out length:

[webmasterworld.com...]
[webmasterworld.com...]

Regarding storing session in a database, you pretty much do what you're doing now, but twice. Create copies of each table to hold temporary session data. Insert the data as the user passes through each page. Once the user's finished copy the data to your real tables and remove it from the temporary tables. The advantage of using this method is that you've got control of the data, you're not relying (so much) on sessions which can be handled differently in different browsers and cause problems.

By the way, it makes no difference where you are in the world these days... You can learn PHP & other web technologies just as well from your couch as you can anywhere else :)

11:57 pm on Aug 26, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


omoutop and MattAU

Thank you for your responses.

omoutop -

Have checked the session time-out on the host which is set to the default in the .ini: 1440 seconds or 24 minutes. I suppose I should increase that.

MattAU -

Thanks for your explanations and links! I wasn't aware that Sessions are time-sensitive (although this does make sense) and I REALLY wasn't aware that different browsers handle $_Session vars differently (which is REALLY SCARY and could cause no end to problems with this application and any other that I build with multiple form pages).

After giving this issue a lot of hard thought I suppose I'd have to decide which way to make data from multiple form pages the most "bullet-proof" - Save all data from each page (when a user goes to the next page) via temp tables as you've suggested, or in cookies.

I'm going to create another thread on this forum to ask the "best-practices" way to accomplish this.

Thank you all for your time and suggestions!

Neophyte

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members