> ## Content Index
> Fetch the complete content index at: https://aabie.com/llms.txt
> Use this file to discover other available public pages before exploring further.
# How to Use PHP to Connect to a MySQL Database and Retrieve Data
- URL: https://aabie.com/php-connect-mysql-database/
- Published: 2026-10-25T03:12:19.000Z
- Updated: 2026-10-25T03:12:19.000Z
- Author: Daniel Carter
- Tags: Technology
To connect PHP to a MySQL database, you need to provide your database credentials to PHP and use either the mysqli or PDO extension to establish the connection. This lets your PHP code interact with the database to create dynamic, data-driven websites. This guide explains how to set up your credentials, connect using both mysqli and PDO, handle connection errors properly, run a simple test query, and follow best security practices to keep your data safe.
## What information do I need before connecting PHP to MySQL?
Before connecting PHP to MySQL, gather these four details:
- Hostname (usually 'localhost' if the database is on the same server as your PHP code)
- Username (a MySQL user account with permission to access the database)
- Password (for that MySQL user)
- Database name (the specific database you want to use)
If you don’t have these yet, create them via your hosting control panel or the MySQL command line. For example, these MySQL commands create a database, user, and grant privileges:
```sql
CREATE DATABASE my_database;
CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';
FLUSH PRIVILEGES;
```
Keep these credentials secure because your PHP script will use them to connect. Never share them publicly or commit them to source code repositories without protection.
## How do I connect to MySQL using PHP’s mysqli extension?
Use PHP’s mysqli extension like this:
1. Define your database credentials as variables:
```php
$host = 'localhost';
$user = 'my_user';
$password = 'my_password';
$database = 'my_database';
```
1. Create a new mysqli object to connect:
```php
$mysqli = new mysqli($host, $user, $password, $database);
```
1. Immediately check for connection errors:
```php
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
```
1. If there’s no error, the connection is established and you can run queries.
Common mistakes include typos in credentials or not checking for connection errors, which can cause silent failures. The object-oriented style shown here is clearer and easier to maintain than the procedural style.
## How do I connect to MySQL using PHP’s PDO extension?
PDO (PHP Data Objects) provides a consistent interface for connecting to MySQL and other databases.
1. Set up your connection details, including the Data Source Name (DSN):
```php
$host = 'localhost';
$dbname = 'my_database';
$user = 'my_user';
$password = 'my_password';
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
```
1. Use a try-catch block to create a PDO instance and handle errors:
```php
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}
```
1. Setting `PDO::ATTR_ERRMODE` to `PDO::ERRMODE_EXCEPTION` allows you to catch database errors cleanly.
PDO is recommended for new projects because it supports multiple database types and has advanced features like prepared statements for security. The `charset=utf8mb4` ensures proper support for all characters, including emojis.
## How can I check if the connection was successful and handle errors?
Checking your connection status prevents crashes and hides sensitive information.
With mysqli, check the `connect_error` property after creating the connection object. If it’s set, the connection failed. Instead of showing raw errors to users, log the details securely and display a general message.
Example:
```php
if ($mysqli->connect_error) {
error_log('Database connection failed: ' . $mysqli->connect_error);
die('Sorry, we are experiencing technical difficulties.');
}
```
With PDO, the try-catch block catches exceptions if the connection fails. Log the exception message and show a generic message instead of the exact error.
Avoid displaying raw error messages on public pages, as they might reveal database details. Log errors in files accessible only to you or the server administrator.
During development, you can show errors for debugging but disable this on live sites.
## How do I run a simple SELECT query to test the connection?
After connecting, run a simple SELECT query to verify your PHP script can communicate with MySQL.
With mysqli:
1. Write your SQL query:
```php
$sql = "SELECT id, name FROM users";
```
1. Run the query:
```php
$result = $mysqli->query($sql);
```
1. Check for results and display them safely:
```php
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo 'User ID: ' . htmlspecialchars($row['id']) . ', Name: ' . htmlspecialchars($row['name']) . '
';
}
} else {
echo 'No results found.';
}
```
With PDO:
1. Execute the query:
```php
$stmt = $pdo->query("SELECT id, name FROM users");
```
1. Fetch and display results:
```php
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($rows) {
foreach ($rows as $row) {
echo 'User ID: ' . htmlspecialchars($row['id']) . ', Name: ' . htmlspecialchars($row['name']) . '
';
}
} else {
echo 'No results found.';
}
```
Using `htmlspecialchars()` escapes special characters to prevent cross-site scripting (XSS) attacks.
This confirms your PHP code can retrieve data from the database.
## What security best practices should I follow when connecting PHP to MySQL?
Security is crucial when connecting PHP to MySQL. Follow these practices:
- Use prepared statements with bound parameters instead of inserting variables directly into SQL queries. This prevents SQL injection attacks. Both mysqli and PDO support prepared statements.
- Store your database credentials outside the web root or in environment variables, not hardcoded in scripts accessible via the web.
- Limit the database user’s privileges to only what your application needs. Avoid using root or admin accounts. Give the user only SELECT, INSERT, UPDATE, and DELETE rights on the specific database.
- Handle errors so detailed messages aren’t shown to users but are logged securely.
- Keep your PHP and MySQL versions up to date to benefit from security fixes.
Examples of prepared statements:
With mysqli:
```php
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
```
With PDO:
```php
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);
$results = $stmt->fetchAll();
```
Prepared statements ensure user input is treated as data, not executable code, reducing security risks.
## Conclusion
You now know how to connect PHP to a MySQL database using mysqli and PDO, handle connection errors properly, run test queries, and apply key security practices. Keep your credentials safe, always use prepared statements, and build your database interactions step by step. With these tools, you can create dynamic, secure web applications that interact with your data effectively.
## Frequently Asked Questions
### Which is better for beginners: mysqli or PDO?
Both mysqli and PDO work well for beginners. PDO offers more flexibility since it supports multiple database types and has a cleaner API for prepared statements. If you only plan to use MySQL, mysqli is simpler to start with. Many developers recommend PDO for new projects.
### Can I use PHP to connect to a remote MySQL database?
Yes, set the hostname to the remote server’s address instead of 'localhost'. Make sure the remote MySQL server allows connections from your PHP server and that your user has permission to connect remotely.
### What should I do if my PHP script times out when connecting?
A timeout usually means the PHP script can’t reach the MySQL server. Check the hostname is correct, the MySQL server is running, firewall settings allow connections, and your credentials are accurate. If the server is remote, verify network connectivity.