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