3 Replies Latest reply on Jan 3, 2011 3:23 PM by toad78

    Update Form Giving Me MySQL error

    toad78

      I've never experienced this error before in making Update Froms for my dbase tables. I get this 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 'use=NULL,  entertain=NULL, off_site_cater=NULL, valet_shuttle=NULL, insurance=NUL'  at line 1

       

      My form is as follows:

      <?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_update"])) && ($_POST["MM_update"] == "form1")) {
        $updateSQL = sprintf("UPDATE photo_gallery_cat SET category=%s, est_event=%s, video=%s, city=%s, `state`=%s, `description`=%s, max_occ=%s, int_ext=%s, over_acc=%s, use=%s, entertain=%s, off_site_cater=%s, valet_shuttle=%s, insurance=%s WHERE phcat_id=%s",
                             GetSQLValueString($_POST['category'], "text"),
                             GetSQLValueString($_POST['est_event'], "text"),
                             GetSQLValueString($_POST['video'], "text"),
                             GetSQLValueString($_POST['city'], "text"),
                             GetSQLValueString($_POST['state'], "text"),
                             GetSQLValueString($_POST['description'], "text"),
                             GetSQLValueString($_POST['max_occ'], "text"),
                             GetSQLValueString($_POST['int_ext'], "text"),
                             GetSQLValueString($_POST['over_acc'], "text"),
                             GetSQLValueString($_POST['use'], "text"),
                             GetSQLValueString($_POST['entertain'], "text"),
                             GetSQLValueString($_POST['off_site_cater'], "text"),
                             GetSQLValueString($_POST['valet_shuttle'], "text"),
                             GetSQLValueString($_POST['insurance'], "text"),
                             GetSQLValueString($_POST['phcat_id'], "int"));
      
        mysql_select_db($database_CVconn, $CVconn);
        $Result1 = mysql_query($updateSQL, $CVconn) or die(mysql_error());
      
        $updateGoTo = "gallery_list.php";
        if (isset($_SERVER['QUERY_STRING'])) {
          $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
          $updateGoTo .= $_SERVER['QUERY_STRING'];
        }
        header(sprintf("Location: %s", $updateGoTo));
      }
      
      $colname_getCategory = "-1";
      if (isset($_GET['phcat_id'])) {
        $colname_getCategory = $_GET['phcat_id'];
      }
      mysql_select_db($database_CVconn, $CVconn);
      $query_getCategory = sprintf("SELECT * FROM photo_gallery_cat WHERE phcat_id = %s", GetSQLValueString($colname_getCategory, "int"));
      $getCategory = mysql_query($query_getCategory, $CVconn) or die(mysql_error());
      $row_getCategory = mysql_fetch_assoc($getCategory);
      $totalRows_getCategory = mysql_num_rows($getCategory);
      ?>
      

       

      <body>
      <?php
      // remove escape characters from POST array
      if (PHP_VERSION < 6 && get_magic_quotes_gpc()) {
        function stripslashes_deep($value) {
          $value = is_array($value) ? array_map('stripslashes_deep', $value) : stripslashes($value);
          return $value;
          }
        $_POST = array_map('stripslashes_deep', $_POST);
        }
      ?>
      <div class="box">
      <h1>Update Photo Gallery Category Information</h1>
      <form action="<?php echo $editFormAction; ?>" method="POST" name="form1" id="form1">
      <table align="center">
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Category:</td>
      <td colspan="3"><input name="category"  type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['category'], ENT_COMPAT, 'utf-8'); ?>" size="32" maxlength="50" /></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Estate/Event:</td>
      <td colspan="3"><select name="est_event">
      <option value="Estate" <?php if (!(strcmp("Estate", htmlentities($row_getCategory['est_event'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Estate</option>
      <option value="Event" <?php if (!(strcmp("Event", htmlentities($row_getCategory['est_event'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Event</option>
      </select></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Video URL:</td>
      <td colspan="3"><input name="video"  type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['video'], ENT_COMPAT, 'utf-8'); ?>" size="32" maxlength="100" /> (optional)</td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">City:</td>
      <td><input name="city"  type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['city'], ENT_COMPAT, 'utf-8'); ?>" size="30" maxlength="30" /></td>
      <td nowrap="nowrap" align="right">State:</td>
      <td><select style="width: 50px;" name="state">
      <option value="AK" <?php if (!(strcmp("AK", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>AK</option>
      <option value="AL" <?php if (!(strcmp("AL", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>AL</option>
      <option value="AR" <?php if (!(strcmp("AR", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>AR</option>
      <option value="AZ" <?php if (!(strcmp("AZ", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>AZ</option>
      <option value="CA" <?php if (!(strcmp("CA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>CA</option>
      <option value="CO" <?php if (!(strcmp("CO", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>CO</option>
      <option value="CT" <?php if (!(strcmp("CT", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>CT</option>
      <option value="DC" <?php if (!(strcmp("DC", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>DC</option>
      <option value="DE" <?php if (!(strcmp("DE", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>DE</option>
      <option value="FL" <?php if (!(strcmp("FL", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>FL</option>
      <option value="GA" <?php if (!(strcmp("GA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>GA</option>
      <option value="HI" <?php if (!(strcmp("HI", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>HI</option>
      <option value="IA" <?php if (!(strcmp("IA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>IA</option>
      <option value="ID" <?php if (!(strcmp("ID", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>ID</option>
      <option value="IL" <?php if (!(strcmp("IL", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>IL</option>
      <option value="IN" <?php if (!(strcmp("IN", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>IN</option>
      <option value="KS" <?php if (!(strcmp("KS", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>KS</option>
      <option value="KY" <?php if (!(strcmp("KY", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>KY</option>
      <option value="LA" <?php if (!(strcmp("LA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>LA</option>
      <option value="MA" <?php if (!(strcmp("MA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>MA</option>
      <option value="MD" <?php if (!(strcmp("MD", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>MD</option>
      <option value="ME" <?php if (!(strcmp("ME", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>ME</option>
      <option value="MI" <?php if (!(strcmp("MI", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>MI</option>
      <option value="MN" <?php if (!(strcmp("MN", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>MN</option>
      <option value="MO" <?php if (!(strcmp("MO", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>MO</option>
      <option value="MS" <?php if (!(strcmp("MS", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>MS</option>
      <option value="MT" <?php if (!(strcmp("MT", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>MT</option>
      <option value="NC" <?php if (!(strcmp("NC", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>NC</option>
      <option value="ND" <?php if (!(strcmp("ND", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>ND</option>
      <option value="NE" <?php if (!(strcmp("NE", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>NE</option>
      <option value="NH" <?php if (!(strcmp("NH", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>NH</option>
      <option value="NJ" <?php if (!(strcmp("NJ", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>NJ</option>
      <option value="NM" <?php if (!(strcmp("NM", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>NM</option>
      <option value="NV" <?php if (!(strcmp("NV", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>NV</option>
      <option value="NY" <?php if (!(strcmp("NY", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>NY</option>
      <option value="OH" <?php if (!(strcmp("OH", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>OH</option>
      <option value="OK" <?php if (!(strcmp("OK", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>OK</option>
      <option value="OR" <?php if (!(strcmp("OR", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>OR</option>
      <option value="PA" <?php if (!(strcmp("PA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>PA</option>
      <option value="RI" <?php if (!(strcmp("RI", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>RI</option>
      <option value="SC" <?php if (!(strcmp("SC", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>SC</option>
      <option value="SD" <?php if (!(strcmp("SD", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>SD</option>
      <option value="TN" <?php if (!(strcmp("TN", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>TN</option>
      <option value="TX" <?php if (!(strcmp("TX", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>TX</option>
      <option value="UT" <?php if (!(strcmp("UT", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>UT</option>
      <option value="VA" <?php if (!(strcmp("VA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>VA</option>
      <option value="VT" <?php if (!(strcmp("VT", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>VT</option>
      <option value="WA" <?php if (!(strcmp("WA", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>WA</option>
      <option value="WI" <?php if (!(strcmp("WI", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>WI</option>
      <option value="WV" <?php if (!(strcmp("WV", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>WV</option>
      <option value="WY" <?php if (!(strcmp("WY", htmlentities($row_getCategory['state'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>WY</option>
      </select></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right" valign="top">Description:</td>
      <td colspan="3"><textarea name="description" cols="100" rows="8"><?php echo htmlentities($row_getCategory['description'], ENT_COMPAT, 'utf-8'); ?></textarea></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Maximum Occupancy:</td>
      <td colspan="3"><input name="max_occ" type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['max_occ'], ENT_COMPAT, 'utf-8'); ?>" size="50" maxlength="100" /></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Interior/Exterior Use:</td>
      <td colspan="3"><input name="int_ext" type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['int_ext'], ENT_COMPAT, 'utf-8'); ?>" size="50" maxlength="100" /></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Overnight Accomodations:</td>
      <td colspan="3"><input name="over_acc" type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['over_acc'], ENT_COMPAT, 'utf-8'); ?>" size="50" maxlength="100" /></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right" valign="top">Use:</td>
      <td colspan="3"><textarea name="use" cols="100" rows="8"><?php echo htmlentities($row_getCategory['use'], ENT_COMPAT, 'utf-8'); ?></textarea></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Entertainment:</td>
      <td colspan="3"><input name="entertain" type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['entertain'], ENT_COMPAT, 'utf-8'); ?>" size="50" maxlength="100" /></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Off-Site Catering:</td>
      <td colspan="3"><input name="off_site_cater" type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['off_site_cater'], ENT_COMPAT, 'utf-8'); ?>" size="50" maxlength="100" /></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Valet and/or Shuttle Service:</td>
      <td colspan="3"><input name="valet_shuttle" type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['valet_shuttle'], ENT_COMPAT, 'utf-8'); ?>" size="50" maxlength="100" /></td>
      </tr>
      <tr valign="baseline">
      <td nowrap="nowrap" align="right">Insurance:</td>
      <td colspan="3"><input name="insurance" type="text" style="width: 200px;" value="<?php echo htmlentities($row_getCategory['insurance'], ENT_COMPAT, 'utf-8'); ?>" size="50" maxlength="50" /></td>
      </tr>
      <tr valign="baseline">
      <td colspan="4" align="center" nowrap="nowrap"><input class="btn" type="submit" value="Update" /></td>
      </tr>
      </table>
      <input type="hidden" name="phcat_id" value="<?php echo $row_getCategory['phcat_id']; ?>" />
      <input type="hidden" name="MM_update" value="form1" />
      </form>
      </div>
      <p class="bottom-menu group"><a href="list.php"><img src="../images/list.png" width="32" height="32" alt="LIST" title="" /><br /> 
      Categories
      </a><a href="../men_u/menu.php"><img src="../images/menu.png" width="32" height="32" alt="MENU" title="" /><br />
      Menu
      </a><a href="<?php echo $logoutAction ?>"><img src="../images/log_out.png" width="32" height="32" alt="LOG OUT" title="" /><br />
      Log Out</a></p>
      </body>
      </html>
      

      My dbase table:

      dbasetable.jpg

       

      I would appreciate some insight. My insert form works just fine, but not the update form.

       

      Thank you.