How to properly update a MySQL table in PHP

Question

I'm trying to update a row in a table using PHP, although I'm this is not my first time, but the update function is not working.

This is the php code:

<?php
header("Access-Control-Allow-Origin: *");

$server = "localhost";
$username = "username";
$password = "dbpass";
$database = "dbname";

$con = mysql_connect($server, $username, $password) or die ("Could not connect: " . mysql_error());

mysql_select_db($database, $con);

 $q=mysqli_query("UPDATE  `phonegap_login` SET  `firstname` =  'Alhia' WHERE  `reg_id` =50;");


if($q)
{
echo "success";
}
else
{
echo "failed";
}
?>

And by the way, if you try INSERT into, it will work:

$q=mysql_query("insert into `phonegap_login` (`reg_date`, `firstname`, `lastname`, `email`, `password`) values ('test', 'test', 'test', 'test', 'test')");

How do I fix it? the insert is working, updating a row is not. I'm using a row with reg_id=50 for testing.

You're mixing mysqli_query and mysql_query. Only use mysqli_query. mysql_query is deprecated — Maarten van Middelaar, Mar 09 '17 at 22:44
@MaartenvanMiddelaar Thank you! It worked, you can answer the question so I can mark your answer :D — Fala, Mar 09 '17 at 22:46
This is subjective, so not putting it as an answer...but I recommend using PDO instead. Examples of PDO online will also, typically, follow "best-practices" a bit better (such as named parameters), e.g. http://stackoverflow.com/questions/18655706/pdo-with-insert-into-through-prepared-statements — Kevin Nelson, Mar 09 '17 at 22:52
@MaartenvanMiddelaar Thank you, I didn't know about mixing the query. — Fala, Mar 09 '17 at 22:56
Take into account the comment of @KevinNelson it is better practice to use PDO — Maarten van Middelaar, Mar 09 '17 at 22:59
@KevinNelson yes thank you! I'll browse some examples and give it a try! — Fala, Mar 09 '17 at 23:03

Maarten van Middelaar · Accepted Answer · 2017-03-13T22:05:51.427

You're mixing mysqli_query and mysql_query. Only use mysqli_query. mysql_query is deprecated

$con = mysqli_connect($server, $username, $password) or die ("Could not connect: " . mysqli_error());

mysqli_select_db($database, $con);

$q=mysqli_query("UPDATE  `phonegap_login` SET  `firstname` =  'Alhia' WHERE  `reg_id` =50;");

Or, Object Oriented:

$mysqli = new mysqli($server, $username, $password, $database);

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$q = $mysqli->query("UPDATE  `phonegap_login` SET  `firstname` =  'Alhia' WHERE  `reg_id` =50;");

Or, PDO:

$connectionString = 'mysql:host=' . $server . ';dbname='.$database;
try {
    $db = new PDO($connectionString, $user, $pass);
} catch ( PDOException $e) {
    echo "Failed to connect to Database: (" . $e->getCode() . ") " . $e->getMessage();
}
$q = $db->query("UPDATE  `phonegap_login` SET  `firstname` =  'Alhia' WHERE  `reg_id` =50;");

One tip to avoid getting confused between `mysql_query` and `mysqli_query` is to always use the object-oriented interface: `$db->query(...)` will never accidentally use the obsolete interface for lack of an `i` character. It's also a lot less verbose and easier to read because of that. — tadman, Mar 09 '17 at 23:13

How to properly update a MySQL table in PHP

1 Answers1