How do I write UPDATE for SQL in php

I have tried everything I can think of and still get the following error when I try to run this. I’m thinking it’s a minor mistake since I spliced and diced this code from other places but for the sake of me, I can’t seem to figure it out.

Fatal error: Uncaught Exception: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘[‘ADPs’],owner=[”],type=[”],company=[”],status=[‘Active’],soc=[‘yes’],email=[‘ at line 1 in C:xampphtdocsupdateVendor.php:129 Stack trace: #0 C:xampphtdocsupdateVendor.php(129): PDOStatement->execute() #1 {main} in C:xampphtdocsupdateVendor.php:136 Stack trace: #0 {main} thrown in C:xampphtdocsupdateVendor.php on line 136

<?php
session_start();
/* Include the database connection file (remember to change the connection parameters) */
require './db_inc.php';

/* Include the Account class file */
require './account_class.php';
/* Create a new Account object */
$account = new Account();
$user = $_SESSION['username'];
$login = FALSE;
$id = $account->getIdFromName($user);
try
{
    $login = $account->sessionLogin();



}
catch (Exception $e)
{
    echo $e->getMessage();
    die();
}

if ($login)
{
}
else
{
    header('Location: ./index.php');
} 

  $name = $_POST['vendor'];
  $owner = $_POST['owner'];
  $status = $_POST['status'];
  $company = $_POST['company'];
  $email = $_POST['email'];
  $type = $_POST['type'];
  $descr = $_POST['descr'];
  $owner_email = $_POST['owner_email'];

   $inherit = $_POST['inherit'];
   $residual = $_POST['residual'];


    if(isset($_POST['mfa'])){
        $mfa = "1"; 
    } else {
            $mfa = "0";
    }
   if(isset($_POST['policy'])){
        $policy = "1";  
    } else {
            $policy = "0";
    }
      if(isset($_POST['dr'])){
        $dr = "1";  
    } else {
            $dr = "0";
    }
      if(isset($_POST['ir'])){
        $ir = "1";  
    } else {
            $ir = "0";
    }
      if(isset($_POST['media'])){
        $media = "1";   
    } else {
            $media = "0";
    }
      if(isset($_POST['remoteaccess'])){
        $remoteaccess = "1";    
    } else {
            $remoteaccess = "0";
    }


  $otherrisk = $_POST['otherrisk'];
  $other = $_POST['other'];  
  $tier = $_POST['tier'];
  $dept = $_POST['dept'];
  $imp = $_POST['imp'];
  $cloud = $_POST['cloud'];
  $soc = $_POST['soc'];
  $motion = $_POST['motion'];
  $rest = $_POST['rest'];
  if(isset($_POST['baa'])){
        $baa = "1"; 
    } else {
            $baa = "0";
    }
  if(isset($_POST['nda'])){
        $nda = "1"; 
    } else {
            $nda = "0";
    }  
if(isset($_POST['msa'])){
        $msa = "1"; 
    } else {
            $msa = "0";
    }
  if(isset($_POST['phi'])){
        $phi = "1"; 
    } else {
            $phi = "0";
    }
  if(isset($_POST['pii'])){
        $pii = "1"; 
    } else {
            $pii = "0";
    }  
if(isset($_POST['demo'])){
        $demo = "1";    
    } else {
            $demo = "0";
    }


  $other_transfer = $_POST['other'];
  $other_info = $_POST['otherinfo'];

        $query = "UPDATE vendor_data SET name=['$name'],owner=['$owner'],type=['$type'],company=['$company'],status=['$status'],soc=['$soc'],email=['$email'],descr=['$descr'],tier=['$tier'],dept=['$dept'],impl=['$imp'],serv_pro=['$cloud'],baa=['$baa'],in_motion=['$motion'],at_rest=['$rest'],nda=['$nda'],other_transfer=['$other_transfer'],other_info=['$other_info'],msa=['$msa'],phi=['$phi'],pii=['$pii'],demo=['$demo'],owner_email=['$owner_email'],inherit=['$inherit'],residual=['$residual'],policy=['$policy'],mfa=['$mfa'],dr=['$dr'],ir=['$ir'],media=['$media'],remoteaccess=['$remoteaccess'],otherrisk=['$otherrisk'],other=['$other'] WHERE id=['$id']";

        /* Execute the query */
        try
        {
            $res = $pdo->prepare($query);
            $res->execute();


        }
        catch (PDOException $e)
        {
           /* If there is a PDO exception, throw a standard exception */
           throw new Exception($e);
        }


?>

I’m thinking my syntax is totally wrong or I misplaced something but I’ve been staring at this for so long, that I don’t see my error.

Answer

Please, read PDO docs, first.

Especially part with prepare method.

Here, in example, you can see, that all values are presented as pseudo-variables with ‘?’, and then are inserted in accordance with the order in the request.

$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));

So, in prepare method insert your query, like this:

$sth = $dbh->prepare('UPDATE vendor_data SET name = ?, ...');

And in execute method bind params:

$sth->execute([$name, ...]);

Advertisement

Answer