MySQL strangeness, table saves intermittently...

[xtian]

This is a general mystery. I have two tables for a simple form. One contains names and the other emails. The names table has name and auto increment. The email tables have the email, id, and foreign key mapped with php's mysql_last_id_thingy().

 

The ids are fine. The problem is, out of ten tests, only six emails were saved. The rest are blank! What could cause such a thing to happen? any ideas?

 

Chris

[Stooney]

Are you positive that the data isn't lost before the sql query?  Also, did you accidentally set up the email field as unique?

[xtian]

If its lost before it gets to the server, then it should trip the die() error catch. No? And I did ten tests with ten different fake emails....

[xtian]

Devil's in the details. So what's wrong with this code to cause some email addresses to be dropped?

My php query,

//The name query var...
//Then its saved...
$dbSuccess_1 = mysql_query($insertName,$connectID) or die ("ERROR_1 
- Unable to save.");
//Get the name insert ID
$lastMysqlID = mysql_insert_id($connectID);
//The email query var...
$insertEmail = "INSERT INTO email ( email_addr, name_id )VALUES( '$emailAdr', 
'$lastMysqlID')";
//Then its saved
$dbSuccess_2 = mysql_query($insertEmail,$connectID) or die ("ERROR_1 
- Unable to save.");

 

I have a general set of filters for $_POST data from a user form, (NOTE= where I note the following is recursive, thats where I removed the function and just displayed the operative code tag)

$_POST = array_map('trim', $_POST);
// recursive use of...
...array_map('f_stripslashes_deep', $value) :
// recursive use of...
...strip_tags($value, '<a><br><em><i><b>');
// recursive use of... 
...htmlspecialchars($value, ENT_COMPAT, 'ISO8859-1');
$_POST = array_map('mysql_real_escape_string', $_POST);

 

Finally the form page has an email filter,

filter_var($emailAdr, FILTER_VALIDATE_EMAIL)

 

Then when you look into phpMyAdmin you see some emails are missing,

ID    email                      name_id
1      xtian@thatsmyname.com   1
2                              2
3      example2@test.com         3
4                              4
5      guns@andammo.com       5
6                              6
7                              7
8      ToYou@gmail.com          8
9      DoDo@Gogg.com           9
10    swf@strange.com          10

Again, there is a filter check. If the email is missing, you can't submit the form. Any obvious problems??

[PFMaBiSmAd]

A) Is this repeatable. The same data produces the same result?

B) What are the test email address that are failing?

C) It sounds like you are doing client-side validation. You must ALWAYS do server-side validation once the data reaches you server.

D) How about checking what data is actually being submitted to the php script with the following -

echo "<pre>";
echo "POST:";
print_r($_POST);
echo "</pre>";

 

[xtian]

CORRECTION This is supposed to be,

// recursive use of...
...array_map('f_stripslashes_deep', $value) :

...stripslashes($value);

 

[xtian]

Update.

I did an analysis of the various inputs. Its not likely a MySQLs error, but a problem with my variable scope.

 

This form has an instance where, if the user does not select certain options when they first submit the form, its returned with completed options uneditable and remaining options highlighted. The uneditable options exist in this form as labels and printed variable values. At the end I thought I could get the values back into post simply by declaration. Here is a snippet,

 

...more form above...
print '<label title="Message">Message:</label>';
print $messageForw."\n";
print '<input type="submit" value="Confirm" name="confirmed" />'."\n";
$_POST['message'] = $messageForw;
...more post and vars here...

 

I recognized the form was resubmitting, and there were no input fields to repost those values. I thought to do for post similar with session and assign post the value at the end of the form. And I think this must be why the data is not showing up in the database, because post is empty in these few options, including the lost emails.

 

Either I have to find a way to set the post var or leave the input field and make the background invisible. Any comments bout the best solution?

 

Chris

[DavidAM]

To get the values in POST when the user submits the corrected form, they need to be in fields on the form (LABELs are not POSTed).  If you want to prevent the user from changing a field, use the same field tag you did in the original form but with the READONLY attribute:

<INPUT type="text" name="txtFName" value="$_POST['txtFName']" readonly="READONLY">

 

Or you can use a hidden field to prevent the user from even seeing it:

<INPUT type="hidden" name="txtFName" value="$_POST['txtFName']">

 

Do not use the DISABLED attribute, since some (most?) browsers do not POST disabled fields.