Performing operations like inserting, updating, and deleting records in database tables is crucial in web development. PHP, with its database extensions like MySQLi and PDO, provides powerful features to handle these operations efficiently. In this guide, we'll explore how to insert, update, and delete records in database tables using PHP, along with examples.
Connecting to the Database
Before performing any database operations, we need to establish a connection to the database server. Let's demonstrate connecting to a MySQL database using both MySQLi and PDO extensions.
MySQLi Extension
// MySQLi database connection configuration
$servername = "localhost";
$username = "username";
$password = "password";
$database = "dbname";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
PDO Extension
// PDO database connection configuration
$servername = "localhost";
$username = "username";
$password = "password";
$database = "dbname";
try {
// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Inserting Records
To insert new records into a database table, we use SQL INSERT statements. Let's insert a new record into a users table.
// Inserting a record into the users table
$sql = "INSERT INTO users (username, email) VALUES ('john', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
?>
Updating Records
To update existing records in a database table, we use SQL UPDATE statements. Let's update the email address of a user in the users table.
// Updating a record in the users table
$sql = "UPDATE users SET email='newemail@example.com' WHERE username='john'";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
?>
Deleting Records
To delete specific records from a database table, we use SQL DELETE statements. Let's delete a user from the users table.
// Deleting a record from the users table
$sql = "DELETE FROM users WHERE username='john'";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
?>
Practice Excercise Practice now