Jump to content

[SOLVED] INSERT error


KevinM1

Recommended Posts

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?

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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! :D

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.