updating mysqli data using php

Question

I have a form which retrieves various values from my database and displays it in a form. The form contains text boxes, radio, drop down menus. The retrieving part works perfectly and the correct values are displayed for each field. But then when I want to change the field and update the data, it's not updating. Can some please help me with this. Here is my code:

if(isset($_POST['submit'])){
    $sql = "UPDATE tbl_dealer_info ";
    $sql .= "SET phone = '$phone', email = '$email', sfid = '$sfid', ... WHERE id = '$idhidden' ";
    $result = mysqli_query($conn, $sql);
    if(!$result){
        die('Could not update data: '. mysqli_error());
    }
    else{
        echo "Updated Successfully";
    }
}
<input type = "hidden" name = "idhidden" id = "idhidden" value = "" /> // My hidden input to store the id

It displays "Updated Successfully" but isn't actually updating.

Even if you run the update, doesn't mean any rows were affected. What is `mysqli_affected_rows($conn)`? — Qirel, Sep 15 '17 at 16:08
I forgot to include the where clause but I do have it. i have updated my query above. Still nothing though — Sukrit Jaie, Sep 15 '17 at 16:08
Output the query and execute on your DB, does it work there? Perhaps there are just no rows to update based on the `where`? — chris85, Sep 15 '17 at 16:24
So i created a hidden input tag and am storing the row id in that. Then using that id in the WHERE clause — Sukrit Jaie, Sep 15 '17 at 16:26
And what exactly do you mean by output the query? Like echo it? I'm kinda new to this so not really sure — Sukrit Jaie, Sep 15 '17 at 16:27
[Little Bobby](http://bobby-tables.com/) says **[you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). I recommend `PDO`, which I [wrote a function for](http://paragoncds.com/grumpy/pdoquery/#function) to make it extremely easy, clean, and more secure than using non-parameterized queries. Also, [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) may help you choose between `MySQLi` and `PDO` — GrumpyCrouton, Sep 15 '17 at 16:29
```$sql .= "SET phone = '$phone', email = '$email', sfid = '$sfid', WHERE id = '$idhidden' "; $result = mysqli_query($conn, $sql);``` try that as you where clause — Adam Hull, Sep 15 '17 at 17:08
This is the error I'm getting. "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 'WHERE id = ''' at line 1" — Sukrit Jaie, Sep 15 '17 at 18:41
I have a ... before where clause because I have about 20 columns and didn't feel like typing them all here, but they are similar to the ones shown above — Sukrit Jaie, Sep 15 '17 at 18:50
Is `$idhidden` retrieved from `$_POST['idhidden']`? Because if not, your `where` would be trying to find a blank id — Piyin, Sep 15 '17 at 19:19
Yeah I created the variable like this $idhidden = $_POST['idhidden'] ?? ' ' ; — Sukrit Jaie, Sep 15 '17 at 19:25

score -1 · Answer 1 · edited Dec 07 '17 at 17:20

-1

you are missing where condition and ';' in the sql statement

$sql = "UPDATE tbl_dealer_info ";
$sql .= "SET phone = '$phone', email = '$email', sfid = '$sfid' WHERE #here where condition #here ";

edited Dec 07 '17 at 17:20

answered Sep 15 '17 at 16:06

jasinth premkumar

1,430
1
12
22

Updated my query above to include the WHERE clause but it still doesn't update – Sukrit Jaie Sep 15 '17 at 16:11
UPDATE Syntax UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; – jasinth premkumar Sep 15 '17 at 16:12
So I need to have 2 semicolons in that line? – Sukrit Jaie Sep 15 '17 at 16:13
need another eg? with php – jasinth premkumar Sep 15 '17 at 16:15
"..." are the values for all the columns I have. But I tried with 2 semicolons and still nothing – Sukrit Jaie Sep 15 '17 at 16:16
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2"; if ($conn->query($sql) === TRUE) { echo "Record updated successfully"; } else { echo "Error updating record: " . $conn->error; } check if result also returns true – jasinth premkumar Sep 15 '17 at 16:18
`;` is not required. A `where` also is not required. – chris85 Sep 15 '17 at 16:24
sorry for that mistake leave as it is – jasinth premkumar Sep 15 '17 at 16:29
And like I said in a comment above, mysqli_affected_rows($conn) is giving me 0. So what exactly would that mean? – Sukrit Jaie Sep 15 '17 at 16:30
try it mysql statement in examples of w3 schools https://www.w3schools.com/sql/trysql.asp?filename=trysql_update_2 – jasinth premkumar Sep 15 '17 at 16:32
some times it happens in my system too . i will change to objecj oriented try nested if else statement check connection – jasinth premkumar Sep 15 '17 at 16:33
use prepare and execute statements – jasinth premkumar Sep 15 '17 at 16:41

santho · Answer 2 · 2017-09-18T03:44:42.000

-1

Try this

if(isset($_POST['submit'])){
    $sql = "UPDATE tbl_dealer_info SET phone = '".$phone."', email = '".$email."', sfid = '".$sfid."', ... WHERE id = ".$idhidden; 
     $result = mysqli_query($conn, $sql);
     if(!$result){ 
        die('Could not update data: '.  mysqli_error()); 
     } else{ 
        echo "Updated Successfully";
    } 
 }

edited Sep 18 '17 at 03:44

answered Sep 17 '17 at 09:17

santho

366
2
16

The update only works if in my where clause, I have the account name instead of the id. Whenever I put id, it doesn't work. Can someone please please help with what the issue is? – Sukrit Jaie Sep 18 '17 at 18:08

updating mysqli data using php

2 Answers2