Uncaught mysqli_sql_exception: You have an error in your SQL syntax

I cant get my update button to work, my SQL syntax isnt targeting the datebase properly, this is the syntax im using

$sql = ("UPDATE crud SET name='$name', last='$last', email='$email', birthday='$birthday' WHERE id=$id") or
    die($mysqli->error);

​x
 
$sql = ("UPDATE crud SET name='$name', last='$last', email='$email', birthday='$birthday' WHERE id=$id") or    die($mysqli->error);   ​

i tried using different syntaxs but it doesnt update the database

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

<!DOCTYPE html>
<html>
<head>
  <title>Using PHP and a Database</title>

  <style>
    body {
      font-family: Arial;
      font-size: 14px;
    }
    .person {
      width: 45%;
      float: left;
      border: 1px solid darkgray;
      background-color: lightyellow;
      padding: 10px;
      margin: 10px;
      box-sizing: border-box;
    }
    .person .photo {
      float: left;
      margin-right: 10px;
    }
  </style>
</head>

<body>

   <form action="people-list.php" method="post">
    <input type="hidden" name="id" value="<?php echo $id; ?>">

       <p>
        <label for="firstName">First Name:</label>
        <input type="text" name="name" value="<?php echo $name; ?>" />
    </p>
    <p>
        <label for="lastName">Last Name:</label>
        <input type="text" name="last" value="<?php echo $last; ?>" /> 
    </p>
    <p>
        <label for="emailAddress">Email Address:</label>
        <input type="text" name="email" value="<?php echo $email; ?>" />
    </p>

         <p>
        <label for="emailAddress">birthday</label>
        <input type="text" name="birthday" value="<?php echo $birthday; ?>" />
    </p>
    <input type="submit" value="Submit">

    <button type="submit" class="btn" name="save">save</button>   

                <button type="submit" class="btn" name="savechange">Save Changes</button>   

</form> 



  <h1>Using PHP and a Database</h1>

  <p>Here is a list of all the records in our people table:</p>

  <?php    

$id = 0;



    // first, set general PHP error reporting and mysqli error reporting
  // in order to be sure we will see every error in the script
  ini_set('display_errors',1);
  error_reporting(E_ALL);
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

  // Connect to our MySQL server
  $host = "localhost";
  $user_id = "crud";
  $password = "password";
  $database = "crud";
  $mysqli = new mysqli(
    $host, $user_id, $password, $database
  );

  if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" 
      . $mysqli->connect_errno 
      . ") " 
      . $mysqli->connect_error
    ;
  }
  echo $mysqli->host_info . "n";

  // Create the SQL command


if (isset($_POST['save'])) 
{
$name = $_POST['name'];
$last = $_POST['last'];  
$email = $_POST['email'];
$birthday = $_POST['birthday'];  

$sql = "INSERT INTO crud (name, last, email, birthday)
VALUES ('$name', '$last', '$email', '$birthday')"; 

}


if (isset($_GET['delete'])) 
{
$id = $_GET['delete'];    
$sql = ("DELETE FROM crud WHERE id=$id") or die($mysqli->error()); 


}


 if(isset($_GET['edit'])) {
    $id = $_GET['edit'];


    $results = $mysqli->query("SELECT * FROM crud WHERE id=$id") or die($mysqli->error());
     if ($results){
         $row = $results->fetch_array();
         $name = $row['name'];
         $last = $row['last'];
         $email = $row['email'];
         $birthday = $row['birthday'];
     }

 }



if(isset($_POST['savechange'])) {
       $id = $_POST['id'];
       $name = $_POST['name'];
       $last = $_POST['last'];
       $email = $_POST['email'];
       $birthday = $_POST['birthday'];

    $sql = ("UPDATE crud SET name='$name', last='$last', email='$email', birthday='$birthday' WHERE id=$id") or
        die($mysqli->error);





    $_SESSION['MSG_TYPE'] = "WARNING";

}              

  //     $sql = ("UPDATE crud SET (name, last, email, birthday) VALUES ('$name', '$last', '$email', '$birthday') WHERE id=$id");        
//if (isset($_GET['update'])) 
//{
//$id = $_GET['update'];    
//$sql = ("SELECT FROM crud WHERE id=$id") or die($mysqli->error()); 


//}                   


    if ($mysqli->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $mysqli->error;
}



$sql = "SELECT * FROM crud";
$results = mysqli_query($mysqli, $sql); // First parameter is just return of "mysqli_connect()" function
echo "<br>";
echo "<table border='1'>";
while ($row = mysqli_fetch_assoc($results)) { // Important line !!! Check summary get row on array ..
      echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";    
    echo "<td>" . $row['name'] . "</td>";
        echo "<td>" . $row['last'] . "</td>";
        echo "<td>" . $row['email'] . "</td>";
        echo "<td>" . $row['birthday'] . "</td>";

         echo "<td><a href='people-list.php?delete=".$row['id']."'>delete</a></td>";
         echo "<td><a href='people-list.php?edit=".$row['id']."'>update</a></td>";


        //"htmlspecialchars()" function. 

     echo "</tr>";
}


    $mysqli->close();



    ?>

