Multiple parameters in SQL query in PHP

Question

I'm trying to use the following code to edit a row in a database using a userid parameter.

But with the code I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET firstname = IF(? <> '', ?, firstname), SET surname = IF(? <' at line 3

I'm really not sure what's the problem as everything looks in order in the SQL query. I've tried taking out the IF(? <> '', ?, ) but it still gives the error. I've double checked that all the column names in the table are correct. What am I doing wrong?

<?php


        include 'database_conn.php';

        if(!$conn)
        {
            echo "Error connecting to database";
        }

        if (mysqli_connect_errno()) 
        {
            echo "<p>Connection failed:".mysqli_connect_error()."</p>\n";
        }


        $id = isset($_GET['userId']) ? $_GET['userId'] : NULL; 

        $newUsername = isset($_REQUEST['updateUsername']) ? $_REQUEST['updateUsername'] : NULL;
        $newEmail = isset($_REQUEST['updateEmail']) ? $_REQUEST['updateEmail'] : NULL;
        $newPassword = isset($_REQUEST['updatePassword']) ? $_REQUEST['updatePassword'] : NULL;
        $newForename = isset($_REQUEST['updateForename']) ? $_REQUEST['updateForename'] : NULL;
        $newSurname = isset($_REQUEST['updateSurname']) ? $_REQUEST['updateSurname'] : NULL;
        $newDob = isset($_REQUEST['updateDob']) ? $_REQUEST['updateDob'] : NULL;


        $sql = "UPDATE Users
                SET username = IF(? <> '', ?, username),
                SET firstname = IF(? <> '', ?, firstname),
                SET surname = IF(? <> '', ?, surname),
                SET email = IF(? <> '', ?, email),
                SET password = IF(? <> '', ?, password),
                SET dateofbirth = IF(? <> '', ?, dateofbirth)
                WHERE id = ?";


        $stmt = mysqli_prepare($conn, $sql)
            or die(mysqli_error($conn));  

        mysqli_stmt_bind_param($stmt, "ssssssi", $newUsername, $newForename, $newSurname, $newEmail,
                            $newPassword, $newDob, $id)
            or die(mysqli_error($conn));  

        mysqli_stmt_execute($stmt)
            or die(mysqli_error($conn));  




        if($stmt)
        {
            echo "Details updated successfully";   
        }
        else
        {
            die(mysqli_error($conn));  
        }

        mysqli_close($conn);

        ?>

What exactly are you trying to do with that SQL syntax? IF(? <> '', ?, username) is all over the place. Are you just trying to set the username equal to $_REQUEST['updateUsername'])? I strongly recommend checking this existing answer. — Obsidian Age
– Obsidian Age, Commented Mar 15, 2017 at 1:21
That isn't how UPDATE works. RTM dev.mysql.com/doc/refman/5.7/en/update.html — Funk Forty Niner
– Funk Forty Niner, Commented Mar 15, 2017 at 1:35
Plus, I hope you're not going live with this; in storing plain text passwords. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 15, 2017 at 1:37
I found it on another stack overflow thread, wasn't massively sure what it was doing. All I want to do is not overwrite the value in the row with a blank string if no value is entered in the form. No Fred, it's not going live. — user6009023
– user6009023, Commented Mar 15, 2017 at 1:45
I'd like to know the link for it, because the syntax is wrong. Best you ping me when replying @Ajek doing as I did here. I'm not always present. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 15, 2017 at 1:47

Community · Accepted Answer · 2017-05-23 12:17:20Z

MySQL's "UPDATE" uses only one SET separated by commas:

https://dev.mysql.com/doc/refman/5.7/en/update.html

Your query should read as:

$query = "
  UPDATE `users`
  SET 
    `username` = IF(? <> '', ?, `username`),
    `firstname` = IF(? <> '', ?, `firstname`),
    `surname` = IF(? <> '', ?, `surname`),
    `email` = IF(? <> '', ?, `email`), 
    `password` = IF(? <> '', ?, `password`), 
    `dateofbirth` = IF(? <> '', ?, `dateofbirth`)
  WHERE `id` = ?
";

and seeing you based yourself on the following Q&A, have changed the syntax for it:

Stop sql from updating blank or empty fields from my update form

as per a link you left for me in the comments area.

You said in comments that this wasn't a live site. However, if and when you do decide to go live with this, use password_hash() and password_verify().

References:

Since storing plain text passwords is dangerous.

Note: The password column will need to be 60+ in length. The manual suggests 255 as being a good bet.

Collectives™ on Stack Overflow

Multiple parameters in SQL query in PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related