# PHP MySQL: Selecting and Fetching Data
Retrieving data from a database is one of the most fundamental tasks in web development. In PHP, you can query a MySQL database and fetch records using the SQL `SELECT` statement.
This tutorial covers how to select and display data from a MySQL database using three different approaches: **MySQLi (Object-Oriented)**, **MySQLi (Procedural)**, and **PDO (PHP Data Objects)**.
---
## The SQL SELECT Statement
To read data from one or more tables in a database, we use the SQL `SELECT` statement:
```sql
SELECT column_name1, column_name2 FROM table_name
```
If you want to retrieve all columns from a table, you can use the asterisk (`*`) wildcard character:
```sql
SELECT * FROM table_name
```
---
## 1. Selecting Data Using MySQLi (Object-Oriented)
In this example, we connect to a database named `myDB` and retrieve the `id`, `firstname`, and `lastname` columns from the `MyGuests` table.
```php
connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$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. " - Name: " . $row. " " . $row. "
";
}
} else {
echo "0 results";
}
$conn->close();
?>
```
### Code Explanation:
* **`$conn->query($sql)`**: Executes the SQL query and returns a result set object, which is assigned to the `$result` variable.
* **`$result->num_rows`**: This property checks if the query returned more than zero rows.
* **`$result->fetch_assoc()`**: Fetches a result row as an associative array. The keys of the array correspond to the column names of the table.
* **`while()` loop**: Iterates through the result set row by row, printing the values until there are no more rows left.
---
## 2. Selecting Data Using MySQLi (Procedural)
If you prefer procedural programming, you can achieve the exact same result using the procedural functions of the MySQLi extension:
```php
0) {
// Output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row. " - Name: " . $row. " " . $row. "
";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
```
### Code Explanation:
* **`mysqli_query($conn, $sql)`**: Executes the query on the specified connection.
* **`mysqli_num_rows($result)`**: Returns the number of rows in the result set.
* **`mysqli_fetch_assoc($result)`**: Fetches a result row as an associative array.
---
## 3. Selecting Data Using PDO (PHP Data Objects)
Using PDO is highly recommended because it supports multiple database systems and provides robust security features, such as prepared statements.
The following example uses PDO and PHP's `RecursiveIteratorIterator` to output the retrieved database records directly into an HTML table:
```php
";
echo "
| Id | Firstname | Lastname |
|---|
";
// Custom iterator class to format output as HTML table rows
class TableRows extends RecursiveIteratorIterator {
function __construct($it) {
parent::__construct($it, self::LEAVES_ONLY);
}
function current() {
return "
" . parent::current(). " | ";
}
function beginChildren() {
echo "
";
}
function endChildren() {
echo "
" . "\n";
}
}
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepare and execute the SQL statement
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests");
$stmt->execute();
// Set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
// Iterate and output the data
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
echo $v;
}
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
echo "";
?>
```
### Code Explanation:
* **`$conn->prepare()`**: Prepares the SQL statement. This is a secure practice that prevents SQL injection attacks.
* **`$stmt->execute()`**: Executes the prepared statement.
* **`$stmt->setFetchMode(PDO::FETCH_ASSOC)`**: Tells PDO to return each row as an associative array indexed by column name.
* **`RecursiveIteratorIterator`**: A built-in PHP utility class used here to traverse a multi-dimensional array (the query results) and wrap the values in HTML `
` and `| ` tags automatically.
---
## Best Practices and Considerations
1. **Select Only What You Need**: Avoid using `SELECT *` in production environments. Explicitly naming your columns (e.g., `SELECT id, firstname`) improves query performance and reduces unnecessary memory usage.
2. **Always Use Prepared Statements**: When your `SELECT` query depends on user input (e.g., `WHERE id = $user_input`), always use prepared statements with placeholders (`?` or named parameters) to prevent **SQL Injection** vulnerabilities.
3. **Close Connections**: While PHP automatically closes database connections when the script ends, explicitly closing them (`$conn->close()` or setting the PDO object to `null`) is a good practice to free up database resources immediately.
|
YouTip