HELP - PHP not writing 2 specific fields to MYSQL DB??

camarosource

I'm doing a php script that writes the results of a filled out html form to a database and emails me a brief summary of what was written. I have a strange problem where it writes all the info to the DB except 2 of the MYSQL Fields are empty yet the rest carry on being filled out.

For example: If you fill out all the fields in http://www.camarosource.ca/2006_calendars/picture_submissions_2006.htm and submit, it writes the info to the DB, but leaves out the ICQ, and CITY are both BLANK.YET the brief summary emailed to me shows it in fact saved the ICQ and CITY in it's correct variables so this means it's not WRITTING them to the DB..

<?php

// create connection
$conn = mysql_connect("localhost","[PRIVATE]","[PRIVATE]")
or die(mysql_error());

// select database
$db = mysql_select_db("[PRIVATE]", $conn) or die(mysql_error());

// Set entered fields in Request Field as assigned variables

$NAME = $_POST['FULL_NAME'];
$EMAIL = $_POST['EMAIL'];
$URL = $_POST['URL'];
$ICQ = $_POST['ICQ'];
$MSN = $_POST['MSN'];
$AIM = $_POST['AIM'];
$YAHOO = $_POST['YAHOO'];
$ADDRESS = $_POST['ADDRESS'];
$CITY = $_POST['CITY'];
$PROVINCE_STATE = $_POST['PROVINCE_STATE'];
$COUNTRY = $_POST['COUNTRY'];
$POSTAL_ZIP = $_POST['POSTAL_ZIP'];
$YEAR = $_POST['CAMARO_YEAR'];
$MODEL = $_POST['CAMARO_MODEL'];
$PREFERRED_MONTH = $_POST['PREFERRED_MONTH'];
$SAID_TO_PURCHASE = $_POST['SAID_TO_PURCHASE'];
$SPECIAL_DATE = $_POST['SPECIAL_DATE'];
$CUSTOM_TITLE = $_POST['CUSTOM_TITLE'];
$PERSONALIZED_INFO = $_POST['PERSONALIZED_INFO'];

$MISSING = false;



//Start IF fields were blank
if ($NAME == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter your FULL NAME</b></font></p>";
}

if ($EMAIL == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter your EMAIL Address</b></font></p>";
}

if ($ADDRESS == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter your ADDRESS</b></font></p>";
}

if ($CITY == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter your CITY</b></font></p>";
}

if ($PROVINCE_STATE == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter your PROVINCE / STATE</b></font></p>";
}

if ($COUNTRY == "--- Please Select ---") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please select your COUNTRY</b></font></p>";
}

if ($POSTAL_ZIP == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter your ZIP / POSTAL CODE</b></font></p>";
}

if ($YEAR == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter the YEAR of your Camaro</b></font></p>";
}

if ($MODEL == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter the MODEL of your Camaro</b></font></p>";
}

if ($SAID_TO_PURCHASE == "--- Please Select ---") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>For Reference Purposes - Please specify how many calendars you would be interested in.</b></font></p>";
}

if ($PERSONALIZED_INFO == "") {
   $MISSING = true;
   echo "<p><font color=\"#FF0000\" size=\"4\"><b>Please enter your PERSONALIZED INFO.. This is what you want to have said in your Camaro Info bubble. Ie. Car specs, Mods, etc</b></font></p>";
}

 if ($MISSING) exit;


// ------------------- Processing submitted data

 // build our query 
   $query = "INSERT INTO user_submissions(FULL_NAME, EMAIL, URL, ICQ, MSN, AIM, YAHOO, ADDRESS, CITY, PROVINCE_STATE, COUNTRY, POSTAL_ZIP,
   CAMARO_YEAR, CAMARO_MODEL, PREFERRED_MONTH, SAID_TO_PURCHASE, SPECIAL_DATE, CUSTOM_TITLE, PERSONALIZED_INFO, COMMENTS)
   VALUES ('$NAME', '$EMAIL', '$URL', '$ICQ', '$MSN', '$AIM', '$YAHOO', '$ADDRESS', '$CITY', '$PROVINCE_STATE', '$COUNTRY', '$POSTAL_ZIP', '$CAMARO_YEAR',
   '$CAMARO_MODEL', '$PREFERRED_MONTH', '$SAID_TO_PURCHASE', '$SPECIAL_DATE', '$CUSTOM_TITLE', '$PERSONALIZED_INFO', '$COMMENTS')";

   // execute the query 
   mysql_query($query, $conn) or die (mysql_error()); 






