Ajax Won't Insert A New Record Into A Mysql Table

[Greywacke]

Hi there,For some reason AJAX won't add a new record to the 5_suppliers table. The correct value's are sent/requested in the ajax, I believe the javascript generated SQL query is correct from syntax to table names and field names and values - no errors on the client side whatsoever. I've checked the code, server, username , password and db are correct, and it retrieves the existing records no problem.The page I am working on is local so here is the xml response generator, followed by the js file.

<?php$q = $_GET["q"];if (is_numeric($q)) {	if ($q > 3 || $q < 0) {		$q = 0;	}} else {	$q = 0;}$sql = "";$tsql = "";function getrecords() {	$sql = "SELECT * FROM 5_suppliers ORDER BY text_SupplierName ASC;\n";	$GLOBALS["sql"] .= $sql;	$result = mysql_query($sql);	if ($result) {		while ($row = mysql_fetch_array($result)) {			echo "	<supplier>\n";			echo "		<supplierid>" . $row["bigint_SupplierID"] . "</supplierid>\n";			echo "		<suppliername>" . $row["text_SupplierName"] . "</suppliername>\n";			echo "		<supplierw3>" . $row["text_SupplierW3"] . "</supplierw3>\n";			echo "		<contactfname>" . $row["text_ContactFirstName"] . "</contactfname>\n";			echo "		<contactsname>" . $row["text_ContactSurname"] . "</contactsname>\n";			echo "		<contactpos>" . $row["text_ContactPosition"] . "</contactpos>\n";			echo "		<contactemail>" . $row["text_ContactE-mail"] . "</contactemail>\n";			echo "		<contacttel>" . $row["bigint_ContactTel"] . "</contacttel>\n";			echo "		<contactcurbal>" . $row["bigint_CurrentBalance"] . "</contactcurbal>\n";			echo "	</supplier>\n";		}	}}$conn = mysql_connect("localhost", "root", "");//$conn = mysql_connect("localhost", "*****", "*****");if (!$conn) {	die("Could not connect: " . mysql_error());}mysql_select_db("ferr3ty0_ferrety", $conn);switch ($q) {	case 1: // add supplier		$snam = $_POST["text_suppliername"];		$snam = mysql_real_escape_string($snam);		$sw3a = $_POST["text_supplierw3address"];		$sw3a = mysql_real_escape_string($sw3a);		$cfnm = $_POST["text_contactfirstname"];		$cfnm = mysql_real_escape_string($cfnm);		$csnm = $_POST["text_contactsurname"];		$csnm = mysql_real_escape_string($csnm);		$cpos = $_POST["text_contactposition"];		$cpos = mysql_real_escape_string($cpos);		$ceml = $_POST["text_contactemail"];		$ceml = mysql_real_escape_string($ceml);		$ctel = $_POST["text_contacttelephonenumber"];		$ctel = mysql_real_escape_string($ctel);		$cbal = $_POST["text_creditbalance"];		$cbal = mysql_real_escape_string($cbal);		$tsql = "INSERT INTO 5_suppliers (text_SupplierName,text_SupplierW3,text_ContactFirstName,text_ContactSurname,text_ContactPosition,text_ContactE-mail,bigint_ContactTel,bigint_CurrentBalance) VALUES (\"".$snam."\",\"".$sw3a."\",\"".$cfnm."\",\"".$csnm."\",\"".$cpos."\",\"".$ceml."\",".$ctel.",".$cbal.");\n";		$sql .= $tsql;		$result = mysql_query($tsql);		break;	case 2: // modify supplier				break;	case 3: // remove supplier				break;	default: // (re)load form}header("Content-type: text/xml");echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n";echo "<root>\n";getrecords();echo "	<sql>" . $sql . "</sql>\n";echo "</root>";mysql_close($conn);?>