</body>
</html>


sql

-- PHP Version: 7.2.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `crud`
--

-- --------------------------------------------------------

--
-- Table structure for table `crud`
--

CREATE TABLE `crud` (
  `id` int(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `last` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `birthday` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `crud`
--

INSERT INTO `crud` (`id`, `name`, `last`, `email`, `birthday`) VALUES
(21, '', '', '', ''),
(22, '', '', '', ''),
(23, 'hhh', 'hhh', 'hh', 'jjj'),
(26, 'hi', 'ff', 'ff', 'dd'),
(27, '111', '222', '222', '222'),
(28, 'ddd', 'sss', 'sss', 'ddd'),
(29, 'hhh', 'hhh', 'hhh', 'hhh'),
(33, 'nnn', 'fffqf', 'fff', 'fff'),
(34, 'nnnn', 'nn', 'nnn', 'nnn'),
(35, 'ssaa', 'sss', 'sss', 'sss'),
(36, 'hi ', 'fff', 'fff', 'fff'),
(37, 'hh', 'hh', 'hh', 'hh');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `crud`
--
ALTER TABLE `crud`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `crud`
--
ALTER TABLE `crud`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 
<!DOCTYPE html><html><head>  <title>Using PHP and a Database</title>​  <style>    body {      font-family: Arial;      font-size: 14px;    }    .person {      width: 45%;      float: left;      border: 1px solid darkgray;      background-color: lightyellow;      padding: 10px;      margin: 10px;      box-sizing: border-box;    }    .person .photo {      float: left;      margin-right: 10px;    }  </style></head>​<body>​   <form action="people-list.php" method="post">    <input type="hidden" name="id" value="<?php echo $id; ?>">​       <p>        <label for="firstName">First Name:</label>        <input type="text" name="name" value="<?php echo $name; ?>" />    </p>    <p>        <label for="lastName">Last Name:</label>        <input type="text" name="last" value="<?php echo $last; ?>" />     </p>    <p>        <label for="emailAddress">Email Address:</label>        <input type="text" name="email" value="<?php echo $email; ?>" />    </p>​         <p>        <label for="emailAddress">birthday</label>        <input type="text" name="birthday" value="<?php echo $birthday; ?>" />    </p>    <input type="submit" value="Submit">​    <button type="submit" class="btn" name="save">save</button>   ​                <button type="submit" class="btn" name="savechange">Save Changes</button>   ​</form> ​​​  <h1>Using PHP and a Database</h1>​  <p>Here is a list of all the records in our people table:</p>​  <?php    ​$id = 0;​​​    // first, set general PHP error reporting and mysqli error reporting  // in order to be sure we will see every error in the script  ini_set('display_errors',1);  error_reporting(E_ALL);  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);​  // Connect to our MySQL server  $host = "localhost";  $user_id = "crud";  $password = "password";  $database = "crud";  $mysqli = new mysqli(    $host, $user_id, $password, $database  );​  if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: ("       . $mysqli->connect_errno       . ") "       . $mysqli->connect_error    ;  }  echo $mysqli->host_info . "n";​  // Create the SQL command​​if (isset($_POST['save'])) {$name = $_POST['name'];$last = $_POST['last'];  $email = $_POST['email'];$birthday = $_POST['birthday'];  ​$sql = "INSERT INTO crud (name, last, email, birthday)VALUES ('$name', '$last', '$email', '$birthday')"; ​}​​if (isset($_GET['delete'])) {$id = $_GET['delete'];    $sql = ("DELETE FROM crud WHERE id=$id") or die($mysqli->error()); ​​}​​ if(isset($_GET['edit'])) {    $id = $_GET['edit'];​​    $results = $mysqli->query("SELECT * FROM crud WHERE id=$id") or die($mysqli->error());     if ($results){         $row = $results->fetch_array();         $name = $row['name'];         $last = $row['last'];         $email = $row['email'];         $birthday = $row['birthday'];     }​ }​​​if(isset($_POST['savechange'])) {       $id = $_POST['id'];       $name = $_POST['name'];       $last = $_POST['last'];       $email = $_POST['email'];       $birthday = $_POST['birthday'];​    $sql = ("UPDATE crud SET name='$name', last='$last', email='$email', birthday='$birthday' WHERE id=$id") or        die($mysqli->error);​​​​​    $_SESSION['MSG_TYPE'] = "WARNING";​}              ​  //     $sql = ("UPDATE crud SET (name, last, email, birthday) VALUES ('$name', '$last', '$email', '$birthday') WHERE id=$id");        //if (isset($_GET['update'])) //{//$id = $_GET['update'];    //$sql = ("SELECT FROM crud WHERE id=$id") or die($mysqli->error()); ​​//}                   ​​    if ($mysqli->query($sql) === TRUE) {    echo "New record created successfully";} else {    echo "Error: " . $sql . "<br>" . $mysqli->error;}​​​$sql = "SELECT * FROM crud";$results = mysqli_query($mysqli, $sql); // First parameter is just return of "mysqli_connect()" functionecho "<br>";echo "<table border='1'>";while ($row = mysqli_fetch_assoc($results)) { // Important line !!! Check summary get row on array ..      echo "<tr>";    echo "<td>" . $row['id'] . "</td>";        echo "<td>" . $row['name'] . "</td>";        echo "<td>" . $row['last'] . "</td>";        echo "<td>" . $row['email'] . "</td>";        echo "<td>" . $row['birthday'] . "</td>";​         echo "<td><a href='people-list.php?delete=".$row['id']."'>delete</a></td>";         echo "<td><a href='people-list.php?edit=".$row['id']."'>update</a></td>";​​        //"htmlspecialchars()" function. ​     echo "</tr>";}​​    $mysqli->close();​​​    ?>​</body></html>​​sql​-- PHP Version: 7.2.1​SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";SET AUTOCOMMIT = 0;START TRANSACTION;SET time_zone = "+00:00";​​/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8mb4 */;​---- Database: `crud`--​-- --------------------------------------------------------​---- Table structure for table `crud`--​CREATE TABLE `crud` (  `id` int(255) NOT NULL,  `name` varchar(100) NOT NULL,  `last` varchar(100) NOT NULL,  `email` varchar(100) NOT NULL,  `birthday` varchar(100) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;​---- Dumping data for table `crud`--​INSERT INTO `crud` (`id`, `name`, `last`, `email`, `birthday`) VALUES(21, '', '', '', ''),(22, '', '', '', ''),(23, 'hhh', 'hhh', 'hh', 'jjj'),(26, 'hi', 'ff', 'ff', 'dd'),(27, '111', '222', '222', '222'),(28, 'ddd', 'sss', 'sss', 'ddd'),(29, 'hhh', 'hhh', 'hhh', 'hhh'),(33, 'nnn', 'fffqf', 'fff', 'fff'),(34, 'nnnn', 'nn', 'nnn', 'nnn'),(35, 'ssaa', 'sss', 'sss', 'sss'),(36, 'hi ', 'fff', 'fff', 'fff'),(37, 'hh', 'hh', 'hh', 'hh');​---- Indexes for dumped tables--​---- Indexes for table `crud`--ALTER TABLE `crud`  ADD PRIMARY KEY (`id`);​---- AUTO_INCREMENT for dumped tables--​---- AUTO_INCREMENT for table `crud`--ALTER TABLE `crud`  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;COMMIT;​/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;​

Answer

This is just an example using your SQL. This takes no more time to code than what you started with.

<?php    
error_reporting(E_ALL);
ini_set('display_errors', true);

    $host = '127.0.0.1';
    $db   = 'test';
    $user = 'admin';
    $pass = 'howtoforge';

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    try{
        $con = mysqli_connect($host, $user, $pass, $db);
    }catch (Exception $e){
        echo $e->getMessage();
        exit;
    }

    $name = 'NameF'; 
    $last = 'O'Conner';
    $email = 'none@none.com';
    $birthday = '12/01/9999';
    $id = '1';

    try{
    $stmt = $con->prepare('
     UPDATE
        crud 
     SET
        name     = ?, 
        last     = ?, 
        email    = ?, 
        birthday = ?
     WHERE 
        id = ?;
    ');
    $stmt->bind_param('ssssi', $name, $last, $email, $birthday, $id);
    $stmt->execute();
    }catch (Exception $e){
        echo $e->getMessage();
        exit;
    }

 
<?php    error_reporting(E_ALL);ini_set('display_errors', true);​    $host = '127.0.0.1';    $db   = 'test';    $user = 'admin';    $pass = 'howtoforge';​    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);    try{        $con = mysqli_connect($host, $user, $pass, $db);    }catch (Exception $e){        echo $e->getMessage();        exit;    }​    $name = 'NameF';     $last = 'O'Conner';    $email = 'none@none.com';    $birthday = '12/01/9999';    $id = '1';​    try{    $stmt = $con->prepare('     UPDATE        crud      SET        name     = ?,         last     = ?,         email    = ?,         birthday = ?     WHERE         id = ?;    ');    $stmt->bind_param('ssssi', $name, $last, $email, $birthday, $id);    $stmt->execute();    }catch (Exception $e){        echo $e->getMessage();        exit;    }​

Advertisement

Answer