PHP MySQLi UPDATE - Updating Data in the Database

Updating Data with the UPDATE Statement

The UPDATE statement is used to modify existing records in a table. Similar to deleting, the WHERE clause is of crucial importance here as well. If you omit it, all records in the table will be updated!

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

// Establishing a connection
$connection = mysqli_connect($server, $username, $password, $database);

if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// 1. Creating the table (if it does not exist yet)
$table = "CREATE TABLE IF NOT EXISTS mysqli_table (
    table_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fruit VARCHAR(255) NOT NULL,
    vegetable VARCHAR(150) NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
mysqli_query($connection, $table);

// 2. Inserting 4 initial records
$insert_sql = "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Apple', 'Carrot');";
$insert_sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Banana', 'Cabbage');";
$insert_sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Orange', 'Turnip');";
$insert_sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Plum', 'Chicory')";
mysqli_multi_query($connection, $insert_sql);

// Wait for multi_query to finish insertions
while (mysqli_next_result($connection)) { if (!mysqli_more_results($connection)) break; }

// 3. Updating a record (Change data in the row with ID 3)
$update_sql = "UPDATE mysqli_table SET fruit = 'Melon', vegetable = 'Bell pepper' WHERE table_id = 3";
mysqli_query($connection, $update_sql);

// 4. Reading all data for final output
$sql = "SELECT table_id, fruit, vegetable, date FROM mysqli_table";
$result = mysqli_query($connection, $sql);

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Use SET to specify new column values.

Always specify WHERE to avoid accidentally changing the entire database.