submit() ;

 // ------------------------------ [ Start compiling Application email to be sent to ME ] -----------------------------
function submit() {

$msg = "The following Person is interested in being in the 2006 Camarosource.ca Camaro Calendar. CHECK THE DATABASE\n\n";
$msg .= "Real Full Name : \t$_POST[FULL_NAME]\n";
$msg .= "Email Address : \t$_POST[EMAIL]\n";
$msg .= "Webpage URL : \t$_POST[URL]\n";
$msg .= "ICQ : \t$_POST[ICQ]\n";
$msg .= "CITY : \t$_POST[CITY]\n";
$msg .= "MSN : \t$_POST[MSN]\n";
$msg .= "AIM : \t$_POST[AIM]\n";
$msg .= "Yahoo! : \t$_POST[YAHOO]\n";

$msg .= "Comments : \t$_POST[COMMENTS]\n";

$mailheaders = "From: \t$_POST[EMAIL]\n";
$mailheaders .= "Reply-To: \t$_POST[EMAIL]\n\n";
mail("PRIVATE","[PRIVATE]: 2006 Camaro Calendar Submission - INFO", $msg, $mailheaders);

}

?>

I checked and the field for the ICQ and CITY ARE in fact "ICQ" "CITY" and the MYSQL database fields are also "ICQ" and "CITY".. I don't understand why it's skipping over those 2 fields and continuing to write to the rest like normally. It's like 3 fields filled in correctly, then BLANK ICQ , then 4 fields filled in correctly, then BLANK CITY, and the last 11 fields filled out correctly..

Roger_Ramjet

Debugging 101:

echo the query before you run it and post the string here. let's see exactly what it is.

camarosource

Originally posted by Roger Ramjet
Debugging 101:

echo the query before you run it and post the string here. let's see exactly what it is.

INSERT INTO user_submissions(FULL_NAME, EMAIL, URL, ICQ, MSN, AIM, YAHOO, ADDRESS, CITY, PROVINCE_STATE, COUNTRY, POSTAL_ZIP, CAMARO_YEAR, CAMARO_MODEL, PREFERRED_MONTH, SAID_TO_PURCHASE, SPECIAL_DATE, CUSTOM_TITLE, PERSONALIZED_INFO, COMMENTS) VALUES ('Paul Lodge', 'camaro92@telus.net', 'http://www.camarosource.ca', '6705249', 'camaro92@telus.net', 'camarosource', 'camarosource', 'address', 'Burnaby', 'BC', 'Canada', 'postal', '1992', 'Z28', 'June', '10', 'special', 'custom', 'lalalaladfdsafds', 'sfdsdfsaf')

ALL There.. Had it email me it too.. all there.. But still the "6705249" and "Burnaby" are not in the ICQ and CITY MYSQL Fields.

Roger_Ramjet

Nope. Can't see a thing wrong with it. Never had this problem myself, but I've heard of similar strange behaviour with the insert values query.

If this is urgent, i'd just go the other way

INSERT INTO user_submissions SET
full_name = 'name'
email = 'email'
url = 'url'

etc.

As I said, others have mentioned problems with the insert values and don't use it anymore, they use the alternate set syntax for inserts and the problem disappears. If it doesn't then you really have a problem with your db.

camarosource

Originally posted by Roger Ramjet
Nope. Can't see a thing wrong with it. Never had this problem myself, but I've heard of similar strange behaviour with the insert values query.

If this is urgent, i'd just go the other way

INSERT INTO user_submissions SET
full_name = 'name'
email = 'email'
url = 'url'

etc.

As I said, others have mentioned problems with the insert values and don't use it anymore, they use the alternate set syntax for inserts and the problem disappears. If it doesn't then you really have a problem with your db.

I changed it like you said and did

 // build our query 
   $query = "INSERT INTO user_submissions SET FULL_NAME = '$NAME', EMAIL = '$EMAIL', URL = '$URL', ICQ = '$ICQ', MSN = '$MSN', AIM = '$AIM',
   YAHOO = '$YAHOO', ADDRESS = '$ADDRESS', CITY = '$CITY', PROVINCE_STATE = '$PROVINCE_STATE', COUNTRY = '$COUNTRY', POSTAL_ZIP = '$POSTAL_ZIP',
   CAMARO_YEAR = '$CAMARO_YEAR', CAMARO_MODEL = '$CAMARO_MODEL', PREFERRED_MONTH = '$PREFERRED_MONTH', SAID_TO_PURCHASE = '$SAID_TO_PURCHASE',
   SPECIAL_DATE = '$SPECIAL_DATE', CUSTOM_TITLE = '$CUSTOM_TITLE', PERSONALIZED_INFO = '$PERSONALIZED_INFO', COMMENTS = '$COMMENTS'";

