Struggling with UPDATE in MySQL

Question

Beginner here.

The issue I am facing is getting rather tedious now despite me doing this a hobby, I have been stuck at this point for the last few days and have tried searching everywhere for a solution. I have even completely reworking the code I have, following other examples/tutorials.

This is the closest I get to.

Everything is filled out, form submitted. It states successful but when I return back to the page, the field hasn't been updated. I have even checked the database directly to ensure the output display isn't wrong. But it doesn't appear there either.

(I do have other entries in the table, but I only want this page to edit one of them).

Any help pointing me to my error and why would be greatly appreciated. Best to learn where I have gone wrong so I can look out for it in the future.

<?php
$host="****"; // Host name 
$username="****"; // Mysql username 
$password="****"; // Mysql password 
$db_name="****"; // Database name 
$tbl_name="****"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id from the address bar
$id=$_GET['id'];

// get value of modname from the form
$modname=$_POST['modname'];

// update data in mysql database 
$sql = "UPDATE $tbl_name SET modname='$modname' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='edit.php'>Return to overview</a>";
}

else {
echo "ERROR";
}

?>

The page with the form.

<?php
$host="****"; // Host name 
$username="****"; // Mysql username 
$password="****"; // Mysql password 
$db_name="****"; // Database name 
$tbl_name="****"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar


// Retrieve data from database 
$sql="SELECT modname FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>

<form name="form1" method="post" action="update_ac.php">
<input name="modname" type="text" id="modname"></td>
<br>
<input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>">
<br>
<input type="submit" name="Submit" value="Submit">
</form>

Answer 1

Even though your id field is a hidden field in your form it is still sent as a POST attribute, so you need $_POST['id'] to retrieve the value (not $_GET['id']).

Also, you should put table and column names in ` commas, like this;

$sql = "UPDATE `$tbl_name` SET `modname`='$modname' WHERE `id`='$id'";

However, your code is also wide open to SQL injection. Read this question and rewrite your code.

Answer 2

In this case, your query is solid and syntax is correct, but your variables don't contain the values you are expecting. Since you are learning, I'd like to offer some advice and example code.

1. While your code is still somewhat simple, change to the mysqli API. The syntax is similar but it is not depracated like mysql and you can use prepared statements.
2. When in the development stage, always turn on error reporting for your functions/conditions.
3. ALWAYS be sure to verify that your variables contain the values you are expecting before you try to use them with isset(), empty(), etc.
4. Always validate/sanitize/escape user input before inserting into your database.

Sample:

So it seems you are getting the id from a query string initially, such as http://domain.com/get_id.php?id=1? If so, your code should work, just make sure your URL is formed properly. If you are getting the id here from your <form>, then it should be $_POST instead.

// get value of id from the address bar
if(!empty($_GET['id'])) {
    $id=$_GET['id'];
} else {
   echo "ID is empty";
}

// get value of modname from the form
if(!empty($_POST['modname'])) {
    $modname=$_POST['modname'];
} else {
   echo "ID is empty";
}

// update data in mysql database 
$sql = "UPDATE $tbl_name SET modname='$modname' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='edit.php'>Return to overview</a>";
}

else {
echo mysql_error();
}

On your form, you need to select the id before you will be able to echo it.

// Retrieve data from database 
$sql="SELECT id,modname FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>

<form name="form1" method="post" action="update_ac.php">
<input name="modname" type="text" id="modname"></td>
<br>
<input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>">
<br>
<input type="submit" name="Submit" value="Submit">
</form>

Answer 3

This should fix your issue.

$sql = "UPDATE {$tbl_name} SET modname='{$modname}' WHERE id='{$id}'";

Hope it helps!