// Regions AJAX & DHTML// xml requestvar http_request = false;var parents = new Array();function makeRequest(method, url, parameters) {	http_request = false;	if (window.XMLHttpRequest) { // Mozilla, Safari,...		http_request = new XMLHttpRequest();		if (http_request.overrideMimeType) {			// set type accordingly to anticipated content type			http_request.overrideMimeType('text/xml');			//http_request.overrideMimeType('text/html');		}	} else if (window.ActiveXObject) { // IE		try {			http_request = new ActiveXObject("Msxml2.XMLHTTP");		} catch (e) {		try {			http_request = new ActiveXObject("Microsoft.XMLHTTP");		} catch (e) {}		}	}	if (!http_request) {		alert('Cannot create XMLHTTP instance');		return false;	}	http_request.onreadystatechange = alertContents;	url += (method=="GET")?parameters:"";	http_request.open(method, url, true);	if (method == "POST") {		http_request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");		http_request.setRequestHeader("Content-length", parameters.length);		http_request.setRequestHeader("Connection", "close");	}	http_request.send((method=="GET")?null:parameters);}// xml parserfunction alertContents() {	if (http_request.readyState == 4) {		if (http_request.status == 200) {			var xmldoc = http_request.responseXML;			var rows = xmldoc.getElementsByTagName("supplier");			for (var r = 0; r < rows.length; r++) {				var i = 0;				var len = parseInt(rows[r].childNodes.length / 2);				var arr = new Array(len);				for (var c = 0; c < rows[r].childNodes.length; c++) {					var supplier = rows[r].childNodes[c];					if (supplier.childNodes.length > 0) {						arr[i] = supplier.firstChild.data;						i++;					}				}				//alert(arr);				addrecord("list_suppliers", arr);			}			if (xmldoc.getElementsByTagName("sql")[0]) if (xmldoc.getElementsByTagName("sql")[0].firstChild) var sql = xmldoc.getElementsByTagName("sql")[0].firstChild.data; if (sql != "") alert(sql);		} else {			alert('There was a problem with the request.');		}		document.getElementById("ajaxbg").style.visibility = "hidden";	}}// instantiate ajax requestsfunction ajaxRequest(obj) {	document.getElementById("ajaxbg").style.visibility = "visible";	switch (obj.value) {		case "Add":			if (trim(document.form_suppliers.text_suppliername.value) == "") {				alert("Cannot create a blank supplier!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.text_suppliername.focus();				return false;			}			for (var i = 0; i < document.form_suppliers.list_suppliers.options.length; i++) {				if (trim(document.form_suppliers.text_suppliername.value).toLowerCase() ==trim(document.form_suppliers.list_suppliers.options[i].text).toLowerCase()) {					alert("Cannot create a duplicate supplier!");					document.getElementById("ajaxbg").style.visibility = "hidden";					document.form_suppliers.list_suppliers.focus();					return false;				}			}			if (trim(document.form_suppliers.text_supplierw3address.value) == "") {				if (!confirm("Are you sure you want to leave\nthe supplier's web address blank?")) {					document.getElementById("ajaxbg").style.visibility = "hidden";					document.form_suppliers.text_supplierw3address.focus();					return false;				}			}			if (trim(document.form_suppliers.text_contactfirstname.value) == "") {				alert("The contact's first name must be supplied!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.text_contactfirstname.focus();				return false;			}			if (trim(document.form_suppliers.text_contactsurname.value) == "") {				alert("The contact's surname must be supplied!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.text_contactsurname.focus();				return false;			}			if (trim(document.form_suppliers.text_contactposition.value) == "") {				alert("The contact's position must be supplied!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.text_contactposition.focus();				return false;			}			if (trim(document.form_suppliers.text_contactemail.value) == "") {				if (!confirm("Are you sure you want to leave\nthe contact's e-mail address blank?")) {					document.getElementById("ajaxbg").style.visibility = "hidden";					document.form_suppliers.text_contactemail.focus();					return false;				}			}			if (trim(document.form_suppliers.text_contacttelephonenumber.value) == "") {				if (!confirm("Are you sure you want to leave\nthe contact's phone numberlol blank?")) {					document.getElementById("ajaxbg").style.visibility = "hidden";					document.form_suppliers.text_contacttelephonenumber.focus();					return false;				}			}			if (parseInt(document.form_suppliers.text_creditbalance.value) < 0) {				alert("Can't start a supplier account with a negative PAYG balance!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.text_contactposition.focus();				return false;			}			var poststr = "text_suppliername=" + encodeURIComponent(document.form_suppliers.text_suppliername.value) +						  "&text_supplierw3address=" + encodeURIComponent(document.form_suppliers.text_supplierw3address.value) +						  "&text_contactfirstname=" + encodeURIComponent(document.form_suppliers.text_contactfirstname.value) +						  "&text_contactsurname=" + encodeURIComponent(document.form_suppliers.text_contactsurname.value) +						  "&text_contactposition=" + encodeURIComponent(document.form_suppliers.text_contactposition.value) +						  "&text_contactemail=" + encodeURIComponent(document.form_suppliers.text_contactemail.value) +						  "&text_contacttelephonenumber=" + encodeURIComponent(parseInt(document.form_suppliers.text_contacttelephonenumber.value)) +						  "&text_creditbalance=" + encodeURIComponent(parseInt(document.form_suppliers.text_creditbalance.value));			makeRequest("POST", "scripts/ajax_suppliers.php?q=1", poststr);			document.form_suppliers.list_suppliers.options.length = 0;			document.form_suppliers.text_suppliername.value = "";			document.form_suppliers.text_supplierw3address.value = "";			document.form_suppliers.text_contactfirstname.value = "";			document.form_suppliers.text_contactsurname.value = "";			document.form_suppliers.text_contactposition.value = "";			document.form_suppliers.text_contactemail.value = "";			document.form_suppliers.text_contacttelephonenumber.value = "";			document.form_suppliers.text_creditbalance.value = "";			break;		case "Modify":			if (document.form_suppliers.list_regions.selectedIndex == -1) {				alert("Please select a region to modify!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.list_regions.focus();				return false;			}			if (document.form_suppliers.list_regions.options[document.form_suppliers.list_regions.selectedIndex].value.split(",")[0] == document.form_suppliers.menu_parentregion.options[document.form_suppliers.menu_parentregion.selectedIndex].value) {				alert("Cannot set the region as it's own parent!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.menu_regions.focus();				return false;			}			if (trim(document.form_suppliers.text_regionname.value) == "") {				alert("Cannot make a region blank!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.text_regionname.focus();				return false;			}			var poststr = "list_regions=" + encodeURIComponent(document.form_suppliers.list_regions.options[document.form_suppliers.list_regions.selectedIndex].value.split(",")[0]) +						  "&menu_parentregion=" + encodeURIComponent(document.form_suppliers.menu_parentregion.options[document.form_suppliers.menu_parentregion.selectedIndex].value) +						  "&text_regionname=" + encodeURIComponent(trim(document.form_suppliers.text_regionname.value));			makeRequest("POST", "scripts/ajax_suppliers.php?q=2", poststr);			document.form_suppliers.list_regions.options.length = 0;			document.form_suppliers.menu_parentregion.length = 1;			document.form_suppliers.text_regionname.value = "";			break;		case "Remove":			if (document.form_suppliers.list_regions.selectedIndex == -1) {				alert("Please select a region to remove!");				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.list_regions.focus();				return false;			}			var l = document.form_suppliers.list_regions.selectedIndex;			var t = ltrim(document.form_suppliers.list_regions.options[l].text);			if (!confirm("Are you sure you want to remove " + t + "?")) {				document.getElementById("ajaxbg").style.visibility = "hidden";				document.form_suppliers.list_regions.focus();				return false;			}			var poststr = "list_regions=" + encodeURIComponent(document.form_suppliers.list_regions.options[document.form_suppliers.list_regions.selectedIndex].value.split(",")[0]);			makeRequest("POST", "scripts/ajax_suppliers.php?q=3", poststr);			document.form_suppliers.list_regions.options.length = 0;			document.form_suppliers.menu_parentregion.length = 1;			document.form_suppliers.text_regionname.value = "";			break;		default:			makeRequest("GET", "scripts/ajax_suppliers.php?q=0", "");			document.form_suppliers.list_suppliers.options.length = 0;			document.form_suppliers.text_suppliername.value = "";			document.form_suppliers.text_supplierw3address.value = "";			document.form_suppliers.text_contactfirstname.value = "";			document.form_suppliers.text_contactsurname.value = "";			document.form_suppliers.text_contactposition.value = "";			document.form_suppliers.text_contactemail.value = "";			document.form_suppliers.text_contacttelephonenumber.value = "";			document.form_suppliers.text_creditbalance.value = "";	}}// string trim functionsfunction trim(str, chars) {	return ltrim(rtrim(str, chars), chars);}function ltrim(str, chars) {	if (chars == undefined) var chars = "";	chars = chars || "\\s";	return str.replace(new RegExp("^[" + chars + "]+", "g"), "");}function rtrim(str, chars) {	if (chars == undefined) var chars = "";	chars = chars || "\\s";	return str.replace(new RegExp("[" + chars + "]+$", "g"), "");}// leftpadding for tree displayfunction strRepeat(str, len) {	var ret = "";	for (i = 0; i < len; i++) {		ret += str;	}	return ret;}// dynamic updatersfunction addrecord(id, arr) {	var opt1 = document.createElement('option');	var opt2 = document.createElement('option');	//alert(arr[2]);	opt1.text = arr[1];				// supplier name	opt1.value = arr[0] +			// supplier id			     "," + arr[2] +		// supplier w3				 "," + arr[3] +		// supplier contact first name				 "," + arr[4] +		// supplier contact surname				 "," + arr[5] +		// supplier contact position				 "," + arr[6] +		// supplier contact e-mail				 "," + arr[7] +		// supplier contact telephone				 "," + arr[8];		// supplier current balance	var sel1 = document.getElementById("list_suppliers");	try {		sel1.add(opt1, null); // standards compliant; doesn't work in IE	}	catch(ex) {		sel1.add(opt1); // IE only	}}// onload event handlerwindow.onload = function () {	return ajaxRequest(document.form_suppliers.button_reset);}// onselect event handlerdocument.form_suppliers.list_suppliers.onchange = function () {	var val = this.options[this.selectedIndex].value.split(",");	//alert(val);	document.form_suppliers.text_suppliername.value = ltrim(this.options[this.selectedIndex].text,"");	document.form_suppliers.text_supplierw3address.value = val[1];	document.form_suppliers.text_contactfirstname.value = val[2];	document.form_suppliers.text_contactsurname.value = val[3];	document.form_suppliers.text_contactposition.value = val[4];	document.form_suppliers.text_contactemail.value = val[5];	document.form_suppliers.text_contacttelephonenumber.value = val[6];	document.form_suppliers.text_creditbalance.value = val[7];}// onkeypress event handlerdocument.form_suppliers.text_creditbalance.onkeypress = function (e) {     if (!e) var e = window.event;     if ([e.keyCode||e.which]==8||[e.keyCode||e.which]==46) //this is to allow backspace or delete          return true;     if ([e.keyCode||e.which] < 48 || [e.keyCode||e.which] > 57)          e.preventDefault? e.preventDefault() : e.returnValue = false;}

