Data in my DataBase doesn't get updated MySql

Question

The database doesn't get updated for some strange reason, I can get the correct data (I checked it), but the database does not get updated when I click submit. Can you anyone check if there is anything missing? NB: the "----" are just for security reasons!

<?php
$host = "sql308.-----.com";
$dbUsername = "----_3041----";
$dbPassword = "-----------";
$dbName = "----_3041----_phpmysqli";

$conn = mysqli_connect($host, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($conn-> connect_error) {
    die("Connection échouée!".$conn-> connect_error);
}

$rn = $_GET['rn'];
$dt = $_GET['dt'];
$to = $_GET['to'];
$rk = $_GET['rk'];
    
if(isset($_POST['edit'])) {
    $rn = $_POST['rn'];
    $dt = $_POST['date'];
    $to = $_POST["total"];
    $rk = $_POST['remarques'];

    $sql = "UPDATE `datab1` SET 
    `date` = '$dt',
    `total` = '$to',
    `remarques` = '$rk' 
    WHERE `datab1`.`id` = '$rn'";
    
    $result = mysqli_query($conn, $sql);
    
    if($result == 1) {
        header('location:table.php');
    } 
    else {
        die(mysqli_error($conn));
    }
}

?>

Please learn how to prepare statment, you are open to sql inject. — Simone Rossaini, Dec 07 '21 at 12:57
And why you try to use GET and then use POST for create variable? — Simone Rossaini, Dec 07 '21 at 12:58
(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Dec 07 '21 at 13:01
Do you get the die or the header, or nothing at all? You may want to add an else to your `if(isset($_POST['edit'])) {`, in case it's not going in that block at all. — aynber, Dec 07 '21 at 13:01
@aynber yes, I echo the values i input (they are correct) and header send me to another page with no issues, but the data does not get updated. — fouad B, Dec 07 '21 at 13:08
@SimoneRossaini I use GET to get the values from the table into variables, and the POST to post them into text fields to be able to modify them. — fouad B, Dec 07 '21 at 13:17

score 0 · Answer 1 · answered Dec 07 '21 at 15:50

Most probably the update is executed without finding a match for the where condition.

Just before

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

add the following code at line 18

echo "<p>GET: "; var_dump($_GET); echo "<p>POST: "; var_dump($_POST); echo "<p>";

and inspect the values.

Also before

$result = mysqli_query($conn, $sql);

at line 30, please add

echo "<br>$sql<br>"; exit();

And if you could not solve after reviewing both the outputs, please post the same here.

fouad B · Answer 2 · 2021-12-09T12:16:42.233

Thank you @Natarajan N Napoleon for the helpful trick, the outputs are correct in both GET and POST as it's shown here:

GET: array(9) { ["rn"]=> string(2) "21" ["dt"]=> string(10) "07-12-2021"  ["rk"]=> string(5) "walou" ["to"]=> string(4) "1250" }

POST: array(9) { ["date"]=> string(10) "07-12-2021" ["total"]=> string(4) "1800" ["remarques"]=> string(5) "done" ["edit"]=> string(8) "Modifier" }

the SQL Query is the one making troubles, this is what I get:

UPDATE `datab1` SET `date` = '07-12-2021', `total` = '1800', `remarques` = 'done' WHERE id = ''

I was assigning a null to my "rn" which caused this error, I had to pass the value of "rn" directly to my query without no modification.

Data in my DataBase doesn't get updated MySql

2 Answers2