There may be a requirement that existing data in a MySQL table must be modified. You can do this by using the SQL UPDATE command. This will modify any field value of any MySQL table.
Syntax
The following code block has a generic SQL syntax of the UPDATE command to modify the data in the
MySQL table: UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE clause] You can update
- one or more fields completely
- specify any condition by using the WHERE clause
- You can update values for only one table at a time.
. You can
.
The WHERE clause
is very useful when you want
to update selected rows in a table. Updating data from the command
prompt
This will use the SQL UPDATE command with the WHERE clause to update the selected data in the MySQL table tutorials_tbl
.
Example
The following example will update the tutorial_title field for a record that has the tutorial_id as 3.
root@host# password mysql -u root -p; Enter password:******* mysql> use TUTORIALS; Changed database mysql> UPDATE tutorials_tbl -> SET tutorial_title = ‘Learning JAVA’ -> WHERE tutorial_id = 3; Query successful, 1 row affected (0.04 sec) Matching rows: 1 Changed: 1 Warnings: 0 mysql>
Refreshing data using a PHP script PHP uses the
mysqli query() or mysql_query() function to update records in a MySQL table. This function takes two parameters and returns TRUE in case of success or FALSE in case of error.
Syntax
$mysqli->query($sql,$resultmode) Sr.No. Parameter and Description 1
$sql
Required – SQL query to update records in a MySQL table
. 2
$resultmode
Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.
Example
Try the following example
to update a record in a table
−
Copy and paste the following example as mysql_example.php −
<html> <head> <title>Updating MySQL Table</title> </head> <body> <?php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘root@123’; $dbname = ‘TUTORIALS’; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if($mysqli->connect_errno ) { printf(“Connection error: %s<br />”, $mysqli->connect_error); exit(); } printf(‘Successfully connected.<br />’); if ($mysqli->query(‘UPDATE tutorials_tbl set tutorial_title = “Learning Java” where tutorial_id = 4’)) { printf(“Table tutorials_tbl updated successfully.<br />”); } if ($mysqli->errno) { printf(“Could not update table: %s<br />”, $mysqli->error); } $sql = “SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl”; $result = $mysqli->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { printf(“Id: %s, Title: %s, Author: %s, Date: %d <br />”, $row[“tutorial_id”], $row[“tutorial_title”], $row[“tutorial_author”], $row[“submission_date”]); } } else { printf(‘No record found.<br />’); } mysqli_free_result($result); $mysqli->close(); ?> </body> </html>
Output
Access the deployed mysql_example.php on the Apache web server and verify the output. Here we have entered several records in the table before running the select script.
Connected successfully. The table tutorials_tbl updated successfully. Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021 Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021 Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021 Id: 4, Title: Learning Java, Author: Mahesh, Date: 2021 Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021