-2

I am trying to make an update in Mysql with php. I wrote the following:

include "db.php";

function cliffhangers(){
global $connection;
$query = "SELECT * FROM users ";
$result = mysqli_query($connection, $query);
if (!$result) {
    die('Query FAILED' . mysqli_error());
}

while($row = mysqli_fetch_assoc($result)){
$id = $row['ID'];
echo "<option value = 'id'>$id</option>";
}
    if(isset($_POST['submit'])){
       $email = $_POST['email'];
       $password =  $_POST['password'];
       $id = $_POST['cliffhangers_id'];
       $query = "UPDATE users SET email = '$email', password = '$password' WHERE ID = $id ";
       $result = mysqli_query($connection, $query);
       if (!$result) {
       die('Query FAILED' . mysqli_error($connection));
       }
    }

and I am getting the following syntax error:

Query FAILEDYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

What am I doing wrong?

I expected for the email and password to update, but I am getting the syntax error instead.

Mike Orr
  • 1
  • 1
  • My guess is that `$id` is empty. – Barmar Aug 23 '23 at 16:04
  • Your `$id` is probably empty. – aynber Aug 23 '23 at 16:05
  • That's why you should never* add a PHP variable directly to SQL by only though a parameter – Your Common Sense Aug 23 '23 at 16:05
  • 2
    You are wide open for SQL injection. Since you're using mysqli, take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). **This will take care of any pesky quoting issues that may occur.** It will also fix your syntax error with your id, but you'll still need to fix the id – aynber Aug 23 '23 at 16:05
  • Thanks for replies! This is my first attempt at doing an UPDATE. Yes it was the $id, I had it named wrong on the form. – Mike Orr Aug 23 '23 at 16:09
  • Also, please don't store passwords in plain text - that is another security risk. Learn about [password hashing](https://www.php.net/manual/en/faq.passwords.php) instead. See also [How to use PHP's password_hash to hash and verify passwords](https://stackoverflow.com/questions/30279321/how-to-use-phps-password-hash-to-hash-and-verify-passwords) – ADyson Aug 23 '23 at 16:16
  • I have no idea why anyone thinks writing a login system is a suitable topic for an inexperienced or beginner programmer. There are so many ways to get it badly wrong (some of which are demonstrated in this code), and given what the functionality is, it's very important not to get _any_ of it wrong. If you need such a feature in your application, use an existing well-known framework or plugin. Then, focus your energies on learning PHP properly and adding some genuine value to your application with a novel feature, not something you can easily re-use a (reliable, well-made) product for. – ADyson Aug 23 '23 at 16:17
  • (Even if you never plan to use this code in a real application, it's not a good idea to learn the wrong way of doing things - bad habits are harder to unlearn later) – ADyson Aug 23 '23 at 16:17
  • But now it's updating all of the rows to the same thing. I wanted it to just update one row – Mike Orr Aug 23 '23 at 16:17
  • You must have made some mistake then. [edit] the question and show us your latest code. You should just have used the same SQL, but converted it to prepared statements and parameters. It sounds like you _might_ have removed the WHERE clause or something like that, which would of course cause it to update all the rows at once. That isn't the solution to your problem, and isn't what was suggested. – ADyson Aug 23 '23 at 16:18
  • @ADyson Thanks for the advice. I'm taking an online class. Are there any classes for PHP that you would recommend for doing things the right way? – Mike Orr Aug 23 '23 at 16:18
  • https://phpdelusions.net is an excellent resource. As is https://phptherightway.com/ . I don't know what course you're doing but clearly they are getting money for old rope. – ADyson Aug 23 '23 at 16:19
  • good to know. The question now has my latest code. The only change I made was the variable for $id to $cliffhangers_id which took care of the syntax error, but is updating all rows. I still have WHERE – Mike Orr Aug 23 '23 at 16:24
  • You're updating based on the originally listed data, not the data the user actually selected. That makes no sense. make it get the ID from $_POST like you get the email and password. It should be submitted from the form. – ADyson Aug 23 '23 at 16:33
  • 1
    fixed it. The value in the form was wrong. Thank you for your help! I will check out some of those other resources. – Mike Orr Aug 23 '23 at 16:43

0 Answers0