Arush Shah Arush Shah - 1 year ago 74
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 Source

Your code is not secure

Look at this code with prepared statements

$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` = ?");

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.

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download