Database Queries Tutorial
Introduction to Database Queries
Database queries are essential for interacting with databases. They allow you to create, read, update, and delete data. In this tutorial, we will cover the basics of database queries using PHP.
Connecting to a Database
Before you can run any queries, you need to connect to a database. In PHP, this is typically done using the mysqli or PDO extension.
Example using mysqli:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Executing a Simple Query
Once connected, you can execute SQL queries to interact with your database. Here is an example of a simple SELECT query.
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();
?>
Handling Query Results
After running a query, you often need to handle the results. This typically involves iterating over rows of data.
Example:
<?php
$sql = "SELECT * FROM Users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Inserting Data
To insert data into a database, you use the INSERT INTO statement.
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();
?>
Updating Data
To update existing data in a database, you use the UPDATE statement.
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();
?>
Deleting Data
To delete data from a database, you use the DELETE FROM statement.
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();
?>
Prepared Statements
Prepared statements are used to execute the same statement repeatedly with high efficiency and to prevent SQL injection attacks.
Example:
<?php
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// Set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();
echo "New records created successfully";
$stmt->close();
$conn->close();
?>