Skip to main content
J
jeffmg
Known Participant
January 10, 2012
Answered

insert record behavior problem

  • January 10, 2012
  • 1 reply
  • 1748 views

Although it might be a mysql problem, I'm not sure. I'm using the in-built Insert Record Behavior in DMWCS4 to upload data from an online form direct to a MySWQL db table which I've set up via phpMyAdmin on the server. The SQL for the db table is below:

CREATE TABLE `tablename` (

  `compid` int(11) NOT NULL AUTO_INCREMENT,

  `Title` varchar(55) DEFAULT NOT NULL,

  `Firstname` varchar(55) DEFAULT NULL,

  `Surname` varchar(55) DEFAULT NULL,

  `Company` varchar(55) DEFAULT NULL,

  `Add1` varchar(55) DEFAULT NULL,

  `Add2` varchar(55) DEFAULT NULL,

  `Town` varchar(55) NOT NULL,

  `City` varchar(55) DEFAULT NULL,

  `Postcode` varchar(55) DEFAULT NULL,

  `County` varchar(55) DEFAULT NULL,

  `Telephone` varchar(55) DEFAULT NULL,

  `Fax` varchar(55) DEFAULT NULL,

  `Email` varchar(55) DEFAULT NULL,

  `Do not contact marker` enum('y','n') NOT NULL DEFAULT 'n',

  PRIMARY KEY (`compid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

the problem I'm having is that when the form is submitted, the Firstname column is blank in the db, regardless of what's entered on the form. I've tried changing this line:

`Firstname` varchar(55) DEFAULT NULL,

to

`Firstname` varchar(55) NOT NULL,

but when I test the form out using MAMP I get the error message "Column 'Firstname' cannot be null"

I have no idea why this is happening – if I leave the Firstname column as DEFAULT NULL I get no first names in the db table, but it won't let me change it – any and all ideas about what's going on here would be greatly appreciated. Thanks in advance.

This topic has been closed for replies.
Correct answer whatalotofrubbish

I'm now wondering if this is where things are going wrong – comparing the two versions of MySQL running on my local server (via MAMP) and the live server, it seems that I've got different versions running:

MySQL 5.5.9   (MAMP)

MySQL 4.1.22 (live server)

not sure if this is why I'm having problems... anyone?


Just noticed a typo:

  $insertSQL = sprintf("INSERT INTO panda (Title, Firstname, Surname, Company, Add1, Add2, Town, City, Postcode, County, Telephone, Fax, Email, `Do not contact marker`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['Title'], "text"),

                       GetSQLValueString($_POST['First name'], "text"),

There is a space between First and name in the second instance. This could well be causing the problem.

1 reply

B
Community Expert
Ben MCommunity Expert
Community Expert
January 10, 2012

I have no idea why this is happening – if I leave the Firstname column as DEFAULT NULL I get no first names in the db table, but it won't let me change it – any and all ideas about what's going on here would be greatly appreciated. Thanks in advance.

If this is the case the answer is in the script.  Can we see where the code from the form?  The problem must lie in the form script posting the data.

J
jeffmgAuthor
Known Participant
January 10, 2012

this is the code from the .php page:

<?php require_once("webassist/form_validations/wavt_scripts_php.php"); ?>

<?php require_once("webassist/form_validations/wavt_validatedform_php.php"); ?>

<?php require_once('Connections/compsNL.php'); ?>

<?php

if ($_SERVER["REQUEST_METHOD"] == "POST")  {

  $WAFV_Redirect = "nl_comp1.php";

  $_SESSION['WAVT_nlcomp1_969_Errors'] = "";

  if ($WAFV_Redirect == "")  {

    $WAFV_Redirect = $_SERVER["PHP_SELF"];

  }

  $WAFV_Errors = "";

  $WAFV_Errors .= WAValidateLE(strtolower($_SESSION['captcha_Security_Code_1']) . "",strtolower(((isset($_POST["Security_Code_1"]))?$_POST["Security_Code_1"]:"")) . "",true,1);

  if ($WAFV_Errors != "")  {

    PostResult($WAFV_Redirect,$WAFV_Errors,"nlcomp1_969");

  }

}

?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

?>

<?php

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

?>

<?php

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO panda (Title, Firstname, Surname, Company, Add1, Add2, Town, City, Postcode, County, Telephone, Fax, Email, `Do not contact marker`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['Title'], "text"),

                       GetSQLValueString($_POST['First name'], "text"),

                       GetSQLValueString($_POST['Surname'], "text"),

                       GetSQLValueString($_POST['Company'], "text"),

                       GetSQLValueString($_POST['Address line 1'], "text"),

                       GetSQLValueString($_POST['Address line 2'], "text"),

                       GetSQLValueString($_POST['Town'], "text"),

                       GetSQLValueString($_POST['City'], "text"),

                       GetSQLValueString($_POST['Postcode'], "text"),

                       GetSQLValueString($_POST['County'], "text"),

                       GetSQLValueString($_POST['Telephone'], "text"),

                       GetSQLValueString($_POST['Fax'], "text"),

                       GetSQLValueString($_POST['Email'], "text"),

                       GetSQLValueString(isset($_POST['Other offers']) ? "true" : "", "defined","'Y'","'N'"));

  mysql_select_db($database_compsNL, $compsNL);

  $Result1 = mysql_query($insertSQL, $compsNL) or die(mysql_error());

  $insertGoTo = "nl_comps_thanks.htm";

  if (isset($_SERVER['QUERY_STRING'])) {

    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

    $insertGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $insertGoTo));

}

?>

<!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">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Natural & Complementary Lifestyle</title>

<!--CSS STYLESHEETS START -->

<link rel="stylesheet" type="text/css" href="nl/css/reset-min.css" media="all" />

<link rel="stylesheet" type="text/css" href="nl/css/tools.css" media="all" />

<link rel="stylesheet" type="text/css" href="nl/css/style.css" media="all" />

<link rel="stylesheet" type="text/css" href="nl/js/skin.css" />

<!--CSS STYLESHEETS END -->

<!--TYPEKIT FONTS START -->

<script type="text/javascript" src="http://use.typekit.com/sep4brq.js"></script>

<script type="text/javascript">try{Typekit.load();}catch(e){}</script>

<!--TYPEKIT FONTS END -->

</head>

<body>

          <div id="wrapper">

              <!--HEADER SECTION START -->

              <div id="header">

                  <a href="#"><img src="nl/images/logo.png" alt="Natural & Complementary Lifestyle" title="Natural & Complementary Lifestyle" class="logo" /></a>

            <div class="banner_ad"></div>

            <!--TOP MENU START -->

            <div class="top_navi"><div class="top_navi_left">

                      <ul class="top_navi_right">

                    <li><a href="http://www.nat-lifestyle.com" target="_blank">Home</a></li>

                    <li><a href="http://www.nat-lifestyle.com/browse-your-symptoms/symptoms/" target="_blank">Browse Your <br />Symptoms</a></li>

                    <li><a href="http://www.nat-lifestyle.com/complementary-medicine-guide/Remedies/" target="_blank">Natural <br />Product Guide</a></li>

                    <li><a href="http://www.nat-lifestyle.com/Practitioners/" target="_blank">Find A <br />Practitioner</a></li>

                    <li><a href="http://www.nat-lifestyle.com/find-a-retailer/Retailers/" target="_blank">Find A <br />Retailer</a></li>

                    <li><a href="http://www.nat-lifestyle.com/magazines" target="_blank">Magazines</a></li>

                    <li><a href="http://www.nat-lifestyle.com/about" target="_blank">About</a></li>

                    <li class="current"><a href="https://www.targetpublishing.com/subscriptions/nlife/subscribe/">Subscribe</a></li>

                    <li class="last"><a href="http://www.targetnaturalmedia.com/nl_comps.htm" target="_blank">Win</a></li>

                      </ul>

            </div></div>

            <!--TOP MENU END -->

        </div>

        <!--HEADER SECTION END -->

        <div id="content" class="clearfix">

                  <!--BANNER SECTION START -->

                   <div class="main_banner_subscribe"></div>

            <!--BANNER SECTION END -->

            <!--CONTENT START -->

            <div class="left_col">

                      <div class="mag_lm_box">

                          <div class="mag_left_col">

                                    <h2>Latest Issue of Natural Lifestyle</h2>

                        <a href="#" class="view_issue">View Issue</a>

                    </div>

                    <div class="mag_thumb_lm">

                        <img src="nl/images/mag_thumb_1_lm.jpg" alt="Latest Issue of Natural Lifestyle" title="Latest Issue of Natural Lifestyle" />

                        <img src="nl/images/take_a_look_red_lm.png" alt="Take a look inside!" title="Take a look inside!" class="take_look_tag png" />

                    </div>

                </div>

                      <div class="mag_lm_box mag_lm_box_green">

                          <div class="mag_left_col">

                                    <h2>Latest Issue of CAM Lifestyle</h2>

                        <a href="#" class="view_issue">View Issue</a>

                    </div>

                    <div class="mag_thumb_lm">

                        <img src="nl/images/mag_thumb_2_lm.png" alt="Latest Issue of Natural Lifestyle" title="Latest Issue of Natural Lifestyle" />

                        <img src="nl/images/take_a_look_green_lm.png" alt="Take a look inside!" title="Take a look inside!" class="take_look_tag png" />

                    </div>

                </div>

            </div>

           

            <div class="center_col">

      <h1>Competitions<strong><br />

        </strong></h1>

      <h1><br />

      </h1>

      <h3><strong><img src="nl/comp_img/panda.jpg" width="176" height="230" align="left" class="right" /><br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

          <br />

Panda Strawberry Liquorice Bears Offer </strong></h3>

      <br />

      <p><em>Natural Lifestyle</em> is offering readers the chance to win one of 30 packs of the new Panda Strawberry Liquorice bears. The bears are so soft, juicy, and tasty and so kind to the new pandas (Tian Tian and Yang Guang) at Edinburgh Zoo. Guaranteed to delight kids and adults with the uniquely tasty but natural recipe made with real strawberries and no nasties, proceeds from the packs will be donated by Panda and Bravura Foods to the pandas at Edinburgh Zoo. Suitable for vegetarians and vegans.</p>

<h3> </h3>

      <h3>For your chance to win simply complete your details on the form below and click 'Submit'.<a href="http://www.targetnaturalmedia.com/nl_comps_1.php"></a></h3>

      <h3> </h3>

      <form name="form1" id="form1" method="POST" action="<?php echo $editFormAction; ?>">

        <table width="100%" border="0" cellspacing="0" cellpadding="3">

          <tr>

            <td><p>Title:</p></td>

            <td><p>

              <select name="Title" id="Title">

                <option selected="selected">Select</option>

                <option value="Mr">Mr</option>

                <option value="Mrs">Mrs</option>

                <option value="Miss">Miss</option>

                <option value="Ms">Ms</option>

                <option value="Other">Other</option>

              </select>

            </p></td>

          </tr>

          <tr>

            <td><p>First name:</p></td>

            <td><p>

              <input name="First name" type="text" class="input_box_285" id="First name" />

            </p></td>

          </tr>

          <tr>

            <td><p>Surname:</p></td>

            <td><p>

              <input name="Surname" type="text" class="input_box_285" id="Surname" />

            </p></td>

          </tr>

          <tr>

            <td><p>Company:</p></td>

            <td><p>

              <input name="Company" type="text" class="input_box_285" id="Company" size="35" />

            </p></td>

          </tr>

          <tr>

            <td><p>Address line 1:</p></td>

            <td><p>

              <input name="Address line 1" type="text" class="input_box_285" id="Address line 1" size="35" />

            </p></td>

          </tr>

          <tr>

            <td><p>Address line 2:</p></td>

            <td><p>

              <input name="Address line 2" type="text" class="input_box_285" id="Address line 2" size="35" />

            </p></td>

          </tr>

          <tr>

            <td><p>Town:</p></td>

            <td><p>

              <input name="Town" type="text" class="input_box_285" id="Town" size="35" />

            </p></td>

          </tr>

          <tr>

            <td><p>City:</p></td>

            <td><p>

              <input name="City" type="text" class="input_box_285" id="City" size="35" />

            </p></td>

          </tr>

          <tr>

            <td><p>Postcode:</p></td>

            <td><p>

              <input name="Postcode" type="text" class="input_box_85" id="Postcode" />

            </p></td>

          </tr>

          <tr>

            <td><p>County:</p></td>

            <td><p>

              <input name="County" type="text" class="input_box_285" id="County" size="35" />

            </p></td>

          </tr>

          <tr>

            <td><p>Telephone:</p></td>

            <td><p>

              <input name="Telephone" type="text" class="input_box_85" id="Telephone" />

            </p></td>

          </tr>

          <tr>

            <td><p>Fax:</p></td>

            <td><p>

              <input name="Fax" type="text" class="input_box_85" id="Fax" size="35" />

            </p></td>

          </tr>

          <tr>

            <td><p>Email:</p></td>

            <td><p>

              <input name="Email" type="text" class="input_box_285" id="Email" />

            </p></td>

          </tr>

          <tr>

            <td colspan="2"><p>From time to time we may wish to make you aware of special offers from carefully selected companies. If you do NOT wish to receive such offers, please tick this box

                <input name="Other offers" type="checkbox" id="Other offers" value="No thanks" /></p></td>

          </tr>

          <tr>

            <td colspan="2"><p><img id="capt1" src="webassist/captcha/wavt_captchasecurityimages.php?width=200&height=50&field=Security_Code_1&bgcolor=FFFFFF&transparent=0&bgimage=&gridfreq=20&gridcolor=000000&gridorder=behind&noisefreq=20&noisecolor=000000&noiseorder=behind&characters=6&charheight=&font=fonts/MOM_T___.TTF&textcolor=000000" alt="security code" width="200" height="50" /><img src="webassist/captcha/images/refresh.png" height="18" onclick="document.getElementById('capt1').src+='&ref=1'" /><br />

                <input id="Security_Code_1" name="Security_Code_1" type="text" value="" />

                <?php

if (ValidatedField('nlcomp1_969','nlcomp1_969'))  {

  if ((strpos((",".ValidatedField("nlcomp1_969","nlcomp1_969").","), "," . "") >= 0))  {

    if (!((strpos((",".ValidatedField("nlcomp1_969","nlcomp1_969").","), "," . "1" . ",") !== false || "1" == "")))  {

?>

                  Wrong code! try again

                  <?php //WAFV_Conditional nl_comp1.php nlcomp1_969(:1)

    }

  }

}?>

            </p></td>

          </tr>

          <tr>

            <td><p>

              <input type="submit" name="Submit" id="Submit" value="Submit" />

            </p></td>

            <td><p> </p></td>

          </tr>

          <tr>

            <td><p> </p></td>

            <td><p> </p></td>

          </tr>

          <tr>

            <td><p> </p></td>

            <td><p> </p></td>

          </tr>

        </table>

        <input type="hidden" name="MM_insert" value="form1" />

      </form>

      <h3><br />

        <br />

        <strong><br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        <br />

        </strong></h3>

      <p><br />

      </p>

<p><br /></div>

           

            <div class="sidebar">

                      <div class="custom_sidebar_ad img_center"><span></span></div>

                      <div class="nl_box">

                          <div class="nl_txt">

                        <h2>To receive more information from Natural and Complementary Lifestyle</h2>

                        <p>Enter you name and e-mail below:</p>

                    </div>

                   

                    <input name="name" type="text" value="Enter Full Name" size="25" class="txt_box" />

                    <input name="email" type="text" value="Enter Email" size="25" class="txt_box" />

                    <input name="submit" type="button" value="submit" class="submit_nl" />

                   

                </div>

            </div>

           

                   <!--CONTENT END -->

           

        </div>

    </div>

    <!--FOOTER SECTIONS START -->

    <div id="footer">

              <div class="footer">

                <div class="useful_links">

                  <h3>Useful Links . . . </h3>

                  <ul>

                    <li><a href="http://www.nat-lifestyle.com/about/">About Us</a></li>

                    <li><a href="http://www.nat-lifestyle.com/find-a-retailer/Retailers/">Find a health food retailer</a></li>

                    <li><a href="http://www.nat-lifestyle.com/advertise/">Advertise</a></li>

                    <li><a href="https://www.targetpublishing.com/subscriptions/nlife/subscribe/">Subscribe to Natural Lifestyle</a></li>

                    <li><a href="http://www.nat-lifestyle.com/sitemap/">Site Map</a></li>

                    <li><a href="http://www.nat-lifestyle.com/terms-and-conditions/">Terms & Conditions</a></li>

                    <li><a href="http://www.nat-lifestyle.com/our-partners/">Our Partners</a></li>

                  </ul>

                </div>

                <div class="twitter">

                 <h3>Follow us on twitter. . .</h3>

                <div class="twitter_box">

                          <ul id="twitter_update_list"></ul>

                </div>

                <div class="twitter_bottom"><div></div></div>

            </div>

            <div class="latest_issue">

                      <h3>View Latest issue of</h3>

                <div class="issue_box">

                          <div class="issue_details">

                                    <h4>Natural Lifestyle</h4>

                        <a href="#">View More</a>

                    </div>

                    <img src="nl/images/issue_img.png" alt="Natural Lifestyle" title="Natural Lifestyle" class="issue_img" />

                </div>

            </div>

           

        </div>

    </div>

   

     <script type="text/javascript" src="http://twitter.com/javascripts/blogger.js"></script>

<script type="text/javascript" src="http://twitter.com/statuses/user_timeline/NLmagazine.json?callback=twitterCallback2&count=1"></script>

   

    <div id="copyrights"><div class="copyrights">Copyright 2011 Target Publishing</div></div>

    <!--FOOTER SECTIONS END -->

<!--JQUERY CLEARN INPUTS START -->

<script type="text/javascript" src="nl/js/jquery-1.4.2.min.js"></script>   

<script type="text/javascript" src="nl/js/clearinputs.js"></script>

<!--JQUERY CLEARN INPUTS END -->

<script type="text/javascript">

var pageTracker = _gat._getTracker("UA-5891647-1");

pageTracker._trackPageview();

</body>

</html>

W
whatalotofrubbish
Inspiring
January 14, 2012

Thats a bit complex to unravel.

Why not set up a simple form with no validation and just one field to update and see if that works?

As it is we cannot see the wood for the trees.

I always try that when I am stumped, and it usually ends up with a result.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices