PHP MySQL Update Query

What is PHP MySQL Update Query?

The syntax for updating data in a MySQL database using the UPDATE query is as follows:

Syntax of PHP MySQL Update Query

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Let us breakdown the syntax: 

• UPDATE: This keyword informs MySQL that you want to modify existing records in the table
• table_name: Replace this with the name of the table in which you want to update the data
• SET: This keyword specifies the columns and their corresponding new values that you want to update
• column1, column2, etc.: Replace these with the names of the columns you want to update
• value1, value2, etc.: Replace these with the new values you want to set for the respective columns
• WHERE: This clause is optional but highly recommended as it allows you to specify a condition to identify the records you want to update. 
  Note: If you omit the WHERE clause, all rows in the table will be updated with the new values, which can be dangerous if not intended
• condition: Replace this with the specific condition that will be used to identify the records you want to update. 
  For example, you can use WHERE id = 1 to update the record where the id column equals 1

Where Update Query

In PHP, you can use an "UPDATE" SQL query to modify existing records in a database table. The process typically involves connecting to the database, preparing the SQL statement, executing it, and then closing the connection.

Here’s a step-by-step explanation and implementation of an UPDATE query in PHP:

1. Connect to the Database: Establish a connection using the mysqli or PDO extension.
2. Prepare the SQL Statement: Create the SQL UPDATE query to modify the specific record.
3. Bind Parameters: If you're using prepared statements, bind the necessary parameters to avoid SQL injection.
4. Execute the Query: Run the query and check if the operation was successful.
5. Close the Connection: Finally, close the database connection.
    

Here’s an example using mysqli to update a user's name and email in a database:

Database Table Structure

Assume we have a table named users with the following structure:

• id (INT, PRIMARY KEY)
• name (VARCHAR)
• email (VARCHAR)

Implementation

<?php
// Database connection parameters
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL Update statement
$id = 1; // Assuming we want to update the user with ID 1
$new_name = "John Doe";
$new_email = "john.doe@example.com";

// Prepare and bind
$stmt = $conn->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");
$stmt->bind_param("ssi", $new_name, $new_email, $id);

// Execute the query
if ($stmt->execute()) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $stmt->error;
}

// Close the statement and connection
$stmt->close();
$conn->close();
?>

Explanation of the Code

1. Database Connection: The code establishes a connection to the MySQL database using mysqli.
2. Prepare Statement: The prepare() method is used to create a prepared statement for the UPDATE operation.
3. Bind Parameters: The bind_param() method binds the parameters to the SQL query:
   • "ssi" specifies the types of the variables (s for string and i for integer).
4. Execute: The execute() method runs the prepared statement. If successful, it outputs a success message; otherwise, it displays an error.
5. Cleanup: Finally, the statement and connection are closed to free up resources.

Methods for Database Connectivity

In PHP, there are three main methods for database connectivity and interaction: Object-Oriented (OO) method using PDO and mysqli, Procedural method using mysqli, and Procedural method using the older mysql extension (deprecated as of PHP 5.5.0).

In this section, we will be talking more about the first two methods. 

Object-Oriented (OO) method using PDO and mysqli

The Object-Oriented (OO) method involves using classes and objects to interact with the database. Both PDO (PHP Data Objects) and mysqli (MySQL Improved) extensions provide Object-Oriented APIs, allowing developers to work with databases using objects and methods.

Procedural method using mysqli

The Procedural method involves using procedural programming techniques to interact with the database. 

Note: The mysqli extension offers both Object-Oriented and Procedural interfaces, giving developers the flexibility to choose between these methods based on their coding preferences.

We have discussed the Update Query using both methods.

Let us now take an example to see the working of the Update Query. 

Time for an Example 

Let us take a sample table: Employee

Let us see some use cases where we can utilize the Update Query. 

Problem Statement: In the above table, we require to update the age of employee having Empld = 3 with 24. 

1. Update with Procedural using MySqli

Some steps are required to be followed: 

Step 1: Connecting to the Database ( First, you need to establish a connection to the MySQL database)

<?php
$servername = "your_database_server";
$username = "your_database_username";
$password = "your_database_password";
$database = "your_database_name";

// Create a connection
$connection = mysqli_connect($servername, $username, $password, $database);

// Check the connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Step 2: Preparing the Update

Here, we will update the value of the record with the desired one. 

<?php
// Variables for the update
$updateEmpId = 1; // The employee ID you want to update
$newAge = 24; // The new age

// Construct the update query
$query = "UPDATE employees_table SET age='$newAge' WHERE empId=$updateEmpId";
?>

Step 3: Executing the Update

It's time to execute the update command and make the changes in the database.

<?php
// Execute the update query
if (mysqli_query($connection, $query)) {
    echo "Employee information updated successfully!";
} else {
    echo "Oops! Something went wrong: " . mysqli_error($connection);
}
?>

Step 4: Closing the Connection

Once you're done updating the data, it's essential to close the connection to the database.

<?php
// Close the database connection
mysqli_close($connection);
?>

Let us now run the code and see the output: 

Output

2. Using Object Oriented Method with MySQLi

