PHP - SPLessons

PHP Update Data in MySQL

Chapter 39

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PHP Update Data in MySQL

PHP Update Data in MySQL

shape Description

“PHP Update Data in MySQL” chapter gives information on how to update the data in Database. MySQL tables information can be upgraded by executing UPDATE statement of SQL with the PHP function mysql_query.

To update any table record, first the record has to be located using conditional clause. Primary key can also be used to match with any of the employee table record.

shape Syntax

UPDATE table_name SET field1=value1, field2=value2 [WHERE Clause]

  • Single/Multiple fields to be updated altogether.
  • Using WHERE clause, define the conditions.
  • Values in a single table can be updated at a time.
  • While updating the selected rows in a table,WHERE clause is the best choice.

shape Examples

MySQLi (Object-oriented)

<!DOCTYPE html>
<html>
	<body>
		<?php 
			$HostName = "localhost"; 
			$UserName = "root"; 
			$Password = "password"; 
			$dbname="accounts"; 
			
			// Create connection 
			$connection = new mysqli($HostName, $UserName, $Password, $dbname); 
			
			// Check connection 
			if ($connection->connect_error) 
			{
				die("Connection failed: " . $connection->connect_error);
			} 
			 
			$sql = "UPDATE users2 SET Username='William', Password='password0', First='William', Last='Sarah' WHERE id=1";
			 
			if ($connection->query($sql) === TRUE) {
				echo "Record updated successfully";
			} else {
				echo "Error updating record: " . $connection->error;
			}
			 
			$connection->close();
		?>
	</body>
</html>

The output will be “Record updated successfully” by changing the records in phpmyadmin like below.

MySQLi (Procedural)

<!DOCTYPE html>
<html>
	<body>
		<?php 
			$HostName = "localhost"; 
			$UserName = "root"; 
			$Password = "password"; 
			$dbname="accounts"; 
			
			// Create connection 
			$connection = new mysqli($HostName, $UserName, $Password, $dbname); 
			
			// Check connection 
			$connection = mysqli_connect($servername, $username, $password, $dbname);
			// Check connection
			if (!$connection) {
				die("Connection failed: " . mysqli_connect_error());
			}
			 
			$sql = "UPDATE users2 SET Username='William', Password='password0', First='William', Last='Sarah' WHERE id=1";
			 
			if (mysqli_query($connection, $sql)) {
				echo "Record updated successfully";
			} else {
				echo "Error updating record: " . mysqli_error($connection);
			}

			mysqli_close($connection);
		?>
	</body>
</html>

Summary

shape Key Points

  • MySQL tables can be updated with SQL UPDATE statement.
  • To update a record in any table it is required to locate that record by using a conditional clause.