-1

I have this code to update my record and to check if there's any value on the respective input. But it's not even updating my record, and i got this error when i click on submit:

Fatal error: Uncaught 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 '? = ':title', ? = ':keywords' WHERE title = ?'

if(isset($_POST["updateBTN"])){    

  $insert_data = array(
    'keywords'         => $_POST['keywords'],
    'img'              => $_POST['img'],
    'widht'            => $_POST['widht'],
    'status'           => $_POST['status'],
    'name'             => $_POST['name'],
    'height'           => $_POST['height']
  );
    $sets="";

    foreach ($insert_data as $column => $value) {

        if ($value!=""){

            $sets .= $column." = '".$column."', ";

        }
    }
     $sets = rtrim($sets, ', ');

     $query = "UPDATE table SET $sets WHERE title = :title";
     $stmt = $conn->prepare($query);
     $stmt->execute($insert_data);

}

html:

<form  method="post">
<div>
    <input type="text" name="title"> 
    <span data-placeholder="Title"></span>          
</div>
<div>
    <input type="text" name="keywords"> 
    <span data-placeholder="keywords"></span>          
</div>
<div>
    <input type="text" name="img"> 
    <span data-placeholder="img"></span>          
</div>
.
.
.
<button type="submit" name="updateBTN">Send</button>
</form>
mario
  • 367
  • 1
  • 4
  • 17
  • 1
    are you sure that you give right output of `var_dump()`? – Artem Ilchenko Nov 22 '18 at 15:35
  • echo your $query and check if that's the output you are looking for – Ruub Nov 22 '18 at 15:36
  • When you are building your SQL, any string values need to inside quotes (something like `$sets .= $column." = '".$column."', ";`) but I would also recommend using prepared statements instead. – Nigel Ren Nov 22 '18 at 15:38
  • `$query = "UPDATE table SET $sets WHERE title = :title";` ... you need bind the parameter `$query ->bindParam(':title', $title);` – Juan Carlos Oropeza Nov 22 '18 at 16:11
  • @JuanCarlosOropeza I don't think so, since i'm using a `array` instead of binding. I tryed anyways and nothing changed. – mario Nov 22 '18 at 16:22
  • What does `print_r($sets);` show you? – Martin Nov 22 '18 at 16:25
  • Possible duplicate of [Update query with PDO and MySQL](https://stackoverflow.com/questions/18323065/update-query-with-pdo-and-mysql) – Martin Nov 22 '18 at 16:31
  • That's not a reason. Put the `print_r($sets);` **BEFORE** the SQL call. – Martin Nov 22 '18 at 17:00
  • The duplicate is to show you how you **should** be doing SQL Updates with PDO. The way you're doing it is very poor, as you're discovering with these errors.... – Martin Nov 22 '18 at 17:01
  • re: POST: Please do some basic research. https://www.dreamhost.com/blog/php-security-user-validation-sanitization/ – Martin Nov 22 '18 at 17:05
  • @Martin I saw the answer on your link, he showed how to update an entire record, not just a column of a record, that is my goal. – mario Nov 22 '18 at 17:05
  • Please clarify. You can use that code and customise it for your own needs. We are not going to write your code for you. – Martin Nov 22 '18 at 21:31
  • @Martin You meant to customize a simple UPDATE query like this one? `UPDATE talbe SET column = :column WHERE id = :id` WTF!!! – mario Nov 22 '18 at 21:40

1 Answers1

0
  • "Widht" looks like it's spelt wrong. Is that the actual column name?
  • What does print_r($sets); show you?
  • $column values should ideally be encased in backticks
  • I hope you're cleaning that $_POST data!!!
  • Your error states "the right syntax to use near '? = ':title', ? = ':keywords' WHERE title = ?'" which implies the error comes before the first ?. Therefore, review the $sets string output from the bullet above.

And last but not least:

Martin
  • 22,212
  • 11
  • 70
  • 132