As we have discussed, in the object oriented technique, we play with objects and classes. In the previous example, we have utilized the connect method to connect with the database. 

In this example, we will be creating the object of mysqli and passing the parameters. 

The steps are going to remain the same. 

<?php
$servername = "your_database_server";
$username = "your_database_username";
$password = "your_database_password";
$database = "your_database_name";

// Create a connection
$connection = new mysqli($servername, $username, $password, $database);

// Check the connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

// Variables for the update
$updateEmpId = 1; // The employee ID you want to update
$newAge = 24; // The new age

// Construct the update query
$query = "UPDATE employees_table SET age='$newAge' WHERE empId=$updateEmpId";

// Execute the update query
if ($connection->query($query) === true) {
    echo "Employee information updated successfully!";
} else {
    echo "Oops! Something went wrong: " . $connection->error;
}

// Close the database connection
$connection->close();
?>

You can also try this code with Online PHP Compiler

Run Code

When you are going to run this query, the value will get updated. 

What is the advantage of using Object Oriented method over procedural approach? 

The main advantage of using the Object Oriented method with mysqli is that it provides better error handling and security compared to the procedural approach, making your code more robust and maintainable.

3. Update Query using PDO

Using PDO (PHP Data Objects) to execute an UPDATE query in PHP offers a flexible and secure way to interact with databases. PDO supports prepared statements, which help protect against SQL injection and improve performance by allowing the database to cache the query plan.

Here’s an example of how to use PDO to update a user's name and email in a database:

Database Table Structure

Assume we have a table named users with the following columns:

• id (INT, PRIMARY KEY)
• name (VARCHAR)
• email (VARCHAR)

Implementation

<?php
// Database connection parameters
$dsn = 'mysql:host=localhost;dbname=your_database;charset=utf8';
$username = 'your_username';
$password = 'your_password';

try {
    // Create a PDO instance
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception

    // SQL Update statement
    $id = 1; // Assuming we want to update the user with ID 1
    $new_name = "John Doe";
    $new_email = "john.doe@example.com";

    // Prepare the SQL statement
    $stmt = $pdo->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id");

    // Bind parameters
    $stmt->bindParam(':name', $new_name);
    $stmt->bindParam(':email', $new_email);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);

    // Execute the query
    if ($stmt->execute()) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close the connection
$pdo = null;
?>

Explanation of the Code

• Database Connection:
  • The code sets up a DSN (Data Source Name) to connect to a MySQL database using PDO.
  • The PDO object is instantiated with the DSN, username, and password.
  • The error mode is set to PDO::ERRMODE_EXCEPTION to handle errors using exceptions.
• Prepare Statement:
  • The SQL UPDATE query is prepared with named placeholders (:name, :email, :id).
• Bind Parameters:
  • The bindParam() method binds the actual values to the placeholders.
  • PDO::PARAM_INT is used for the integer ID to ensure proper type handling.
• Execute:
  • The execute() method runs the prepared statement. If successful, it echoes a success message; otherwise, it indicates an error.
• Error Handling:
  • A try-catch block is used to handle potential exceptions and display error messages if something goes wrong.
• Cleanup:
  • Finally, the connection is closed by setting the $pdo object to null, which is a good practice to release resources.

Let us now see some faqs based on the same: 

Frequently Asked Questions

Can I update multiple columns using a single UPDATE query?

Yes, you can update multiple columns using a single UPDATE query. Simply include the column-value pairs separated by commas in the SET clause.

What happens if I don't include a WHERE clause in the UPDATE query?

If you don't include a WHERE clause in the UPDATE query, all rows in the specified table will be updated with the new values provided in the SET clause. This could result in unintended changes to your data, so be cautious when omitting the WHERE clause.

Can I update data in multiple tables simultaneously using a single UPDATE query?

No, the UPDATE query in MySQL can only update data in a single table. If you need to update data in multiple tables, you would need to execute multiple UPDATE queries.

Why do we use the UPDATE query?

The UPDATE query is used to modify existing records in a database table. It allows developers to change specific field values based on certain conditions, enabling data management and ensuring that the information remains current and relevant to application needs.

What is the difference between a query and an UPDATE query?

A query is a general term for any SQL statement that retrieves, modifies, or manages data in a database, while an UPDATE query specifically alters existing records. Essentially, all UPDATE queries are queries, but not all queries are UPDATE queries, as they may also include SELECT, INSERT, or DELETE operations.

Conclusion

In this article, we have discussed the PHP MySQL Update Query. It is an essential tool for managing and modifying data within your database. By understanding how to effectively use UPDATE queries, you can ensure that your application maintains accurate and up-to-date information. The ability to dynamically alter records based on user input or other conditions empowers developers to create more interactive and responsive applications.

We hope that the article has helped you in understanding how you can manipulate your data. Alright! So now that you have learnt about PHP MySQL Update Query, you can also refer to other similar articles. 

• Introduction to PHP
• PHP EXPLODE 
• php str_replace
• PHP Developer Tools
• Difference Between echo and print in PHP

You may refer to our Guided Path on Code360 for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!