Failed syntax UPDATE id in MariaDB

Question

I am getting the following error when I try to update tables in a db using PHP.

QUERY FAILED 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

(line one is where my query begins)

I've concluded that it has something to do with the "id" but I have no idea what. If I hard code it as "WHERE id = 1" it works, but it really doesn't seem to read the id.

if(isset($_POST['submit'])){

    $username = $_POST['username'];
    $password = $_POST['password'];
    $id = $_POST['id'];

    $query = "UPDATE users SET ";
    $query .= "username = '$username', ";
    $query .= "password = '$password' ";
    $query .= "WHERE id = $id ";

    $result = mysqli_query($connection, $query);
    if(!$result) {

        die("QUERY FAILED" . mysqli_error($connection));    
    }  
}

Here is the HTHML I'm using for the ID:

<form action="login_update.php" method="post">

<div class="form-group">
            <select name="id" id="">
        <?php
    global $connection; 
            $query = "SELECT * FROM users";
            $result = mysqli_query($connection, $query);
            if(!$result){ //om result inte är TRUE then die()
                die('Query failed ' .mysqli_error());
            }

            while($row = mysqli_fetch_assoc($result)){
            $id = $row['id'];
            echo "<option value=''>$id</option>";
        ?>
    </select>

</div>

  <input class="btn btn-primary" type="submit" name="submit" value="Update">

        </form>

I'm running this on localhost so no need for any hashing and the connection to the db works fine.

Your code is open to [SQL injection](https://stackoverflow.com/q/332365/2469308) related attacks. Please learn to use [Prepared Statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Madhur Bhaiya, Oct 12 '18 at 12:42
Parameterize your query. Hash your passwords. Use the connection link in `mysqli_error`. Also note the error from `mysql` relates to the SQL, not PHP, so `at line 1` is the first line of your SQL, not PHP. — user3783243, Oct 12 '18 at 12:43
`mysqli_error()` requires a db connection here. You also don't seem to have any value for the option. — Funk Forty Niner, Oct 12 '18 at 12:45
`option value=''` You're not actually passing the id in the field, you're just displaying it. — Patrick Q, Oct 12 '18 at 12:46
how do i pass the id? im running this on a local host - sorry for not mensioning that, so no attacks sofar :P — Saucepan, Oct 12 '18 at 12:47
You have no `username` or `password` field in your form. Where do you expect those values to come from? — Patrick Q, Oct 12 '18 at 13:11
`$_POST['username']` There is no `username` field in your form, so `$_POST['username']` doesn't exist. If you had error reporting turned on, you'd be alerted to this. Please take the time to review a basic tutorial on handling forms with PHP. — Patrick Q, Oct 12 '18 at 13:14
@PatrickQ ive got input fields for that where is enter that data. i get it to work fine [as stated above] if i switch the id to a hardcoded statement — Saucepan, Oct 12 '18 at 13:18
So you're saying that you did _not_ put your _complete_ form above as asked? Because there are no `username` or `password` fields shown above. If you don't give us a complete picture of what you're doing, we can't help you. — Patrick Q, Oct 12 '18 at 13:19

score 0 · Answer 1 · answered Oct 12 '18 at 12:58

0

Add query like this:

$query .= "WHERE id = '$id' ";

answered Oct 12 '18 at 12:58

lucky

308
2
4
17

sorry didnt do anything. it completed the query, but no records were updated – Saucepan Oct 12 '18 at 13:02
add $id in '$id' – lucky Oct 12 '18 at 13:04
as in "WHERE $id = '$id' ? – Saucepan Oct 12 '18 at 13:05
thats what I got above that doesnt work ... as i said; it completed the query (as in it doesnt throw errors), but no records were updated – Saucepan Oct 12 '18 at 13:07
@Saucepan it does do something , ie does not throw errors anymore. If you have another question, put that in another question. – YvesLeBorg Oct 12 '18 at 13:29

score -1 · Accepted Answer · answered Oct 12 '18 at 12:49

-1

Add value to select box options

echo "<option value='$id'>$id</option>";

answered Oct 12 '18 at 12:49

Sachin

789
5
18

sorry, but that did absolute nothing :( – Saucepan Oct 12 '18 at 12:53
have you got Id value in post – Sachin Oct 12 '18 at 12:56
@Saucepan Please put your entire form in your question. – Patrick Q Oct 12 '18 at 12:57
@Saucepan I mean put everything from `
` to `
` in your question. It would be helpful to see _all_ of the fields. – Patrick Q Oct 12 '18 at 13:05
In your form username and password input field is missing – Sachin Oct 12 '18 at 13:10
@Saucepan login_update.php echo query before result statement so you will see what is the query build and which fields are not coming in the query so we can figure out your issue. – Sachin Oct 12 '18 at 13:19
reinserted this and it solved the issue. thank you @sachin for all the help and also having compassion with us new to coding (not a trait shared by many here at so sadly) – Saucepan Oct 16 '18 at 10:18

Failed syntax UPDATE id in MariaDB

2 Answers2