📖 PHP Connecting to a Database

Why Use PDO?

PDO
A built-in PHP class that provides a consistent interface for accessing different types of databases.
prepare()
Creates a SQL statement that can be safely executed with user data to avoid injection.
bindParam()
Binds a value to a named or question mark placeholder in a SQL statement before execution.
fetch() / fetchAll()
Retrieves rows from a result set, either one-by-one or all at once (as associative arrays).

Setting Up db_connect.php

Create a db_connect.php file that establishes your database connection using PDO. Place this file outside your public web root if possible.

// db_connect.php
$host = 'localhost';
$db   = 'your_database';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    exit('Database connection failed: ' . $e->getMessage());
}
$host, $db, $user, $pass, $charset
These variables store the connection details: database server location, name, username, password, and character set. These values must match your phpMyAdmin or hosting credentials.
$dsn (Data Source Name)
A string that tells PDO how to connect to the database. It includes the driver (MySQL), host, database name, and character encoding.
$options
An array of configuration flags that customize PDO's behavior:
  • PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION — makes PDO throw exceptions on errors, which helps with debugging.
  • PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC — returns query results as associative arrays (e.g., $row['Name']).
  • PDO::ATTR_EMULATE_PREPARES => false — disables emulated prepared statements to use real ones for security.
new PDO(...)
This creates the actual database connection using the values above. If the connection fails, it throws a PDOException.
try/catch block
Wraps the connection in a try block to catch errors cleanly. If a connection error occurs, the script stops and displays a helpful error message.

Using the Connection in a Script

Include the db_connect file at the top of your script and query the database using prepare() and execute().

include_once 'db_connect.php';

$stmt = $pdo->prepare("SELECT * FROM [table_name]");
$stmt->execute();
$rows = $stmt->fetchAll();
prepare()
Initializes a SQL query and returns a statement object. Helps protect against SQL injection.
execute()
Runs the prepared SQL statement. Can optionally accept an array of values to bind into the statement.
fetchAll()
Retrieves all rows from the executed query result as an array of associative arrays.

Displaying the Results

Use a foreach() loop to output results in a table. This is clean and avoids complex OOP structures.

echo "<table><tr><th>ID</th><th>Name</th></tr>";
foreach ($rows as $row) {
    echo "<tr><td>{$row['id']}</td><td>{$row['field_name']}</td></tr>";
}
echo "</table>";

Alternative Syntax for Cleaner Templates

PHP offers a special syntax for control structures like if, foreach, and while when you're embedding them inside HTML. Instead of using curly braces, you can use a colon (:) and an endif;, endforeach;, etc. This helps improve readability when mixing PHP with HTML templates.

Here's the same example rewritten using alternative PHP syntax:

<table>
    <tr><th>ID</th><th>Name</th></tr>
    <?php foreach ($rows as $row): ?>
        <tr>
            <td><?= htmlspecialchars($row['id']) ?></td>
            <td><?= htmlspecialchars($row['field_name']) ?></td>
        </tr>
    <?php endforeach; ?>
</table>

When to Use Alternative Syntax

  • When mixing PHP with lots of HTML (especially in views)
  • To avoid confusing nested curly braces
  • To keep your layout clean and readable

Supported Keywords

You can use the alternative control structure syntax with the following PHP keywords. This format is especially useful for templates where HTML and PHP are mixed:

Traditional Syntax Alternative Syntax 
if (condition) { 
// do something
} elseif (condition) {
// do something else
} else { 
// do default action
}
 
if (condition): 
// do something
elseif (condition): 
// do something else
else: 
// do default action
endif;
 
foreach ($items as $item) {
  // code block
}
 
foreach ($items as $item):
  // code block
endforeach;
 
for ($i = 0; $i < 10; $i++) {
  // code block
}
 
for ($i = 0; $i < 10; $i++):
  // code block
endfor;
 
while ($condition) {
  // code block
}
 
while ($condition):
  // code block
endwhile;
 
switch ($value) {
  case 1:
    // do something
    break;
}
 
switch ($value):
  case 1:
    // do something
endswitch;

Common Pitfalls

  • Incorrect login credentials — check your username and password carefully.
  • DSN typos — the format must be mysql:host=...;dbname=...;charset=...
  • Not catching exceptions — always use try/catch to catch errors cleanly.

Troubleshooting

Running Test Queries with phpMyAdmin

phpMyAdmin provides a convenient way to test your SQL queries directly against your database before writing any PHP code. To test your database connection and queries, you can use phpMyAdmin. This is a great way to confirm your SQL syntax and see if the database responds as expected. You can run any SQL query directly in the phpMyAdmin interface.

For example, to test the connection and see if you can retrieve data from a table, you can run a simple SELECT query on any table in your database.

SELECT * FROM `[table_name]`;

Replace [your_table_name] with the actual name of your table. If the query runs successfully, you should see the results displayed in the phpMyAdmin interface. The same query should run without error in your scripts.

💡 Pro Tip: Using phpMyAdmin to test your queries can help you catch syntax errors and confirm that your database is set up correctly before you start writing PHP code. This practice helps isolate whether an issue lies with your SQL syntax or your PHP code.

Pulling It All Together

Building the playlist app

To build and test your playlist app, follow these steps. Be sure you have already created a working db_connect.php file — this file stores your database credentials and is reused throughout your project.

  1. Set up your db_connect.php file: This file should create and return a valid PDO object. Place it in your project root.
  2. Create the database table: Use phpMyAdmin to run the SQL script provided below to create and populate the playlist table.
  3. Test the table: Run a simple test query in phpMyAdmin like SELECT * FROM playlist; to confirm the data was inserted correctly.
  4. Build the data access script: Write a new PHP file (e.g., playlistModel.php) that includes db_connect.php and fetches records using PDO.
  5. Create a display script: In a separate file (e.g., playlistView.php), include the model and display the results in an HTML table.

🧠 Best Practice: Keep your database logic in a separate file from your HTML. This makes your code easier to maintain and improves project readability.

By following these steps, you will have a basic PHP application that connects to a MySQL database using PDO, retrieves data, and displays it in a user-friendly format. This structure sets the foundation for building more complex applications with PHP and MySQL.

The Database playlist Table

This is the table structure and sample data for the playlist table. You can run this SQL script in phpMyAdmin to create and populate the table.

-- Table structure for table `playlist`

DROP TABLE IF EXISTS `playlist`;

CREATE TABLE `playlist` (
	`id` int NOT NULL AUTO_INCREMENT,
	`name` varchar(120) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `playlist`
--

INSERT INTO `playlist` VALUES (1,'Music'),(2,'Movies'),(3,'TV Shows'),(4,'Audiobooks');

Display Playlist in HTML

This approach separates the database logic (model) from the HTML structure (view). Below are two sample files that work together to display the contents of the playlist table.

playlistModel.php

This file retrieves the playlist data from the database and returns it as an array:

<?php
include_once 'db_connect.php';

function getAllPlaylists(PDO $pdo): array {
  $query = "SELECT * FROM playlist";
  $stmt = $pdo->prepare($query);
  $stmt->execute();
  return $stmt->fetchAll();
}
?>
playlistView.php

This updated view file uses the alternative syntax to cleanly embed PHP within HTML. It's easier to read and maintain — especially when working inside templates:

<?php
include_once 'playlistModel.php';
$rows = getAllPlaylists($pdo);

$pageTitle = "Playlist Viewer";
include 'header.php';
?>

<?php if ($rows): ?>
  <table>
  <caption>Sample Table Output</caption>
  <tr><th>ID</th><th>Name</th></tr>
  <?php foreach ($rows as $row): 
    $id = htmlspecialchars($row['id']);
    $name = htmlspecialchars($row['name']);
  ?>
  <tr><td><?= $id ?></td><td><?= $name ?></td></tr>
  <?php endforeach; ?>
  </table>
<?php else: ?>
  <p>No records found.</p>
<?php endif; ?>

<?php include 'footer.php'; ?>

Sample Table Output

Below is the output expected from running the script above on the playlist table.

ID Name
1 Music
2 Movies
3 TV Shows
4 Audiobooks

Additional Resources

Last updated: August 5, 2025 at 6:36 PM