Strange syntax error in form used to insert data to a table.

Question

ebanbury 13 Junior Poster

16 Years Ago

Hello
Now this is very strange and way out of my knowledge-basis.
I had a working form (see code) which inserted data into eph_product.
Suddenly it stopped working and when I click on 'submit' 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 ')' at line 1

Here is the code (and I just can't see what to change to make it work. Most of the script is also exactly like some other scripts I have which are working)....If anyone can see the problem, it would really make me day :-)

<?php require_once('../Connections/erinpubl_localhost.php'); ?>
<?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;
}
}
$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 eph_product (prod_id, prod_name, prod_level, prod_price, prod_status, image, thumbnail, prod_cover, prod_content, prod_exercise1, prod_exercise2) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, $s)",
                       GetSQLValueString($_POST['prod_id'], "text"),
                       GetSQLValueString($_POST['prod_name'], "text"),
                       GetSQLValueString($_POST['prod_level'], "text"),
                       GetSQLValueString($_POST['prod_price'], "text"),
					   GetSQLValueString($_POST['prod_status'], "text"),
					   GetSQLValueString($_POST['image'], "text"),
					   GetSQLValueString($_POST['thumbnail'], "text"),
					   GetSQLValueString($_POST['prod_cover'], "text"),
					   GetSQLValueString($_POST['prod_content'], "text"),
					   GetSQLValueString($_POST['prod_exercise1'], "text"),
					   GetSQLValueString($_POST['prod_exercise2'], "text"));
					   
					   
					   

  mysql_select_db($database_erinpubl_localhost, $erinpubl_localhost);
  $Result1 = mysql_query($insertSQL, $erinpubl_localhost) or die(mysql_error());
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Inserting Product Records</title>
</head>

<body>
<p>Insert a New Product Record</p>
<table width="529" border="1">
  <tr>
    <td>Please enter details of the new product, including full location URL of images.</td>
  </tr>
  <tr>
    <td height="230"><form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
      <table align="left">
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Product ID:</td>
            <td><input type="text" name="prod_id" value="LEAVE BLANK" size="32" /></td>
          </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Prod_name:</td>
            <td><input type="text" name="prod_name" value="" size="32" /></td>
          </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Prod_level:</td>
            <td><input type="text" name="prod_level" value="lower case" size="32" /></td>
          </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">Prod_price:</td>
            <td><input type="text" name="prod_price" value="" size="32" /></td>
          </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">prod_desc</td>
            <td><label for="prod_desc"></label>
            <textarea name="prod_desc" cols="50" id="prod_desc">
</textarea></td>
          </tr>
          <tr valign="baseline">
          <td nowrap="nowrap" align="right">prod_status:</td>
            <td><p>
              <label for="prod_status"></label>
              <select name="prod_status" id="prod_status">
                <option value="Available" selected="selected">Available Now</option>
                <option value="Coming Soon">Coming Soon</option>
                <option value="New">New</option>
              </select>
            </p></td>
          </tr>
           <tr valign="baseline">
            <td nowrap="nowrap" align="right">image:</td>
            <td><input name="image" type="text" value="" size="50" maxlength="80" /></td>
          </tr>
           <tr valign="baseline">
            <td nowrap="nowrap" align="right">thumbnail:</td>
            <td><input name="thumbnail" type="text" value="" size="50" maxlength="80" /></td>
          </tr>
           <tr valign="baseline">
             <td nowrap="nowrap" align="right">prod_cover</td>
             <td><label for="cover"></label>
             <input name="cover" type="text" id="cover" size="50" maxlength="80" /></td>
           </tr>
           <tr valign="baseline">
             <td nowrap="nowrap" align="right">prod__content</td>
             <td><label for="content"></label>
             <input name="content" type="text" id="content" size="50" maxlength="80" /></td>
           </tr>
           <tr valign="baseline">
             <td nowrap="nowrap" align="right">prod_exercise1</td>
             <td><label for="exercise1"></label>
             <input name="exercise1" type="text" id="exercise1" size="50" maxlength="80" /></td>
           </tr>
           <tr valign="baseline">
             <td nowrap="nowrap" align="right">prod_exercise2</td>
             <td><input name="exercise2" type="text" id="exercise2" value="" size="50" maxlength="80" /></td>
           </tr>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">&nbsp;</td>
            <td><input type="submit" value="Insert record" /></td>
          </tr>
          </table>
        <p>
          <input type="hidden" name="MM_insert" value="form1" />
        </p>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
    </form>
    <p>&nbsp;</p></td>
  </tr>
</table>
</body>
</html>

mysql php

3 Contributors
3 Replies
226 Views
10 Hours Discussion Span
Latest Post 16 Years Ago Latest Post by ebanbury

Dani AI

Generated 7 Months Ago

Quick diagnosis and context (ties to earlier replies)
The MySQL error that points at a closing parenthesis usually means the VALUES list contains an invalid token. As suggested, dumping the generated SQL will show the problem; in this case the sprintf format had a typo — a literal $s remained instead of the final %s, so the query ended with ..., $s) which is invalid SQL. was also right to flag quoting/format issues, and ’s Dreamweaver regeneration simply replaced the broken line so the symptom went away.

Concrete steps to fix and verify

Temporarily turn on full error reporting and echo the SQL before it runs:

error_reporting(E_ALL);
ini_set('display_errors', 1);
echo $insertSQL; exit;

Fix the sprintf format string so every column has a matching %s (the single-character typo $s is the culprit).
Ensure form input names match the $_POST keys used for each column. The posted form uses names like cover, content, exercise1 while the insert expects prod_cover, prod_content, prod_exercise1 etc. Either rename the inputs or read the alternative names from $_POST.
Re-run and confirm the echoed SQL looks like a valid INSERT with quoted values.

Safer long-term approach
Move away from deprecated mysql_* functions and use parameterized statements. Example with PDO (keeps the same column set but avoids manual sprintf):

$pdo = new PDO('mysql:host=localhost;dbname=DB;charset=utf8mb4','user','pass');
$sql = 'INSERT INTO eph_product (prod_id, prod_name, prod_level, prod_price, prod_status, image, thumbnail, prod_cover, prod_content, prod_exercise1, prod_exercise2)
        VALUES (:prod_id,:prod_name,:prod_level,:prod_price,:prod_status,:image,:thumbnail,:prod_cover,:prod_content,:prod_exercise1,:prod_exercise2)';
$stmt = $pdo->prepare($sql);
$stmt->execute(array_map(function($k){return $_POST[$k] ?? null;}, 
  ['prod_id','prod_name','prod_level','prod_price','prod_status','image','thumbnail','prod_cover','prod_content','prod_exercise1','prod_exercise2']));

Final notes
Small typos in format strings are common and hard to spot; echoing the final SQL and fixing input-name mismatches will resolve this. Converting to prepared statements prevents this class of bugs and improves security.

All 3 Replies

pritaeas 2,276 Most Valuable Poster

16 Years Ago

Can you show us what's in $insertSQL ?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sami.asanga 0 Light Poster · Answer 1 · 2010-08-20T15:33:22+00:00

I got the same error with an insert query..But it has solved now ,surely you might be missing ""/'' there.Double check $insertSQL..:)

ebanbury 13 Junior Poster · Answer 2 · 2010-08-20T15:56:33+00:00

Your right! In fact I use dreamweaver and found that the only way I could eventually fix this is to delete that part of the script and then use the inbuilt function to 'insert record'
Strange though :-)

Strange syntax error in form used to insert data to a table.

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers