One of the most common tasks in web applications is to interact with databases to store, retrieve, and manipulate data. PHP provides several database extensions that allow developers to connect to and work with various database management systems (DBMS) such as MySQL, PostgreSQL, Oracle, and SQLite.
Database Drivers and Extensions
PHP offers different database extensions, each designed to work with specific database systems. The most commonly used extensions are:
- mysqli: This extension is used for connecting to MySQL databases. It provides an object-oriented interface for accessing MySQL databases.
- PDO (PHP Data Objects): PDO is a modern and efficient database access layer that provides a consistent interface for different database systems. It supports various DBMS like MySQL, PostgreSQL, SQLite, Oracle, and more.
- SQLite3: This extension allows you to work with SQLite databases, which are file-based and serverless.
- PostgreSQL: The PostgreSQL extension provides functions for working with PostgreSQL databases.
- Oracle: PHP offers extensions for connecting to Oracle databases, such as OCI8 and PDO_OCI.
Connecting to a Database
Before you can interact with a database, you need to establish a connection. Here's an example of connecting to a MySQL database using the mysqli extension:
$servername = "localhost";
$username = "username";
$password = "password";
$database = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
And here's an example of connecting to a MySQL database using the PDO extension:
$servername = "localhost";
$username = "username";
$password = "password";
$database = "database_name";
try {
$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();
}
Creating Tables and Inserting Data
Once you have established a connection to the database, you can create tables and insert data using SQL queries. Here's an example of creating a table and inserting data using the mysqli extension:
// SQL query to create a table
$sql = "CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50)
)";
if ($conn->query($sql) === TRUE) {
echo "Table users created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
// SQL query to insert data
$sql = "INSERT INTO users (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "
" . $conn->error;
}
And here's an example using the PDO extension:
try {
// SQL query to create a table
$sql = "CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50)
)";
// Create table
$conn->exec($sql);
echo "Table users created successfully";
// SQL query to insert data
$sql = "INSERT INTO users (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";
// Insert data
$conn->exec($sql);
echo "New record created successfully";
} catch(PDOException $e) {
echo $sql . "
" . $e->getMessage();
}
Retrieving Data
Retrieving data from a database is a common operation in web applications. Here's an example of how to fetch data using the mysqli extension:
$sql = "SELECT id, firstname, lastname 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["firstname"]. " " . $row["lastname"]. "
";
}
} else {
echo "No results found";
}
And here's an example using the PDO extension:
$sql = "SELECT id, firstname, lastname FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach($stmt->fetchAll() as $row) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
";
}
Updating and Deleting Data
In addition to creating and retrieving data, you can also update and delete data in a database. Here's an example of updating data using the mysqli extension:
$sql = "UPDATE users SET email='[email protected]' WHERE id=1";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
And an example of deleting data:
$sql = "DELETE FROM users WHERE id=1";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
Using the PDO extension, updating and deleting data can be done as follows:
// Update data
$sql = "UPDATE users SET email=? WHERE id=?";
$stmt = $conn->prepare($sql);
$stmt->execute(['[email protected]', 1]);
echo "Record updated successfully";
// Delete data
$sql = "DELETE FROM users WHERE id=?";
$stmt = $conn->prepare($sql);
$stmt->execute([1]);
echo "Record deleted successfully";
Prepared Statements and SQL Injection
When working with user input in SQL queries, it's crucial to protect your application from SQL injection attacks. SQL injection is a technique where malicious SQL statements are inserted into an application's input fields to gain unauthorized access or manipulate data in a database.
To prevent SQL injection, you should always use prepared statements or parameterized queries. Prepared statements separate the SQL query from the user input, making it harder for attackers to inject malicious code. Here's an example of using prepared statements with the mysqli extension:
$firstname = mysqli_real_escape_string($conn, $_POST['firstname']);
$lastname = mysqli_real_escape_string($conn, $_POST['lastname']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$stmt = $conn->prepare("INSERT INTO users (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
if ($stmt->execute() === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
And here's an example using prepared statements with the PDO extension:
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$sql = "INSERT INTO users (firstname, lastname, email) VALUES (:firstname, :lastname, :email)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Error: " . $stmt->errorInfo()[2];
}
By using prepared statements, you can safely handle user input and prevent SQL injection attacks, ensuring the security and integrity of your web application.
In the next section, we'll explore about comments in PHP, which allows you to document or hide your codes.