[RESOLVED] SQL Error when inserting a new record

[dcjones]

Hi all,

I have the following code which should insert a new record into the database. But when the submit button is hit the following error is displayed.

"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 '-meal, rooms, descript, rating, map, pic, sat_tv, tv, childre"

The Code

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO accom (id, firstname, surname, location, name, address1, address2, address3, address4, address5, tel, fax, email, web, acctype, Tariff, parking, e-meal, rooms, descript, rating, `map`, pic, sat_tv, tv, children, groups, room_fridge, luggage_storage, heating, cooking, rest, bar) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['id'], "int"),
                       GetSQLValueString($_POST['firstname'], "text"),
                       GetSQLValueString($_POST['surname'], "text"),
                       GetSQLValueString($_POST['location'], "text"),
                       GetSQLValueString($_POST['name'], "text"),
                       GetSQLValueString($_POST['address1'], "text"),
                       GetSQLValueString($_POST['address2'], "text"),
                       GetSQLValueString($_POST['address3'], "text"),
                       GetSQLValueString($_POST['address4'], "text"),
                       GetSQLValueString($_POST['address5'], "text"),
                       GetSQLValueString($_POST['tel'], "text"),
                       GetSQLValueString($_POST['fax'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['web'], "text"),
                       GetSQLValueString($_POST['acctype'], "text"),
                       GetSQLValueString($_POST['Tariff'], "text"),
                       GetSQLValueString($_POST['parking'], "text"),
                       GetSQLValueString($_POST['emeal'], "text"),
                       GetSQLValueString($_POST['rooms'], "text"),
                       GetSQLValueString($_POST['descript'], "text"),
                       GetSQLValueString($_POST['rating'], "text"),
                       GetSQLValueString($_POST['map'], "text"),
                       GetSQLValueString($_POST['pic'], "text"),
                       GetSQLValueString(isset($_POST['sat_tv']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['tv']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['children']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['groups']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['room_fridge']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['luggage_storage']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['heating']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['cooking']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['rest']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['bar']) ? "true" : "", "defined","'Y'","'N'"));

  mysql_select_db($database_accomm, $accomm);
  $Result1 = mysql_query($insertSQL, $accomm) or die(mysql_error());

  $insertGoTo = "../index.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>

Can anyone see what the problem is, I've been lookingat this for hours and I think it requires a fresh set of eyes.

Thanks in advance

[kemu_]

just wondering why map is map, while all other fieldnames dont have the `-s around em...

[dcjones]

Hi,

Yes that looked odd so I changed the "e-meal" to "emeal" in the database and the script. Still the same error.

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO accom (id, firstname, surname, location, name, address1, address2, address3, address4, address5, tel, fax, email, web, acctype, Tariff, parking, emeal, rooms, descript, rating, location_map, pic, sat_tv, tv, children, groups, room_fridge, luggage_storage, heating, cooking, rest, bar) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['id'], "int"),
                       GetSQLValueString($_POST['firstname'], "text"),
                       GetSQLValueString($_POST['surname'], "text"),
                       GetSQLValueString($_POST['location'], "text"),
                       GetSQLValueString($_POST['name'], "text"),
                       GetSQLValueString($_POST['address1'], "text"),
                       GetSQLValueString($_POST['address2'], "text"),
                       GetSQLValueString($_POST['address3'], "text"),
                       GetSQLValueString($_POST['address4'], "text"),
                       GetSQLValueString($_POST['address5'], "text"),
                       GetSQLValueString($_POST['tel'], "text"),
                       GetSQLValueString($_POST['fax'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['web'], "text"),
                       GetSQLValueString($_POST['acctype'], "text"),
                       GetSQLValueString($_POST['Tariff'], "text"),
                       GetSQLValueString($_POST['parking'], "text"),
                       GetSQLValueString($_POST['emeal'], "text"),
                       GetSQLValueString($_POST['rooms'], "text"),
                       GetSQLValueString($_POST['descript'], "text"),
                       GetSQLValueString($_POST['rating'], "text"),
                       GetSQLValueString($_POST['location_map'], "text"),
                       GetSQLValueString($_POST['pic'], "text"),
                       GetSQLValueString(isset($_POST['sat_tv']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['tv']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['children']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['groups']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['room_fridge']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['luggage_storage']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['heating']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['cooking']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['rest']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['bar']) ? "true" : "", "defined","'Y'","'N'"));

  mysql_select_db($database_accomm, $accomm);
  $Result1 = mysql_query($insertSQL, $accomm) or die(mysql_error());

  $insertGoTo = "../index.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>

What do you think.

[dcjones]

Hi all

RESOLVED. The code was incorrect, it should have been:

I had not set the checkbox's to display as checkbox's, I made the changes and it now work fine.

Thanks to all you guys & girls who looked at the problem, sorry for waisting you time.