Arush Shah Arush Shah - 3 months ago 13
MySQL Question

PHP MySQL Update Query Failing

I'm trying to execute the following query to update a record in a table.

require "conn.php";

$user_name = $_POST["username"];
$code = $_POST["code"];
$name = $_POST["groupname"];

echo "$user_name, $code, $name";

$sql_update = "UPDATE users SET group = '$name' WHERE username = '$user_name'";

if ($conn->query($sql_update) === TRUE) {
echo "success";
}
else {
echo "fail";
}


The query fails and I'm not sure why. The connection is made and I'm able to echo the username code and name. Is there a reason why it's not working?

Answer

Your code is not secure

Look at this code with prepared statements

require_once("conn.php");
$user_name = $conn->real_escape_string($_POST["username"]);
$code = $conn->real_escape_string($_POST["code"]);
$name = $conn->real_escape_string($_POST["groupname"]);
$sql_update = $conn->prepare("update `users` set `group` = ? where `username` = ?");
$sql_update->bind_param("ss",$name,$user_name);
$sql_update->execute();
$sql_update->close();
$conn->close();

And conn.php file should be like this

$config = parse_ini_file('config.ini'); // Connection infos.
$conn = mysqli_connect('localhost',$config['username'],$config['password'],$config['db_name']);
if($conn === false) {
 die("Something was wrong ! Please try again later."); // Error if connection not ok.
} 
$conn->set_charset("utf8");

Create file outside the public_html folder named config.ini to write connection data

[db_connection]
username = username
password = password
db_name = dbname

This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

i - integer
d - double
s - string
b - BLOB

Learn more here

Comments