PHP syntax error in UPDATE table statement
<p>I am trying to update a table named mineraltable (which has primary key named ItemID) with foreign keys values from a sourcelocationtable (LocationID), imagetable (ImageID), itemtypetable (ItemTypeID) and donatortable (DonatorID). </p>

<p>I want the user to be able to select location, image, itemtype and donator values from dropdown select boxes the value will be stored in a variable and then the mineraltable will be updated with the foreign key numbers of the value displayed in the dropdown select boxes. </p>

<p>The relationship of the latter 4 tables to the mineraltable is 1-Many therefore I can't use a junction table to hold the foreign keys they must go in the mineral table. </p>

<p>After trying to run the following sql code </p>

<pre><code>UPDATE mineraltable SET LocationID='160',ItemTypeID='1',ImageID='6',    DonatorID='4' WHERE ItemID='372' 
</code></pre>

<p>This is it converted to PHP formatting with my php variables substituted for numerical values. </p>

<pre><code>$sql = "UPDATE mineraltable\n"
    . "SET LocationID=\'$LocationID\', ItemTypeID=\'$ItemTypeID\', ImageID=\'$ImageID\', DonatorID=\'$DonatorID\'\n"
    . "WHERE ItemID=\'ItemID\'" 
</code></pre>

<p>I found that the sql code written into my local server xampp with numerical values runs successfully and updates the foreign key values in the mineraltable, but when  I run the php version of this code in my web browser I get the error: </p>

<blockquote>
  <p>"You have an error in your SQL syntax; check the manual that
  corresponds to your MariaDB server version for the right syntax to use
  near '\'166\', ItemTypeID=\'6\', ImageID=\'11\', DonatorID=\'4\' WHERE
  ItemID=\'371\'' at line 2”</p>
</blockquote>

<p>I have looked at the on StackOverFlow for another method of updating foreign keys I have found that you can temporarily remove the foreign key to execute code and then reapply the foreign key. But this was not recommended. </p>

<p><strong>I have gone through the code multiple times and cannot see any errors. Can someone please tell me as I am new to php coding where the syntax error is being caused? Any constructive answers much appreciated.</strong></p>

<p>I have followed the answer in <a href="https://stackoverflow.com/questions/7541446/how-to-update-foreign-key-value-in-mysql-database">How to update foreign key value in mysql database</a> to get the UPDATE statement code. But have also looked at 
[1]: <a href="https://stackoverflow.com/questions/881573/syntax-error-in-update-statement">Syntax error in update statement</a> to troubleshoot the problem, but the example in the latter link was not similar to mine.</p>

<p>Here is the php code for the whole input form.
    

<pre><code>   $debugMode = true;

   $dbhost = 'localhost';
   $dbuser = 'root';
   $dbpass = '';
   $dbname = 'rockandmineraldb';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass,$dbname);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   echo 'Connected successfully';


   $sql = 'SELECT LocationID,Site,Region,Country,Continent FROM sourcelocationtable';
   mysql_select_db('rockandmineraldb');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

$SiteOptionData="";
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
         $LocationID = $row['LocationID']; 
         $Site = $row['Site'];
         $Region = $row['Region'];
         $Country = $row['Country'];
         $Continent = $row['Continent'];

       $SiteOptionData .= "&lt;option value-\"$LocationID\"&gt;$Site $Region $Country $Continent&lt;/option&gt;";

}




   $sql = 'SELECT DonatorID,DonatorFN,DonatorLN FROM donatortable';
   mysql_select_db('rockandmineraldb');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

$DonatorOptionData="";
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
         $DonatorID = $row['DonatorID']; 
         $DonatorFN = $row['DonatorFN'];
         $DonatorLN = $row['DonatorLN'];


       $DonatorOptionData .= "&lt;option value-\"$DonatorID\"&gt;$DonatorFN $DonatorLN&lt;/option&gt;";

}


   $sql = 'SELECT ItemTypeID,ItemType FROM itemtypetable';
   mysql_select_db('rockandmineraldb');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

$ItemTypeOptionData="";
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
         $ItemTypeID = $row['ItemTypeID']; 
         $ItemType = $row['ItemType'];



       $ItemTypeOptionData .= "&lt;option value-\"$ItemTypeID\"&gt;$ItemType&lt;/option&gt;";

}

   $sql = 'SELECT ImageID,Image FROM imagetable';
   mysql_select_db('rockandmineraldb');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