But still skipped over the ICQ and CITY.

Roger_Ramjet

Weird! Should be throwing an error if it's the wrong data type for the column or the wrong name or such. You are sure that the row is being added? (don't take offence, we've all overlooked the obvious plenty of times)

Does it work if you paste the exact query into phpmyadmin?

Have you got errors turned off?

What else??

Are the columns set to NOT NULL? and what are their default values?

At least with the SET syntax we don't have to worry about column order.

? idea ? Try an update on each column on it's own in the faulty row. Let's see if you can get the db engine to store anything in them at all.

camarosource

Originally posted by Roger Ramjet
Weird! Should be throwing an error if it's the wrong data type for the column or the wrong name or such. You are sure that the row is being added? (don't take offence, we've all overlooked the obvious plenty of times)

Does it work if you paste the exact query into phpmyadmin?

Have you got errors turned off?

What else??

Are the columns set to NOT NULL? and what are their default values?

At least with the SET syntax we don't have to worry about column order.

? idea ? Try an update on each column on it's own in the faulty row. Let's see if you can get the db engine to store anything in them at all.

I sincerely appreciate you trying to help. :-)

I have all the fields for everything set to VarChar (just for now), and Not Null. There are no default, Extra's nor attributes set for any of them.

I paste the query directly into PhpMyAdmin's SQL Query and it adds the row with all the information, but it STILL skips the ICQ and CITY. I even went to the point of RENAMING it from ICQ to "ICQ_NUMBER"... still..

camarosource

Would you be willing to perhaps go into Remote Assist with me to actually SEE what is happening? 🙂

Roger_Ramjet

Not at the moment I'm afraid. I'm here trying to file my tax returns so I'm only in the forum for light refreshment. Don't know if I could through our firewall anyway.

Did you try an update on the ICQ column? If it was happening to me I'd suspect the table def was corrupt in some way. Alwasy the prgmatist, Id just build another table and try to insert the same data into that.

camarosource

I am getting SOOO fustrated with this!!! I have now spent over 8 HOURS on this bloody thing!!

I DELETED the entire table and started over. STILL doing it.

So I then realized I didn't have a Primary ID field so the rows contained the same exact info. So I added a "SUBMISSIONS_ID" and it auto-increments.

however NOW, the the fields that are blank have been moved 1 to the LEFT. !! So NOW ICQ works, but URL that is to the LEFT of it is now the one blank, and the CITY works but NOW the one to the LEFT of that which is ADDRESS is now BLANK..

What the ????

I have DELETED the table and re-created it and it STILL doesn't work??? If I added 1 more field to the beginning, it would move the blank fields 1 MORE to the LEFT and the one that were not working before will work and the one to the LEFT of that will now be the ones blank...

Roger_Ramjet

OK, I'm back. Can't sleep. Just finished the first draft of my tax retrun and got the most horrible shock when I saw their calculation of what I owe. I'm just hoping that it is missing a lot of the money I've paid on account already for that tax year.

Anyway. Your problem sounds nearly as bad.

To the LEFT you say?? I'd have thought it would !! AH! NO?

Trash in the input.

Use mysql real escapr string on every single piece of data

camarosource

Problem FIXED. Turned out it was the Field Names that were NUMBERS that were causing problems. I added the fields as to add new fields and just entered like "234" to give it a name.

Deleted them and works PERFECTLY

camarosource

turns out to be a BUG with a numberic names field

Roger_Ramjet

Glad to hear it.

I did not see any numeric field names? I'd have told you to change them right away. Mysql may accept them, it's the only db that does, but look at the problems they lead to. Don't think you'll be using them again eh?

You should still be using the escape string on the user input though, just to protect against sql injection attacks. Read the manual if you are not convinced.

camarosource

I'm just curious. Since I am only experimenting right now and doing trial and errors (I'm an absolutely beginner at this)

How would I have it start at Primary ID 1 and not continue like 43 and on?

I deleted all rows and so I would assume it would start as one but it's MEMORIZED 43 and going on from there. By the time I have finished this script, it will be at 1000 ;-)

Roger_Ramjet

You will have to Drop the field and Create a new one once you have finished testing-development. There is no way to reset an autoinc in most dbs. The one thing you can do is to start it at a number above 1, and you can also set the increment to be more than 1, eg increment of 2 would then result in 1,3,5,7 etc.