Retrieving data from a MySQL database using PHP is a foundational skill for web development. Here's a comprehensive guide on how to use the `SELECT` statement to get data from a MySQL database using PHP.
Before fetching data, you need to establish a connection to the MySQL database. The PHP `mysqli` (MySQL Improved) extension is commonly used for this purpose.
<?php
$servername = "localhost"; // Usually "localhost"
$username = "username"; // Your MySQL username
$password = "password"; // Your MySQL password
$dbname = "myDB"; // Your database name
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
The `SELECT` statement is used to retrieve data from a database. The data returned is stored in a result table, known as the result-set.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Assume we have a table named "users" with columns "id", "firstname", and "lastname".
$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"] . "<br>";
}
} else {
echo "0 results";
}
Retrieve data of users with the firstname "John":
$sql = "SELECT id, firstname, lastname FROM users WHERE firstname='John'";
// rest of the code remains the same
After executing a SELECT query, the data can be fetched in a number of ways:
Here's an example using fetch_array():
while($row = $result->fetch_array()) {
echo "id: " . $row[0] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
It's important to close the connection after you've finished with your database operations.
$conn->close();
When fetching data or performing any database operations, always be cautious about SQL injection attacks. Always use prepared statements or parameterized queries to ensure your application's security. Example using prepared statements:
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM users WHERE firstname = ?");
$stmt->bind_param("s", $firstname); // "s" means the variable is a string
$firstname = "John";
$stmt->execute();
$result = $stmt->get_result(); // Get the result-set
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
$stmt->close();
$conn->close();
This way, user input can't directly interfere with your SQL query structure, making it safer from SQL injections. Always validate and sanitize user input before using it in any database operations.
UPCET Exam
Click Here
SAAT Exam
Click Here
MHT CET Exam
Click Here
IPU CET Exam
Click Here
KCET Exam
Click Here
COMEDK UG Exam
Click Here
VITEEE Exam
Click Here
BITSAT
Click Here
DSAT: Dayanand Sagar Admission Test
Click Here
Career In Animation in india
Click Here
Merchant Navy Courses in india
Click Here
Interior Design Career in india
Click Here
UGC NET Exam
Click Here
B. Ed Exam
Click Here
AFCAT - Air Force Common Admission Test
Click Here
GATE Exam
Click Here
Joint Entrance Examination (JEE)
Click Here
Common Admission Test (CAT)
Click Here
CDS - Combined Defence Services Exam
Click Here