KevinM1 Posted June 14, 2007 Share Posted June 14, 2007 I'm currently trying to insert a host of data into a MySQL database with a custom built sticky form for a PHP Fusion product. This form is supposed to enable people to register for events that my client is hosting. The site has an event calendar addon, which I've modified to display a link if the event in question is supposed to have guest registration. If so, there's a link that brings the user to a relatively simple registration form. The link contains the event id, which it passes to the form via GET to help with the eventual insertation of values. Here's my table: fusion_aflac: registering_agent VARCHAR(50) NOT NULL agent_writing_number VARCHAR(10) NOT NULL phone VARCHAR(11) NOT NULL email VARCHAR(25) NOT NULL regional_sales_coordinator VARCHAR(50) NOT NULL district_sales_coordinator VARCHAR(50) NOT NULL ev_id INT(10) UNSIGNED NOT NULL user_id SMALLINT(5) UNSIGNED NOT NULL ev_title VARCHAR(200) NOT NULL ev_start DATETIME NOT NULL ev_end DATETIME NOT NULL login_timestamp INT(10) UNSIGNED NOT NULL login_status TINYINT(1) UNSIGNED NOT NULL My code (and forgive me, it's long): <?php require_once "maincore.php"; require_once "subheader.php"; require_once "side_left.php"; if(file_exists(INFUSIONS."aw_ecal_panel/locale/".$settings['locale'].".php")) { include INFUSIONS."aw_ecal_panel/locale/".$settings['locale'].".php"; } else { include INFUSIONS."aw_ecal_panel/locale/German.php"; } if(isset($_GET['evid'])){ $ev = dbquery("SELECT * FROM ".DB_PREFIX."aw_ec_events WHERE ev_id=". $_GET['evid']); $event = dbarray($ev); $ev_id = $event['ev_id']; $user_id = $userdata['user_id']; $ev_title = $event['ev_title']; $ev_start = $event['ev_start']; $ev_end = $event['ev_end']; } //Below is code I used for debugging my timestamp problems from my last PHP Help thread. //Please ignore everything that's in the comment block immediately below. /* echo "Event id = ". $event['ev_id'] .", User id = ". $userdata['user_id'] .", Event title = ". $event['ev_title'] ."<br />"; echo "Event start time = ". $event['ev_start'] .", Event end time = ". $event['ev_end'] ."<br />"; $start_timestamp = strtotime($event['ev_start']); $end_timestamp = strtotime($event['ev_end']); echo "Event start timestamp = $start_timestamp, Event end timestamp = $end_timestamp<br /><br />"; echo "Can we get the right thing back?<br /><br />"; echo "Event start time (after timestamp -> date conversion) = ". date("m-d-Y h:i:s T", $start_timestamp) .", Event end time (after timestamp -> date conversion) = ". date("m-d-Y h:i:s T", $end_timestamp) ."<br /><br />"; */ $errMessage = NULL; if(isset($_POST['submit'])){ $ev = dbquery("SELECT * FROM ".DB_PREFIX."aw_ec_events WHERE ev_id=". $_POST['evid']); $event = dbarray($ev); $ev_id = $event['ev_id']; $user_id = $userdata['user_id']; $ev_title = $event['ev_title']; $ev_start = $event['ev_start']; $ev_end = $event['ev_end']; if(!empty($_POST['regAgent']) && preg_match("/^[a-zA-Z]+([ a-zA-Z-]+)*$/i", $_POST['regAgent'])){ $regAgent = $_POST['regAgent']; $ra = TRUE; } else{ $errMessage .= "Please enter your name!<br />\n"; } if(!empty($_POST['agentWritingNum']) && preg_match("/^[0-9a-zA-Z]*$/i", $_POST['agentWritingNum'])){ $agentWritingNum = $_POST['agentWritingNum']; $awn = TRUE; } else{ $errMessage .= "Please enter your writing number!<br />\n"; } if(!empty($_POST['phoneNum'])){ $phoneNum = $_POST['phoneNum']; if(preg_match("/^[0-9]{3}$/i", $phoneNum[0]) && preg_match("/^[0-9]{3}$/i", $phoneNum[1]) && preg_match("/^[0-9]{4}$/i", $phoneNum[2])){ $areaCode = $phoneNum[0]; $firstPart = $phoneNum[1]; $secondPart = $phoneNum[2]; $phoneText = "$areaCode-$firstPart-$secondPart"; $phone = TRUE; } else{ $errMessage .= "Please enter your correct phone number!<br />\n"; } } else{ $errMessage .= "Please enter your phone number!<br />\n"; } if(!empty($_POST['emailAddress']) && preg_match("/^[\w-]+(\.[\w-]+)*@([\w-]+\.)+[a-zA-Z]{2,7}$/i", $_POST['emailAddress'])){ $email = $_POST['emailAddress']; $e = TRUE; } else{ $errMessage .= "Please enter your e-mail address!<br />\n"; } if(!empty($_POST['regionalSales']) && preg_match("/^[a-zA-Z]+([ a-zA-Z-]+)*$/i", $_POST['regionalSales'])){ $regSales = $_POST['regionalSales']; $rs = TRUE; } else{ $errMessage .= "Please enter the name of your regional sales coordinator!<br />\n"; } if(!empty($_POST['districtSales']) && preg_match("/^[a-zA-Z]+([ a-zA-Z-]+)*$/i", $_POST['districtSales'])){ $disSales = $_POST['districtSales']; $ds = TRUE; } else{ $errMessage .= "Please enter the name of your district sales coordinator!<br />\n"; } if($ra && $awn && $phone && $email && $rs && $ds){ //start the big process of updating tables and e-mailing results $query = "INSERT INTO ".DB_PREFIX."aflac (registering_agent, agent_writing_number, phone, email, regional_sales_coordinator, district_sales_coordinator, ev_id, user_id, ev_title, ev_start, ev_end, login_timestamp, login_status) VALUES (". $regAgent .", ". $agentWritingNum .", ". $phoneText .", ". $email .", ". $regSales .", ". $disSales .", ". $ev_id .", ". $user_id .", ". $ev_title .", ". $ev_start .", ". $ev_end .", ". strtotime('now') .", 1)"; $result = mysql_query($query); if($result){ echo "Success!<br /><br />\n\n"; } else{ echo "Something went wrong with the insert!<br /><br />\n\n"; } } else{ echo "<div style='color: red;'>$errMessage</div><br />"; } } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>Registration Form</title> <link rel="stylesheet" type="text/css" href="formstyles.css"> </head> <body style="text-align: center;"> <div style="width: 400px; margin: 0 auto;"> Registration Form<br /><br /> <form action="<?php echo $_SERVER['PHP_SELF']; ?>?evid=<?php echo $ev_id; ?>" method="post"> <fieldset class="narrow"><legend>Please input your information</legend> <p><label for="regAgent">Registering Agent:</label><input type="text" name="regAgent" value="<?php if(isset($_POST['regAgent'])){echo $_POST['regAgent'];} ?>" /></p> <p><label for="agentWritingNum">Agent Writing Number:</label><input type="text" name="agentWritingNum" value="<?php if(isset($_POST['agentWritingNum'])){echo $_POST['agentWritingNum'];} ?>" /></p> <p><label for="phoneNum">Phone Number:</label>(<input type="text" name="phoneNum[]" size="3" maxlength="3" value="<?php if(isset($_POST['phoneNum'])){echo $_POST['phoneNum'][0];} ?>" />) - <input type="text" name="phoneNum[]" size="3" maxlength="3" value="<?php if(isset($_POST['phoneNum'])){echo $_POST['phoneNum'][1];} ?>" /> - <input type="text" name="phoneNum[]" size="4" maxlength="4" value="<?php if(isset($_POST['phoneNum'])){echo $_POST['phoneNum'][2];} ?>" /></p> <p><label for="emailAddress">E-mail Address:</label><input type="text" name="emailAddress" value="<?php if(isset($_POST['emailAddress'])){echo $_POST['emailAddress'];} ?>" /></p> <p><label for="regionalSales">Regional Sales Coordinator:</label><input type="text" name="regionalSales" value="<?php if(isset($_POST['regionalSales'])){echo $_POST['regionalSales'];} ?>" /></p> <p><label for="districtSales">District Sales Coordinator:</label><input type="text" name="districtSales" value="<?php if(isset($_POST['districtSales'])){echo $_POST['districtSales'];} ?>" /></p> </fieldset> <input type="hidden" name="evid" value="<?php echo $ev_id; ?>" /> <p><input type="submit" name="submit" value="Submit" /></p> </form> </div> </body> </html> <?php require_once "side_right.php"; require_once "footer.php"; ?> I'm not getting any MySQL syntax errors. Instead, I'm only getting the message I coded in the event that an insert didn't work ("Something went wrong with the insert!"). Any ideas? Quote Link to comment https://forums.phpfreaks.com/topic/55605-solved-insert-error/ Share on other sites More sharing options...
Wildbug Posted June 14, 2007 Share Posted June 14, 2007 Columns appear to match. Try mysql_error(). Quote Link to comment https://forums.phpfreaks.com/topic/55605-solved-insert-error/#findComment-274743 Share on other sites More sharing options...
KevinM1 Posted June 14, 2007 Author Share Posted June 14, 2007 Columns appear to match. Try mysql_error(). Using PHP Fusion's own dbquery() function and the following input: Registering Agent: Test Subject Agent Writing Number: A1234 Phone Number: 123 456 7890 E-Mail: testsubject@gmail.com Regional Sales Coordinator: Another Person District Sales Coordinator: Someone Else ev_id: 11 user_id: 1 ev_title: Test ev_start: 2007-06-14 11:59:00 ev_end: 2007-06-14 11:59:00 login_timestamp: 1181843655 login_status: 1 I get the following error: 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 'Subject, A1234, 123-456-7890, testsubject@gmail.com, Another Person, Someone Els' I can't see any obvious error, and the syntax looks correct when I output it to the screen: INSERT INTO fusion_aflac (registering_agent, agent_writing_number, phone, email, regional_sales_coordinator, district_sales_coordinator, ev_id, user_id, ev_title, ev_start, ev_end, login_timestamp, login_status) VALUES (Test Subject, A1234, 123-456-7890, testsubject@gmail.com, Another Person, Someone Else, 11, 1, Time test, 2007-06-14 11:59:00, 2007-06-14 11:59:00, 1181843655, 1) Quote Link to comment https://forums.phpfreaks.com/topic/55605-solved-insert-error/#findComment-274765 Share on other sites More sharing options...
Wildbug Posted June 14, 2007 Share Posted June 14, 2007 Your strings are not quoted. What I like to do alot of times is something like the following: <?php query = sprintf('INSERT INTO %saflac (registering_agent, agent_writing_number, phone, email, regional_sales_coordinator, district_sales_coordinator, ev_id, user_id, ev_title, ev_start, ev_end, login_timestamp, login_status) VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", NOW(), 1)' DB_PREFIX, mysql_real_escape_string($regAgent), mysql_real_escape_string($agentWritingNum), mysql_real_escape_string($phoneText), mysql_real_escape_string($email), mysql_real_escape_string($regSales), mysql_real_escape_string($disSales), mysql_real_escape_string($ev_id), mysql_real_escape_string($user_id), mysql_real_escape_string($ev_title), mysql_real_escape_string($ev_start), mysql_real_escape_string($ev_end) ); ?> Of course, if there's a Fusion-specific dbquery() function, maybe it should handle escaping. Anyway, the quoting issue is what's generating the error. Quote Link to comment https://forums.phpfreaks.com/topic/55605-solved-insert-error/#findComment-274812 Share on other sites More sharing options...
KevinM1 Posted June 14, 2007 Author Share Posted June 14, 2007 Your strings are not quoted. What I like to do alot of times is something like the following: <?php query = sprintf('INSERT INTO %saflac (registering_agent, agent_writing_number, phone, email, regional_sales_coordinator, district_sales_coordinator, ev_id, user_id, ev_title, ev_start, ev_end, login_timestamp, login_status) VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", NOW(), 1)' DB_PREFIX, mysql_real_escape_string($regAgent), mysql_real_escape_string($agentWritingNum), mysql_real_escape_string($phoneText), mysql_real_escape_string($email), mysql_real_escape_string($regSales), mysql_real_escape_string($disSales), mysql_real_escape_string($ev_id), mysql_real_escape_string($user_id), mysql_real_escape_string($ev_title), mysql_real_escape_string($ev_start), mysql_real_escape_string($ev_end) ); ?> Of course, if there's a Fusion-specific dbquery() function, maybe it should handle escaping. Anyway, the quoting issue is what's generating the error. LOL, it's always the stupid mistakes that bite me. Thanks for the help! I'm not 100% sure if PHP Fusion uses any escaping. I'm going to add some to my registration form anyway once I get the second segment of my project finished. I'm not worried about it right now as I'm doing everything on a test site, so nothing on the live site runs the risk of being messed up. Quote Link to comment https://forums.phpfreaks.com/topic/55605-solved-insert-error/#findComment-274894 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.