insert record behavior problem

Report · Jan 10, 2012

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.

Report · Jan 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.

Report · Jan 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">

<head>

<title>Natural & Complementary Lifestyle</title>

</head>

<body>

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

</ul>

</div></div>

</div>

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

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

</div>

</div>

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

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

</div>

</div>

<h1>Competitions<strong><br />

</strong></h1>

</h1>

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

<tr>

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

<td><p>

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

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

</select>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

<td><p>

</p></td>

</tr>

<tr>

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</p></td>

</tr>

<tr>

<td><p>

</p></td>

</tr>

<tr>

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

<td><p>

</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

</tr>

<tr>

<?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>

</p></td>

</tr>

<tr>

</tr>

<tr>

</tr>

</table>

</form>

<br />

<br />

</strong></h3>

</p>

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

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

</div>

</div>

</div>

<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/terms-and-conditions/">Terms & Conditions</a></li>

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

</ul>

</div>

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

</div>

</div>

<h3>View Latest issue of</h3>

<h4>Natural Lifestyle</h4>

</div>

</div>

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

pageTracker._trackPageview();

</body>

</html>

Report · Jan 13, 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.

Report · Jan 16, 2012

SnakEyez (above) suggested posting the script which is exactly why it's there. I take your point, but the fact is that only one field (Firstname) isn't inserting correctly, the rest of the form comes through fine, which is why, I guess, SE reckoned the fault was in the script.

Report · Jan 16, 2012

After my post above, I was using the update record server behaviour where one of the fields was a text area. The database was already set up and held data, but for some reason, even though I had specified the field to be a text area, it always displayed as a text field with no information showing in just that one field. All the rest showed correctly.

After trying everything, I switched off for tea. Later I created a page to the same database with just that one field on it as a test.

Amazingly it worked as spec. I copied the relevant code from both pages into notepad to see what the difference was. I could see none. So I pasted the new code over the relevant part of the original code and Bingo - all worked fine.

The only thing that I can think of for this was that in between creating the first page and the second one, I switched off the Apache server and Dreamweaver and then started again after tea.

Whatever was causing the problem reset itself.

I find that repeat use of live view and switching between the MySQL and live view sometimes upsets things, and a restart refreshes everything. Just as a stop for tea refreshes me.

Report · Jan 16, 2012

My problem isn't happening on a local server, though – I tested it in MAMP and it was working fine; it was when I switched to the db on the live server that it started returning NULL in the firstname column regardless of whatever was entered in the form... I take your point re resuming after a break, I often do that when I get in a dead-end with something, but I tend to leave things running when I'm testing locally. This has now got me thinking though, whether there's some issue which might have to do with a difference between versions of MySQL that might be running on my local server and my live....

Report · Jan 17, 2012

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?

Report · Jan 18, 2012

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.

Report · Jan 18, 2012

Genius!!! Can't thank you enough for this – obvious once you'd pointed that out, of course – the form fields weren't inserting properly because they were different in the script... that has been driving me crazy for the last week, on and off and I really appreciate your input – many thanks. Have just tested it and everything's working fine now.

Report · Jan 18, 2012

Glad to have been of assistance. What gave me the tip was that earlier I had

a similar problem when I had a file with an underscore _ in its name and I

typed a hiphen - instead. That was reported by the system in MySQL, but

spaces in variable names are a PHP problem I think, and depending on what

version you have, may be just ignored rather than reported as errors.

Have fun

Howard Walker

insert record behavior problem

1 Correct answer