Hi i am using php and mySql to create a registration form which adds itself to a database: When I send the form it gives me this error: Invalid query: Unknown column 'sVisit' in 'field list' Any help with this matter would be greatly appreciated The form page code is below: <?php header("Expires: Thu, 17 May 2001 10:17:17 GMT"); // Date in the past header ("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // always modified header ("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1 header ("Pragma: no-cache"); // HTTP/1.0 session_start(); if (!isset($_SESSION['SESSION'])) require ( "./include/session_init.php"); $arVals = array(); require_once("./include/session_funcs1.php"); // make sure the seesion vars are initialized... reset ($arVals); while (list ($key, $val) = each ($arVals)) { if (!isset($_SESSION[$key])) $_SESSION[$key] = ""; if ($_SESSION[$key] == "NULL") $_SESSION[$key] = ""; } if ($_SESSION["state_sel"] == "") $_SESSION["state_sel"] = 0; // if the flg is true then some validation problems in the data. // namely a blank field or a submission without the feedback page. // just present a general error... $flg = ""; $error = ""; if (isset($HTTP_GET_VARS["flg"])) $flg = $HTTP_GET_VARS["flg"]; switch ($flg) { case "yellow": $error = "<br><font class=\"txt12_red\">That Email Address already exists in our Database.<br>Please Select Another.<BR></font>"; break; case "red": $error = "<br><font class=\"txt12_red\">Please fill out all the required fields.<br>Please Try Again.<BR></font>"; break; case "blue": $error = "<br><font class=\"txt12_red\">Your Session has Expired.<br>Please Login Again.</font><BR>"; break; case "white": $error = "<br><font class=\"txt12_red\"><BR>The fields are too long for our Database.<br>Please correct your data via this form.</font><BR>"; break; default: $error = ""; } ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link href="./include/main.css" rel="stylesheet" type="text/css"> <script language="javascript"> function SubmitForm() { var form = document.forms[0]; var bRequired = true; if ((form.fname.value.length < 1) || (form.lname.value.length < 1) || (form.email.value.length < 1) || (form.addr1.value.length < 1) || (form.city.value.length < 1) || (form.pcode.value.length < 1) || (form.state.selectedIndex < 1)) { alert("Please fill out all the required fields."); bRequired = false; } if (!bRequired) return false; form.state_sel.value = form.state.selectedIndex; form.submit(); } function FormFill() { var form = document.forms[0]; form.fname.value = "George"; form.lname.value = "Lee"; form.email.value = "info@invisiontek.com"; form.addr1.value = "101 New York Ave."; form.city.value = "New York"; form.pcode.value = "10101"; form.state.selectedIndex = 37; } </script> </head> <body> <table width="100%" border="0" cellspacing="6" cellpadding="6"> <tr> <td><div align="right" class="txt24bb_white">User Registration</div></td> </tr> </table> <div align="center"> <form name="form1" method="post" action="./scripts/register.php"> <input name="state_sel" type="hidden" value="<?php echo $_SESSION['state_sel'] ?>" /> <div align="center"> <p> <span class="txt14bb_white">A Robust Registration Form. </span><br> </p> </div> <div align="center"> <table width="500" border="0" align="center" cellpadding="1" cellspacing="1" class="tbl_border"> <tr> <td><table width="100%" border="0" cellpadding="0" cellspacing="0" class="tbl_gray_wborder"> <tr> <td class="txt14b_red"><table width="426" border="0" align="center" cellpadding="0" cellspacing="0" class="txt12b_black"> <tr> <td width="15"></td> <td width="142"></td> <td width="220"></td> <td width="47"></td> </tr> <tr> <td colspan="4" class="txt24b_black">Registration Form</td> </tr> <tr> <td colspan="4" align="center"><?php echo $error; ?> <div align="left"></div></td> </tr> <tr height="7"> <td height="7"><img src="images/bgcolor_pixel.gif" width="1" height="1" /></td> <td height="7"><img src="images/bgcolor_pixel.gif" width="1" height="1" /></td> <td height="7"><img src="images/bgcolor_pixel.gif" width="1" height="1" /></td> <td height="7"><img src="images/bgcolor_pixel.gif" width="1" height="1" /></td> </tr> <tr height="20"> <td height="26"> </td> <td height="26" class="txt12_black"><div align="left">First Name:</div></td> <td height="26" align="right" class="txt_main12"> <input name="fname" type="text" class="form-field-white-nar-sm" value="<?php echo $_SESSION['fname'] ?>" size="30" maxlength="50" /> </td> <td height="26"><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <td> </td> <td height="26" class="txt12_black"><div align="left">Last Name:</div></td> <td align="right" class="txt_main12"><input name="lname" type="text" class="form-field-white-nar-sm" id="lname4" value="<?php echo $_SESSION['lname'] ?>" size="30" maxlength="50" /> </td> <td><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <tr> <td> </td> <td height="26" class="txt12_black"><div align="left">Email Address:</div></td> <td align="right"><input name="email" type="text" class="form-field-white-nar-sm" id="email4" value="<?php echo $_SESSION['email'] ?>" size="30" maxlength="80" /></td> <td><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <tr> <td> </td> <td height="26" class="txt12_black"><div align="left">Mailing Address:</div></td> <td align="right"><input name="addr1" type="text" class="form-field-white-nar-sm" id="addr1" value="<?php echo $_SESSION['addr1'] ?>" size="30" maxlength="80" /> </td> <td><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <tr> <td> </td> <td height="26" class="txt12_black"> </td> <td align="right"><input name="addr2" type="text" class="form-field-white-nar-sm" id="address24" value="<?php echo $_SESSION['addr2'] ?>" size="30" maxlength="70" /> </td> <td> </td> </tr> <tr> <td> </td> <td height="26" class="txt12_black"><div align="left">City:</div></td> <td align="right"><input name="city" type="text" class="form-field-white-nar-sm" id="city" value="<?php echo $_SESSION['city'] ?>" size="30" maxlength="50"/></td> <td><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <tr> <td> </td> <td height="30" class="txt12_black"><div align="left">State/Province:</div></td> <td align="right"><select name="state" class="form-list-white-nar-sm"> <option value="">Select Your State</option> <option value="AL">Alabama</option> <option value="AK">Alaska</option> </select> </td> <td><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <tr> <td> </td> <td height="30" class="txt12_black"><div align="left">Zip/Postal Code:</div></td> <td align="right"><input name="pcode" type="text" class="form-field-white-nar-sm" id="pcode" value="<?php echo $_SESSION['pcode'] ?>" size="30" maxlength="40" /> </td> <td><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <tr> <td> </td> <td height="30" class="txt12_black"><div align="left">Have you visited MagicFreebiesUk before?</div></td> <td align="right"> <input name="visit" type="radio" value="1"/>Yes <input name="visit" type="radio" value="0" checked/>No </td> <td><font class="text-required-orng"> <img src="./images/required_field.gif" width="8" height="9" hspace="0" vspace="0" border="0" align="top" /></font></td> </tr> <tr height="35"> <td height="35"> </td> <td height="35" colspan="2" align="right" valign="bottom" class="txt_main_str12"> <input type="reset" name="Submit2" value="Reset"> <input type="button" name="Submit" value="Submit" onclick="SubmitForm(); return false;" ></td><td height="35"> </td> </tr> <tr height="15"> <td height="25"> </td> <td height="25" colspan="2" align="right"> </td> <td height="25"> </td> </tr> <tr height="15"> <td height="25"> </td> <td height="25" colspan="2" align="right"><span class="txt_main_str12"> <input type="button" name="Submit3" value="Fill With Samples" onclick="FormFill(); return false;" > </span></td> <td height="25"> </td> </tr> <tr height="15"> <td height="25"><img src="images/bgcolor_pixel.gif" width="1" height="1" /></td> <td height="25" colspan="2" align="right"><img src="images/bgcolor_pixel.gif" width="1" height="1" /></td> <td height="25"><img src="images/bgcolor_pixel.gif" width="1" height="1" /></td> </tr> </table></td> </tr> </table></td> </tr> </table> <br> </div> </form> </div> <script language="javascript"> // set the selection box values... var form = document.forms[0]; form.state.selectedIndex = parseInt("<?php echo $_SESSION['state_sel'] ?>"); </script> </body> </html> Code (markup): And the processing php page: <?php $debug = FALSE; /************************************************************ Adjust the headers... ************************************************************/ header("Expires: Thu, 17 May 2001 10:17:17 GMT"); // Date in the past header ("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // always modified header ("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1 header ("Pragma: no-cache"); // HTTP/1.0 /***************************************************************************** Check the session details. we will store all the post variables in session variables this will make it easier to work with the verification routines *****************************************************************************/ session_start(); if (!isset($_SESSION['SESSION'])) require_once( "../include/session_init.php" ); $arVal = array(); require_once("../include/session_funcs1.php"); reset ($_POST); while (list ($key, $val) = each ($_POST)) { if ($val == "") $val = "NULL"; $arVals[$key] = (get_magic_quotes_gpc()) ? $val : addslashes($val); if ($val == "NULL") $_SESSION[$key] = NULL; else $_SESSION[$key] = $val; if ($key != "access_period" && $key != "passwd") $arVals[$key] = "'".$arVals[$key]."'"; if ($debug) echo $key . " : " . $arVals[$key] . "<br>"; } /********************************************************************************************** Make sure session variables have been set and then check for required fields otherwise return to the registration form to fix the errors. **********************************************************************************************/ // check to see if these variables have been set... if ( (!isset($_SESSION["fname"])) || (!isset($_SESSION["lname"])) || (!isset($_SESSION["email"])) || (!isset($_SESSION["city"])) || (!isset($_SESSION["state"])) || (!isset($_SESSION["pcode"])) || (!isset($_SESSION["visit"])) ) { resendToForm("?flg=red"); } // form variables must have something in them... if ($_SESSION['fname'] == "" || $_SESSION['lname'] == "" || $_SESSION['email'] == "" || $_SESSION['addr1'] == "" || $_SESSION['city'] == "" || $_SESSION['state'] == "" || $_SESSION['pcode'] == "" || $_SESSION['visit'] == "") { resendToForm("?flg=red"); } // make sure fields are within the proper range... if (strlen($_SESSION['fname']) > 35 || strlen($_SESSION['lname']) > 35 || strlen($_SESSION['email']) > 35 || strlen($_SESSION['addr1']) > 35 || strlen($_SESSION['addr2']) > 40 || strlen($_SESSION['city']) > 35 || strlen($_SESSION['state_name']) > 35 || strlen($_SESSION['pcode']) > 35 || strlen($_SESSION['visit']) > 3) { resendToForm("?flg=white"); } /* NOT USING PASSWORD OR EMAIL DUPLICATION CHECK /********************************************************************************************** Check the DB for records... ********************************************************************************************** // check for the email already in the database... $query = "SELECT COUNT(sEmail) FROM tbl_users where sEmail = '".$_SESSION['email']."'"; if ($debug) echo "<br>SQL STATEMENT:<br>".$query."<br><br>"; mysql_pconnect($_SESSION['MYSQL_SERVER1'],$_SESSION['MYSQL_LOGIN1'],$_SESSION['MYSQL_PASS1']) or die("Unable to connect to SQL server"); mysql_select_db($_SESSION['MYSQL_DB1']) or die("Unable to select database"); $result = mysql_query($query) or die("Invalid query (login): " . mysql_error()); $row = mysql_fetch_row($result); if ($row[0] > 0) { // an email aleady exists in the database, because the row count > 0... resendToForm("?flg=yellow"); } /* WHEN YOU INSERT USE MD5 for Passwords!!!! $password = $arVals['passwd']; $arVals['passwd'] = "'".md5($arVals['passwd'])."'"; */ /********************************************************************************************** Insert into the database... **********************************************************************************************/ mysql_pconnect($_SESSION['MYSQL_SERVER1'],$_SESSION['MYSQL_LOGIN1'],$_SESSION['MYSQL_PASS1']) or die("Unable to connect to SQL server"); mysql_select_db($_SESSION['MYSQL_DB1']) or die("Unable to select database"); $query = "INSERT INTO tbl_users (sFName, sLName, sAddr1, sAddr2, sCity, sState, sPCode, cCountryCode, sEmail, sVisit )" ."VALUES (".$arVals['fname'].", ".$arVals['lname'].", ".$arVals['addr1'].", ".$arVals['addr2'] .", ".$arVals['city'].", ".$arVals['state'].", ".$arVals['pcode'].", 'UK'" .", ".$arVals['email'].", ".$arVals['visit'].") "; //echo $query; $result = mysql_query($query) or die("Invalid query: " . mysql_error() . "<br><br>". $query); $insertid = mysql_insert_id(); /*** This following function will update session variables and resend to the form so the user can fix errors ***/ function resendToForm($flags) { reset ($_POST); // store variables in session... while (list ($key, $val) = each ($_POST)) { $_SESSION[$key] = $val; } // go back to the form... //echo $flags; header("Location: ../user_registration.php".$flags); exit; } ?> SUCCESS!<br>The data was entered in the database!<br> You probably want to redirect to a thank you page or send an email to the user for confirmation.<br> <br><br> Here are the variables...<br> <?php reset ($arVals); while (list ($key, $val) = each ($arVals)) { echo $key . " : " . $arVals[$key] . "<br>"; } echo "<br><br>The SQL Statement was:<br>"; echo $query."<br><br><br><br>"; ?> Code (markup): the session_funcs1: <?php // setup a dummy array... $arVals = array( "fname"=>"", "lname"=>"", "email"=>"", "addr1"=>"", "addr2"=>"", "city"=>"", "state"=>"", "pcode"=>"", "state_sel"=>"", "visit"=>""); ?> Code (markup): Thanks!!!
Are you sure, you have realy in your table tbl_users field with name sVisit??? Otherwise try to copy this (use single quotes for strings) : $query = "INSERT INTO tbl_users (sFName, sLName, sAddr1, sAddr2, sCity, sState, sPCode, cCountryCode, sEmail, sVisit) VALUES ('".$arVals['fname']."', '".$arVals['lname']."', '".$arVals['addr1']."', '".$arVals['addr2']."', '".$arVals['city']."', '".$arVals['state']."', '".$arVals['pcode']."', 'UK', '".$arVals['email']."', '".$arVals['visit']."')"; What does it say now??
It now says: Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'George'', ''Lee'', ''101 New York Ave.'', ''NULL'', ''New York'', ''NY'', ''1010' at line 1
Great. If you have sPCode as a number take away the single quotes. Also NULL as a string is not really healthy, but this piece of syntax seems to be ok. What type of filed is sAddr2 (is it set on NOT NULL)??
Yep: INSERT INTO tbl_users (sFName, sLName, sAddr1, sAddr2, sCity, sState, sPCode, cCountryCode, sEmail, sVisit )VALUES ('George', 'Lee', '101 New York Ave.', 'NULL', 'New York', 'NY', '10101', 'UK', 'info@invisiontek.com', 'No')
There is also a user id auto incrementing column and an insertion time stamp in the database table if that changes anything
Ok. Just for accuracy : ...sVisit) VALUES ... maybe the space there is bothering. And if nothing will help, not sure what version of MySQL you have, but try to put there the id as well into columns and into VALUES just '' - (two single quotes) if you have id as auto_increment. The syntax is ok (though this representation of NULL might be treated as a string with length 4), there has to be some problem with the DB, either it has problems with auto_increment or the fieldnames are written incorrectly...
ANd one of the last try : $query = "INSERT INTO tbl_users (sFName,sLName,sAddr1,sAddr2,sCity,sState,sPCode,cCountryCode,sEmail,sVisit) VALUES ('".$arVals['fname']."','".$arVals['lname']."','".$arVals['addr1']."','".$arVals['addr2']."','".$arVals['city']."','".$arVals['state']."','".$arVals['pcode']."','UK','".$arVals['email']."','".$arVals['visit']."')";
That does not work: Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'George'',''Lee'',''101 New York Ave.'',''NULL'',''New York'',''NY'',''10101'','U' at line 1
Omg, it's not double quotes what the error reports, it's two single quotes... Don't understand how, but it looks that magic_quotes is appending the single quotes. Then we are in the beginning, so : $query = "INSERT INTO tbl_users (sFName,sLName,sAddr1,sAddr2,sCity,sState,sPCode,cCountryCode,sEmail,sVisit) VALUES (".$arVals['fname'].",".$arVals['lname'].",".$arVals['addr1'].",".$arVals['addr2'].",".$arVals['city'].",".$arVals['state'].",".$arVals['pcode'].",'UK',".$arVals['email'].",".$arVals['visit'].")"; This should give either your first error, or it's solved...