$ImageOptionData="";
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
         $ImageID = $row['ImageID']; 
         $Image = $row['Image'];

       $ImageOptionData .= "&lt;option value-\"$ImageID\"&gt;$Image&lt;/option&gt;";

}

 $sql = 'SELECT ItemID,TrayBoxNo,ItemInBox,Name FROM mineraltable';
   mysql_select_db('rockandmineraldb');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

$ItemOptionData="";
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
         $ItemID = $row['ItemID']; 
         $TrayBoxNo = $row['TrayBoxNo'];
         $ItemInBox = $row['ItemInBox'];
         $Name = $row['Name'];



       $ItemOptionData .= "&lt;option value-\"$ItemID\"&gt;$TrayBoxNo,$ItemInBox,$Name&lt;/option&gt;";

}
   mysql_free_result($retval);
   echo "Fetched data successfully\n";

if(isset($_POST['Item'])){ $ItemID== $_POST['Item']; } 
if(isset($_POST['Location'])){ $LocationID = $_POST['Location']; } 
if(isset($_POST['ItemType'])){ $ItemTypeID = $_POST['ItemType']; } 
if(isset($_POST['Image'])){ $ImageID = $_POST['Image']; } 
if(isset($_POST['Donator'])){ $DonatorID = $_POST['Donator']; } 


if(isset)

$sql = "UPDATE mineraltable\n"
    . "SET LocationID=\'$LocationID\', ItemTypeID=\'$ItemTypeID\', ImageID=\'$ImageID\', DonatorID=\'$DonatorID\'\n"
    . "WHERE ItemID=\'$ItemID\'";


            mysql_select_db('rockandmineraldb');
            $retval = mysql_query( $sql, $conn );

            if(! $retval ) {
               die('Could not update data: ' . mysql_error());
            }
            echo "Updated data successfully\n";

            mysql_close($conn);


?&gt;



  &lt;form method = "post" action = "&lt;?php $_PHP_SELF ?&gt;"&gt;
&lt;table&gt;
 &lt;tr&gt;
                    &lt;td&gt;Select Site&lt;/td&gt;
                    &lt;td&gt;
                        &lt;select size="10" name="Location" multiple="multiple" id="Location"&gt;


                            &lt;?php echo $SiteOptionData; ?&gt;

                        &lt;/select&gt;
                    &lt;/td&gt;
                &lt;/tr&gt;

    &lt;br&gt;


                &lt;tr&gt;
    &lt;td&gt;Select Donator&lt;/td&gt;
                    &lt;td&gt;
                        &lt;select size="10" name="Donator" multiple="multiple" id="Donator"&gt;


                            &lt;?php echo $DonatorOptionData; ?&gt;

                        &lt;/select&gt;
                    &lt;/td&gt;
                &lt;/tr&gt;

&lt;br&gt;

                &lt;tr&gt;
    &lt;td&gt;Select ItemType&lt;/td&gt;
                    &lt;td&gt;
                        &lt;select size="10" name="ItemType" multiple="multiple" id="ItemType"&gt;


                            &lt;?php echo $ItemTypeOptionData; ?&gt;

                        &lt;/select&gt;
                    &lt;/td&gt;
                &lt;/tr&gt;

&lt;br&gt;

                &lt;tr&gt;
    &lt;td&gt;Select Image&lt;/td&gt;
                    &lt;td&gt;


                        &lt;select size="10" name="Image" multiple="multiple" id="Image"&gt;


                        &lt;?php echo $ImageOptionData; ?&gt; 

                        &lt;/select&gt;
                    &lt;/td&gt;
                &lt;/tr&gt;

                &lt;tr&gt;

    &lt;td&gt;Select Item&lt;/td&gt;
                    &lt;td&gt;
                        &lt;select size="10" name="Item" multiple="multiple" id="Item"&gt;


                            &lt;?php echo $ItemOptionData; ?&gt;

                        &lt;/select&gt;
                    &lt;/td&gt;
                &lt;/tr&gt;


&lt;/table&gt;

  &lt;input name="update" type="submit" id="update" value="update"&gt;

&lt;/form&gt;
</code></pre>