PHP MySQLi Affected Rows 🎯

Welcome to our deep dive into understanding the PHP MySQLi Affected Rows! This lesson is designed for both beginners and intermediate learners, so let's get started! 📝

What are Affected Rows?

In the context of databases, affected rows refer to the number of rows that have been affected by a SQL query. This could mean inserted, updated, or deleted rows. Understanding affected rows is crucial for ensuring data integrity and troubleshooting database operations. 💡

Why use MySQLi?

MySQLi (MySQL Improved) is a PHP extension for accessing MySQL databases. It offers improved performance, security, and functionality over the traditional MySQL extension. In this tutorial, we will be focusing on the MySQLi extension to interact with MySQL databases. 📝

Connecting to a Database

Before we can work with affected rows, let's establish a connection to our database.

php

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
?>

Affected Rows in Action

Now, let's see how to work with affected rows in PHP using MySQLi.

Inserting a Row

php

<?php
// Insert a new row
$sql = "INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully. Affected rows: " . $conn->affected_rows;
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

Updating a Row

php

<?php
// Update an existing row
$sql = "UPDATE my_table SET column1 = 'new_value1', column2 = 'new_value2' WHERE id = 1";

if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully. Affected rows: " . $conn->affected_rows;
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

Deleting a Row

php

<?php
// Delete a row
$sql = "DELETE FROM my_table WHERE id = 1";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully. Affected rows: " . $conn->affected_rows;
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

📝 Note: Always validate your SQL queries before executing them to prevent SQL injection attacks.

Quiz Time 📝

That's it for today! In the next lesson, we'll explore more advanced topics related to PHP MySQLi. Keep coding and learning! 🎉