PHP MySQL - UPDATE

Let’s discuss how to update records in MYSQL through PHP  in the XAMPP Server.

Consider the table - Medicine present in the database - hospital snapshot with existing records.

 

UPDATE in MySQL is used with WHERE Clause which is used to update single or multiple records in a table based on the condition specified inside the WHERE clause.

If WHERE clause is not specified, then entire table will be updated.So it is important to specify WHERE Clause.

 

SQL UPDATE - Syntax

 

UPDATE TABLE_NAME SET column_name = value WHERE conditions….

As we know that columns refers to the column names that exist in the given table (TABLE_NAME).

 

PHP MySQL - Object oriented Connection

 

We will see how to connect PHP with a MySQL database in XAMPP Server using PHP Script using Object oriented Format.

 

Approach

1.Specify the credentials to create a connection object.
The credentials include XAMPP Server localhost name, username, password and database name.
$connecting_data =  new mysqli($server, $user, $pwd, $data);

2.Specify the SQL Query that includes the UPDATE
$my_query = UPDATE TABLE_NAME SET column_name = value WHERE conditions…;

3.Check for Updation
if ($connecting_data->query($my_query) === TRUE) {
  echo "Record updation done!!.";
} else {
  echo "Error: " . $connecting_data->error;
}

4. Close the Connection
$connecting_data->close();

Example 1:

In this example, we will update the values in Cost column to 200 if the value in the Quantity column is greater than 10.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

$my_query = "UPDATE Medicine SET Cost=200 WHERE Quantity > 10";


if ($connecting_data->query($my_query) === TRUE) {
  echo "Record updation done!!.";
} else {
  echo "Error: " . $connecting_data->error;
}

$connecting_data->close();
?>

Output:

Let's see whether the records are updated or not.

 

Example 2:

In this example, we will update the Disease to 'Other' when Cost is 200.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

$my_query = "UPDATE Medicine SET Disease='Other' WHERE Cost =200";


if ($connecting_data->query($my_query) === TRUE) {
  echo "Record updation done!!.";
} else {
  echo "Error: " . $connecting_data->error;
}

$connecting_data->close();
?>

Output:

 

So the records are updated.

 

Conclusion

In this article we seen how to update records using UPDATE through PHP-MySQL in XAMPP Server.