PHP syntax error in UPDATE table statement

Question

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).

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.

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.

After trying to run the following sql code

UPDATE mineraltable SET LocationID='160',ItemTypeID='1',ImageID='6',    DonatorID='4' WHERE ItemID='372'

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

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

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:

"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”

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.

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.

I have followed the answer in How to update foreign key value in mysql database to get the UPDATE statement code. But have also looked at [1]: Syntax error in update statement to troubleshoot the problem, but the example in the latter link was not similar to mine.

Here is the php code for the whole input form.

   $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 .= "<option value-\"$LocationID\">$Site $Region $Country $Continent</option>";

}




   $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 .= "<option value-\"$DonatorID\">$DonatorFN $DonatorLN</option>";

}


   $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 .= "<option value-\"$ItemTypeID\">$ItemType</option>";

}

   $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 .= "<option value-\"$ImageID\">$Image</option>";

}

 $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 .= "<option value-\"$ItemID\">$TrayBoxNo,$ItemInBox,$Name</option>";

}
   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);


?>



  <form method = "post" action = "<?php $_PHP_SELF ?>">
<table>
 <tr>
                    <td>Select Site</td>
                    <td>
                        <select size="10" name="Location" multiple="multiple" id="Location">


                            <?php echo $SiteOptionData; ?>

                        </select>
                    </td>
                </tr>

    <br>


                <tr>
    <td>Select Donator</td>
                    <td>
                        <select size="10" name="Donator" multiple="multiple" id="Donator">


                            <?php echo $DonatorOptionData; ?>

                        </select>
                    </td>
                </tr>

<br>

                <tr>
    <td>Select ItemType</td>
                    <td>
                        <select size="10" name="ItemType" multiple="multiple" id="ItemType">


                            <?php echo $ItemTypeOptionData; ?>

                        </select>
                    </td>
                </tr>

<br>

                <tr>
    <td>Select Image</td>
                    <td>


                        <select size="10" name="Image" multiple="multiple" id="Image">


                        <?php echo $ImageOptionData; ?> 

                        </select>
                    </td>
                </tr>

                <tr>

    <td>Select Item</td>
                    <td>
                        <select size="10" name="Item" multiple="multiple" id="Item">


                            <?php echo $ItemOptionData; ?>

                        </select>
                    </td>
                </tr>


</table>

  <input name="update" type="submit" id="update" value="update">

</form>

Try and remove the line break \n from the query you put into $sql. — stubben
– stubben, Commented Jan 16, 2016 at 10:11
Thanks for the help everyone I have removed the \n and the backslashes and got the code working — Alex Buckley
– Alex Buckley, Commented Jan 16, 2016 at 11:02

vural · Accepted Answer · 2016-01-16 10:12:50Z

1

Try this one :

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

You used double-quote so you shouldn't escape single-quote.

answered Jan 16, 2016 at 10:12

vural

3912 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shailesh Katarmal · Accepted Answer · 2016-01-16 10:19:12Z

0

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

Please remove \n and backslash from query. it is not required.

answered Jan 16, 2016 at 10:19

Shailesh Katarmal

2,7931 gold badge15 silver badges16 bronze badges

1 Comment

Alex Buckley Over a year ago

Thanks for the help I have removed the \n and the backslashes and got the code working

KLin · Accepted Answer · 2016-01-16 10:11:39Z

0

in your query instead of

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

use

"UPDATE mineraltable "
    . "SET LocationID='$LocationID', ItemTypeID='$ItemTypeID', ImageID='$ImageID', DonatorID='$DonatorID' "
    . "WHERE ItemID='$ItemID'"

answered Jan 16, 2016 at 10:11

KLin

4792 silver badges10 bronze badges

Collectives™ on Stack Overflow

PHP syntax error in UPDATE table statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related