PHP Database Interaction
Connecting To Databases Using PHP Data Access Extensions Such As MySQLi Or PDO
PHP offers powerful extensions for connecting to and interacting with databases. Two commonly used extensions are MySQLi (MySQL Improved) and PDO (PHP Data Objects). In this guide, we'll explore how to connect to databases using both MySQLi and PDO extensions in PHP, along with examples of CRUD (Create, Read, Update, Delete) operations.
MySQLi Extension
MySQLi is a PHP extension that provides an improved interface for interacting with MySQL databases. It offers both procedural and object-oriented approaches for database operations.
Connecting to MySQL Database
First, let's establish a connection to a MySQL database using MySQLi extension. We'll create a PHP script (mysqli_connect.php) to connect to the database server and select a specific database.
// Database configuration
$host = "localhost";
$username = "username";
$password = "password";
$database = "dbname";
// Create connection
$conn = new mysqli($host, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} else {
echo "Connected successfully";
}
?>
Performing CRUD Operations
Next, let's demonstrate CRUD operations (Create, Read, Update, Delete) using MySQLi extension.
Create Operation (Insert Data)
$sql = "INSERT INTO users (username, email) VALUES ('JohnDoe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
Read Operation (Select Data)
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Username: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
Update Operation (Modify Data)
$sql = "UPDATE users SET email='john.doe@example.com' WHERE username='JohnDoe'";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
Delete Operation (Remove Data)
$sql = "DELETE FROM users WHERE username='JohnDoe'";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
PDO Extension
PDO (PHP Data Objects) is a database abstraction layer that provides a consistent interface for accessing different databases. It supports various database drivers, including MySQL, PostgreSQL, SQLite, and more.
Connecting to MySQL Database
Let's establish a connection to a MySQL database using PDO extension. We'll create a PHP script (pdo_connect.php) to connect to the database server and select a specific database.
// Database configuration
$host = "localhost";
$username = "username";
$password = "password";
$database = "dbname";
try {
// Create connection
$conn = new PDO("mysql:host=$host;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();
}
?>
Performing CRUD Operations
Let's demonstrate CRUD operations using PDO extension.
Create Operation (Insert Data)
$username = "JohnDoe";
$email = "john@example.com";
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $stmt->errorInfo();
}
Read Operation (Select Data)
$sql = "SELECT id, username, email FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();
if ($stmt->rowCount() > 0) {
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "id: " . $row["id"]. " - Username: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
Update Operation (Modify Data)
$newEmail = "john.doe@example.com";
$username = "JohnDoe";
$sql = "UPDATE users SET email=:email WHERE username=:username";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':email', $newEmail);
$stmt->bindParam(':username', $username);
if ($stmt->execute()) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $stmt->errorInfo();
}
Delete Operation (Remove Data)
$username = "JohnDoe";
$sql = "DELETE FROM users WHERE username=:username";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':username', $username);
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $stmt->errorInfo();
}
Practice Excercise Practice now
Executing SQL Queries And Retrieving Data From Databases
Executing SQL queries and retrieving data from databases is a fundamental aspect of web development. PHP provides robust features and extensions like MySQLi and PDO to interact with databases seamlessly. In this guide, we'll explore how to execute SQL queries and retrieve data from databases using PHP, along with examples.
Connecting to the Database
Before executing SQL queries, it's essential to establish a connection to the database server. We'll 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();
}
?>
Executing SQL Queries
Once the database connection is established, we can execute SQL queries to perform various operations such as inserting, updating, deleting, and retrieving data.
Inserting Data
// Inserting data into a 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 Data
// Updating data in a 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 Data
// Deleting data from a table
$sql = "DELETE FROM users WHERE username='john'";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
?>
Retrieving Data
To retrieve data from the database, we execute SELECT queries and fetch the results.
Fetching Data
// Fetching data from a table
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
?>
Practice Excercise Practice now
Inserting, Updating, And Deleting Records In Database Tables Using PHP
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
Products
Partner
Copyright © RVR Innovations LLP 2024 | All rights reserved - Mytat.co is the venture of RVR Innovations LLP