PHP MYSQL update stament not working

Question

This code is meant to check the submitted form values and update the table, however it just replaces the field with a blank

Any ideas where it is gone wrong, please?

<form action = "update.php" method = "POST">
    <p>
        New Name: <input type "text" name="name">
        <input type= "submit">
    </p>
</form>

<?php 

require ('/var/www/html/site1/connect_db.php');
if(!empty($_POST['name']) && !is_numeric($_POST['name']))
{
    $name=$_POST['name'];
    $name=mysqli_real_escape_string($dbc,$query);
    $name=strip_tags($name);


    #$query='update customers SET customerName = '".$name."' where customerNumber=114';
    $query = "update customers ". "SET customerName = $name"."where customerNumber=114" ;
    mysqli_query($dbc,$query);
}
else
{
    echo $name;
}

$query = 'select * from customers where customerNumber=103';
$result = mysqli_query($dbc,$query);

while ($row=mysqli_fetch_array($result, MYSQLI_NUM))
{
    echo"<p>Name : $row[1]</p>";
}
mysqli_close($dbc);

?>

If you echo the first `$query`, you'll see that there is no space before the `WHERE` clause. That, and your `$name` need be wrapped in singlequotes, because it's a string. You should also look into using prepared statements, you're wide open to SQL injection.¨ — Qirel, Aug 23 '16 at 20:44
Why are you splitting up the string and concatenating it in the first place? — Barmar, Aug 23 '16 at 20:45
Do you try to use `$query` before assigning it to a string? `$name=mysqli_real_escape_string($dbc,$query);` very wrong — KANAYO AUGUSTIN UG, Aug 23 '16 at 20:47
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use manual escaping and string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/) if you ever forget to properly escape something. `strip_tags` should not be used arbitrarily on user input. — tadman, Aug 23 '16 at 20:53
You also have problem with `$name=mysqli_real_escape_string($dbc,$query);` should be `$name` instead of `$query`. That, combined with my first comment, should fix it. But you really should look into using prepared statements. @Matrix1977 — Qirel, Aug 23 '16 at 21:50
@Qirel well spotted! that fixed it! sorry I was going blind I didn't see it. ..Thanks with regards to security issues, I will do some search on it, if you have any info where I can read about it that would be great. thanks. — Matrix1977, Aug 24 '16 at 12:43
@Matrix1977 http://stackoverflow.com/a/60496/4535200, http://php.net/manual/en/mysqli.quickstart.prepared-statements.php and http://php.net/manual/en/mysqli.prepare.php are good places to start. — Qirel, Aug 24 '16 at 13:05

jedifans · Answer 1 · 2016-08-23T20:55:51.993

0

You are updating customer number 114 but selecting 103 out, whose name may be blank.

Your update statement needs to have quotes around the $name bit as below:

$query = "UPDATE customers SET customerName = '$name' WHERE customerNumber=114";

Edit: please see the parameterised query advice in the question comments.

edited Aug 23 '16 at 20:55

answered Aug 23 '16 at 20:48

jedifans

2,287
1
13
9

Don't be too quick to congratulate, it's being mangled with `strip_tags`. – tadman Aug 23 '16 at 20:54
I have selected now 114 although that wont have no effect on what is being updated and tried all these scenarios but no results :( – Matrix1977 Aug 23 '16 at 20:58
`code` #$query='update customers SET customerName = '".$name."' where customerNumber=114'; #$query = "update customers ". "SET customerName = $name"."where customerNumber=114" ; $query = "UPDATE customers SET customerName = '$name' WHERE customerNumber=114" ; `code` – Matrix1977 Aug 23 '16 at 21:00

PHP MYSQL update stament not working

1 Answers1