Thanks in advance for any help and ideas, even if its only a nudge in the right direction.

[Greywacke]

okay the problem is a sql error i believe is to do with the text_E-mail column. how would i specify this? below is an excerpt of the current php code.

switch ($q) {	case 1: // add supplier		$snam = $_POST["text_suppliername"];				// supplier name		$snam = mysql_real_escape_string($snam);		$sw3a = $_POST["text_supplierw3address"];			// supplier www address		$sw3a = mysql_real_escape_string($sw3a);		$cfnm = $_POST["text_contactfirstname"];			// supplier contact's first name		$cfnm = mysql_real_escape_string($cfnm);		$csnm = $_POST["text_contactsurname"];				// supplier contact's surname		$csnm = mysql_real_escape_string($csnm);		$cpos = $_POST["text_contactposition"];				// supplier contact's position		$cpos = mysql_real_escape_string($cpos);			$ceml = $_POST["text_contactemail"];			// supplier contact's email address		$ceml = mysql_real_escape_string($ceml);		$ctel = $_POST["text_contacttelephonenumber"];		// supplier contact's phone number		$ctel = mysql_real_escape_string($ctel);		$cbal = $_POST["text_creditbalance"];				// supplier's PAYG credit balance		$cbal = mysql_real_escape_string($cbal);		$tsql = "INSERT INTO 5_suppliers (text_SupplierName, text_SupplierW3, text_ContactFirstName, text_ContactSurname, text_ContactPosition, 'text_ContactE-mail', bigint_ContactTel, bigint_CurrentBalance) VALUES (\"".$snam."\", \"".$sw3a."\", \"".$cfnm."\", \"".$csnm."\", \"".$cpos."\", \"".$ceml."\", ".$ctel.", ".$cbal.");\n";		$sql .= $tsql;		$result = mysql_query($tsql);		$err = mysql_error();		$sql .= $err .= "\n";		break;

[Greywacke]

seem to have found my solution, enclosing any names using a hyphen (-) in back slanting single quotes eg. (`text_ContactE-mail`)query solved