Question 42352 in PHP Question posted in
Official manual can be found here.

PHP Query Update Data

This is the error im getting:

UPDATE problemas SET situacao=’Concluido’ WHERE id=? 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 ‘?’ at line 1

I tried a lot of things and only managed to work with the code bellow thanks to everyone

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "oil";

$id = 'id';

try {
  $link = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

  $link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "UPDATE problemas SET situacao='Concluido' WHERE id=$id";

  $stmt = $link->prepare($sql);

  $stmt->execute();

  header("Location:verTodos.php");
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$link = null;

?>

    Thats what you need to do to solve

    UPDATE problemas SET situacao='Concluido' WHERE id=? 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 '?' at line 1


  2. 0

    You are close with your own answer. As Slava Rozhnev points out though your code is open to SQL injection. In your question you are using a placeholder in your query. When you prepare the statement you tell PDO: whatever I put in there, do not execute it, it is just data. After preparing you can bind values to the placeholders. This can be done with bindValue or bindParam or even with execute()

    <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "oil";

$id = 'id';
$sql = "UPDATE problemas SET situacao='Concluido' WHERE id=?";
try {
    $link = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    $link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $link->prepare($sql);
    $stmt->bindValue(1, $id);

    $stmt->execute();

    header("Location:verTodos.php");
} catch (PDOException $e) {

    echo $sql . "<br>" . $e->getMessage();
}

$link = null;

?>

    The line $stmt->bindValue(1, $id) reads the current value and replaces the placeholder with it. If you were to use a loop, and the $id would be everchanging you could use $stmt-bindParam(1, $id) this reads the current value of the $id variable at the moment of executing the query. Another option would be to remove the bindValue call and let execute bind the values. This can be done by adding an array of values as parameter to the execute call. In your case that would be $stmt->execute([$id]). My personal preference goes to execute for that, since it is much clearer than adding a load of bindValue calls before the execute.

    Also, note that I have moved the assignment of the $sql variable outside of the try-catch block. This is because you want to use the variable in the catch, which might be problematic if the PDOException got thrown by the constructor of PDO(on the line $link = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search