- PHP Basics
- PHP Home
- PHP Environment Setup
- PHP Getting Started
- PHP Basic Syntax
- PHP echo
- PHP print
- PHP echo Vs print
- PHP Comments
- PHP Data Types
- PHP Variables
- PHP Variable Scope
- PHP gettype()
- PHP Constants
- PHP Operators
- PHP Program Control
- PHP Decision Making
- PHP if-elseif-else
- PHP switch
- PHP Loops
- PHP for Loop
- PHP while Loop
- PHP do-while Loop
- PHP foreach Loop
- PHP break & continue
- PHP Popular Topics
- PHP Arrays
- PHP print_r()
- PHP Strings
- PHP Functions
- PHP References
- PHP Object Oriented
- PHP Object Oriented
- PHP Classes & Objects
- PHP Member Variable
- PHP Member Function
- PHP Encapsulation
- PHP Data Abstraction
- PHP Inheritance
- PHP Constructor Destructor
- PHP Polymorphism
- PHP Web Developments
- PHP Web Developments
- PHP GET & POST
- PHP Read Requested Data
- PHP File Handling (I/O)
- PHP File Handling (I/O)
- PHP fopen() | Open File
- PHP Create a File
- PHP fwrite() | Write to File
- PHP fread() | Read File
- PHP feof()
- PHP fgetc()
- PHP fgets()
- PHP fclose() | Close File
- PHP unlink() | Delete File
- PHP Append to File
- PHP copy() | Copy File
- PHP file_get_contents()
- PHP file_put_contents()
- PHP file_exists()
- PHP filesize()
- PHP rename() | Rename File
- PHP fseek()
- PHP ftell()
- PHP rewind()
- PHP disk_free_space()
- PHP disk_total_space()
- PHP mkdir() | Create Directory
- PHP rmdir() | Remove Directory
- PHP glob() | Get Files/Directories
- PHP basename() | Get filename
- PHP dirname() | Get Path
- PHP filemtime()
- PHP file()
- PHP Advanced
- PHP Cookies
- PHP Sessions
- PHP Send Emails
- PHP Serialization
- PHP Namespaces
- PHP File Upload
- PHP Date and Time
- PHP Image Processing
- PHP Regular Expression
- PHP Predefined Variables
- PHP Error Handling
- PHP Debugging
- PHP and MySQLi Tutorial
- PHP and MySQLi Home
- PHP MySQLi Setup
- PHP MySQLi Create DB
- PHP MySQLi Create Table
- PHP MySQLi Connect to DB
- PHP MySQLi Insert Record
- PHP MySQLi Fetch Record
- PHP MySQLi Update Record
- PHP MySQLi Delete Record
- PHP MySQLi SignUp Page
- PHP MySQLi LogIn Page
- PHP MySQLi Store User Data
- PHP MySQLi Close Connection
- PHP connect_errno
- PHP connect_error
- PHP query()
- PHP fetch_row()
- PHP fetch_assoc()
- PHP fetch_array()
- PHP free_result()
- PHP error
- PHP prepare()
- PHP bind_param()
- PHP execute()
- PHP fetch()
- PHP store_result()
- PHP num_rows
- PHP bind_result()
- PHP get_result()
- PHP mysqli_result Class
- PHP Error Constants
- PHP mysqli_driver()
- PHP Misc
- PHP error_reporting()
- PHP Escape Special Characters
- PHP htmlspecialchars()
- PHP new
- PHP header()
- PHP getallheaders()
- PHP empty()
- PHP isset()
- PHP unset()
- PHP exit()
- PHP exit Vs break
- PHP include()
- PHP require()
- PHP include() Vs require()
- PHP AJAX & XML
- PHP AJAX
- PHP XML
- PHP File Handling Functions
- PHP abs()
- PHP Test
- PHP Online Test
- Give Online Test
- All Test List
PHP MySQLi Update Data
This article is created to describe the way to update or modify specific data using PHP MySQLi script. These are the two approaches, that can be used to update any specific or all data in the database:
- Using PHP MySQLi Object-Oriented Script
- Using PHP MySQLi Procedural Script
Whatever the approach we choose to update the data. We need to follow these simple steps:
- Open a connection to the database
- Write an SQL statement regarding the data modification
- Initialize the written SQL statement to a variable
- Use this variable to perform the query against the database, to modify particular data
- Close the database connection
The SQL statement to update specific data is:
UPDATE tableName SET column1=value1, column2=value2, column3=value3, ..., columnN=valueN WHERE particularColumn=particularValue;
Important - Be aware while updating the data. The WHERE clause is used when we need to update particular record(s).
Be Aware - Omitting the WHERE clause, update all records in the table. Indirectly, deletes all the previous records, so be aware.
Update Data using PHP MySQLi Object-Oriented Script
To update particular data (record or row) using PHP MySQLi object-oriented script, follow the example given below. In this example, I am going to update age of record whose id is 3:
<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "codescracker"; $conn = new mysqli($servername, $username, $password, $dbname); if($conn->connect_errno) { echo "Connection to the database failed!<BR>"; echo "Reason: ", $conn->connect_error; exit(); } $sql = "UPDATE customer SET age='40' WHERE id=3"; $result = $conn->query($sql); if($result) { echo "Data updated successfully."; // block of code, to process further... } else { echo "Error occurred while updating the record!<BR>"; echo "Reason: ", $conn->error; } $conn->close(); ?>
Before executing the above PHP script, here is the snapshot of the table customer:
Here is the output produced by above PHP example, on updating the record using PHP MySQLi object-oriented script:
And following is the snapshot of the table named student after executing the above PHP script:
Note - The mysqli() is used to open a connection to the MySQL database server, in object-oriented style.
Note - The new keyword is used to create a new object.
Note - The connect_errno is used to get/return the error code (if any) from last connect call, in object-oriented style.
Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.
Note - The exit() is used to terminate the execution of the current PHP script.
Note - The query() is used to perform query on the MySQL database, in object-oriented style.
Note - The error is used to return the description of error (if any), by the most recent function call, in object-oriented style.
Note - The close() is used to close an opened connection, in object-oriented style.
Note - If you remove the WHERE clause from above PHP script, then the age column of all rows will set to 40.
The above example, can also be written as:
<?php $conn = new mysqli("localhost", "root", "", "codescracker"); if(!$conn->connect_errno) { if($conn->query("UPDATE customer SET age='40' WHERE id=3")) echo "Data updated successfully."; } $conn->close(); ?>
Update Data using PHP MySQLi Procedural Script
To update data using PHP MySQLi procedural script, follow the example given below:
<?php $conn = mysqli_connect("localhost", "root", "", "codescracker"); if(!mysqli_connect_errno()) { $sql = "UPDATE customer SET age='42' WHERE id=3"; if(mysqli_query($conn, $sql)) echo "Data updated successfully."; else { echo "Error occurred while updating the record!<BR>"; echo "Reason: ", mysqli_error($conn); } } mysqli_close($conn); ?>
Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.
Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.
Note - The mysqli_query() is used to perform query on the MySQL database, in procedural style.
Note - The mysqli_error() is used to return the description of error (if any), by the most recent function call, in object-oriented style.
Note - The mysqli_close() is used to close an opened connection to the MySQL database, in procedural style.
PHP MySQLi Object-Oriented - Update Multiple Columns
To update multiple columns at once, everything will be same as done in the section Update Data using PHP MySQLi Object-Oriented Script, except the SQL statement. That is, to update two columns say age and email, use the following SQL statement:
$sql = "UPDATE customer SET age='42', email='newmail@xyz.com' WHERE id=3";
And to update more columns, say three columns, use following SQL statement:
$sql = "UPDATE customer SET name='Lucas', age='42', email='newmail@xyz.com' WHERE id=3";
Here is the complete PHP MySQLi script to update multiple columns at once:
<?php $conn = new mysqli("localhost", "root", "", "codescracker"); if(!$conn->connect_errno) { $sql = "UPDATE customer SET name='Lucas', age='42', email='newmail@xyz.com' WHERE id=3"; if($conn->query($sql)) echo "Data updated successfully."; else { echo "Error occurred while updating the record!<BR>"; echo "Reason: ", $conn->error; } } $conn->close(); ?>
PHP MySQLi Procedural - Update Multiple Columns
<?php $conn = mysqli_connect("localhost", "root", "", "codescracker"); if(!mysqli_connect_errno()) { $sql = "UPDATE customer SET name='Lucas', age='42', email='newmail@xyz.com' WHERE id=3"; if(mysqli_query($conn, $sql)) echo "Data updated successfully."; else { echo "Error occurred while updating the record!<BR>"; echo "Reason: ", mysqli_error($conn); } } mysqli_close($conn); ?>
PHP MySQLi - Update All Rows at Once
To update all rows using PHP MySQLi script, all the process will be same, except that, remove/omit the WHERE clause. That is, to update all rows with particular columns, use this similar SQL statement:
$sql = "UPDATE customer SET email='newmail@xyz.com'";
And to update all rows with multiple columns, use this similar SQL statement:
$sql = "UPDATE customer SET name='Lucas', age='42', email='newmail@xyz.com'";
« Previous Tutorial Next Tutorial »
Follow/Like Us on Facebook
Subscribe Us on YouTube