Php Mysql Update - MrExamples

PHP MySQL Update Data

Are you looking to update records in a MySQL database using PHP? Look no further! In this article, we will guide you through the process of PHP Mysql update records in a MySQL database using PHP’s MySQLi and PDO extensions.

Before we get started, it’s important to understand the basics of updating data in MySQL. Updating data in MySQL involves modifying existing records in a table. To update a record, you need to know the table and the specific record that needs to be updated. In addition, you need to know which columns need to be updated and what their new values will be.

Now let’s move on to the practical aspects of updating data in MySQL using PHP.

Table Of Content-

PHP MySQL Update Data:
Update Data In a MySQL Table Using MySQLi and PDO:

Update Data In a MySQL Table Using MySQLi and PDO

When updating existing records in a table, you can use the UPDATE statement to make the necessary changes:

UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value

In the “Users” table, we can see the following data:

id	firstname	lastname	email	age	gender	reg_date
1	Matt	LeBlance	[email protected]	55	Male	2023-03-08 20:09:15
3	Jennifer	Anniston	[email protected]	54	Female	2023-03-08 20:09:15

Below are a couple of examples of updating a record with an id = 3 in the “Users” table:

Below is an example using MySQLi’s Object-Oriented method:

Example (MySQLi Object-Oriented):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = new mysqli($servername, $username, $password, $dbname); // Checking connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "UPDATE Users SET lastname='Lawrence', age=32 WHERE id=3"; if ($conn->query($sql) === TRUE) { echo "Data updated successfully"; } else { echo "Error updating data: " . $conn->error; } $conn->close(); ?>

Below is an example using MySQLi’s Procedural method:

Example (MySQLi Procedural):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Checking connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "UPDATE Users SET lastname='Lawrence', age=32 WHERE id=3"; if (mysqli_query($conn, $sql)) { echo "Data updated successfully"; } else { echo "Error updating data: " . mysqli_error($conn); } mysqli_close($conn); ?>

Below is an example using MySQL PDO(PHP Data Objects) method:

Example (PDO):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Specify the exception error mode in the PDO $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "UPDATE Users SET lastname='Lawrence', age=32 WHERE id=3"; // Prepare statement $stmt = $conn->prepare($sql); // execute the query $stmt->execute(); // Output data updated successfully echo $stmt->rowCount() . " Data UPDATED successfully"; } catch (PDOException $e) { echo $sql . " " . $e->getMessage(); } $conn = null; ?>

Example Explanation

In above example, we define the necessary connection parameters such as servername, username, password, and dbname. Then, a try-catch block is used to handle any exceptions that may occur during the execution of the code.

Inside the try block, a PDO connection is established with the MySQL database. The attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION, which means that exceptions will be thrown for errors.
Next, we define the SQL statement to update the data in the “Users” table where the “id” is 3. The lastname column will be set to “Lawrence” and the age column will be set to 32.
We then prepare the SQL statement using the prepare() method of the PDO object. This helps to prevent SQL injection attacks by separating the SQL statement from the data being used.
The execute() method of the PDO statement object is used to execute the prepared statement.
If the execution of the SQL statement is successful, the number of rows affected is echoed out to the HTML page using the rowCount() method of the PDO statement object.
If an exception is caught, an error message is displayed that includes the SQL statement and the error message generated by the exception.
Finally, the connection to the database is closed by setting the $conn variable to null.

Note that it’s important to properly sanitize and validate any user input used in the SQL statement to prevent SQL injection attacks. Additionally, be sure to use parameter binding when preparing SQL statements to prevent SQL injection attacks.

Once the record has been updated, you will be able to see the table as follows:

id	firstname	lastname	email	age	gender	reg_date
1	Matt	LeBlance	[email protected]	55	Male	2023-03-08 20:09:15
3	Jennifer	Lawrence	[email protected]	32	Female	2023-03-08 20:09:15

We value your feedback.

PHP MySQL Update Data

Update Data In a MySQL Table Using MySQLi and PDO

Example (MySQLi Object-Oriented):

Example (MySQLi Procedural):

Example (PDO):

Leave a Reply Cancel reply

Feeling bored?