| Introduction to Web Development |
|---|
| Basic PHP Syntax |
|---|
| PHP Basics |
|---|
| Variables and Data Types |
|---|
| Functions in PHP |
|---|
| User-defined Functions |
|---|
| Built-in Functions |
|---|
| Variable Scope |
|---|
| Include and Require |
|---|
| Working with Forms |
|---|
| Forms in HTML |
|---|
| Form Handling in PHP |
|---|
| Working with Files |
|---|
| File Handling |
|---|
| File System Functions |
|---|
| Working with Databases |
|---|
| Introduction to Databases |
|---|
| Advanced PHP Techniques |
|---|
| Object-Oriented Programming (OOP) |
|---|
| Error and Exception Handling |
|---|
Performing database queries with PHP involves using PHP to interact with a database, typically a MySQL or MariaDB database. Here’s a detailed description of how to perform SELECT, INSERT, UPDATE, and DELETE queries using PHP.
1. Setting Up the Connection
First, you need to connect to the database. PHP provides mysqli and PDO (PHP Data Objects) extensions to connect to and interact with databases. I’ll use mysqli in this explanation.
Connecting to the Database
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
2. SELECT Query
A SELECT query is used to fetch data from the database.
Example
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$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"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
3. INSERT Query
An INSERT query is used to add new records to the database.
Example
<?php
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
4. UPDATE Query
An UPDATE query is used to modify existing records in the database.
Example
<?php
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
5. DELETE Query
A DELETE query is used to remove records from the database.
Example
<?php
$sql = "DELETE FROM MyGuests WHERE id=3";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>
Using Prepared Statements
To prevent SQL injection, it’s best practice to use prepared statements.
Prepared SELECT
<?php
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname = ?");
$stmt->bind_param("s", $lastname);
$lastname = "Doe";
$stmt->execute();
$stmt->bind_result($id, $firstname, $lastname);
while ($stmt->fetch()) {
echo "id: $id - Name: $firstname $lastname<br>";
}
$stmt->close();
$conn->close();
?>
Prepared INSERT
<?php
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
echo "New record created successfully";
$stmt->close();
$conn->close();
?>
Prepared UPDATE
<?php
$stmt = $conn->prepare("UPDATE MyGuests SET lastname = ? WHERE id = ?");
$stmt->bind_param("si", $lastname, $id);
$lastname = "Smith";
$id = 2;
$stmt->execute();
echo "Record updated successfully";
$stmt->close();
$conn->close();
?>
Prepared DELETE
<?php
$stmt = $conn->prepare("DELETE FROM MyGuests WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 3;
$stmt->execute();
echo "Record deleted successfully";
$stmt->close();
$conn->close();
?>
These are the basics of performing SELECT, INSERT, UPDATE, and DELETE queries with PHP and MySQL. Always remember to close the statement and connection after executing your queries to free up resources.
