Here i will show you how to connect with mysql database using PDO in four simple steps:-
1. Connect to database: create object of class PDO and pass $host(hostname/servername), $dbname(database name), $username (database username) and $passowrd (database user password).
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
2. cool.. now we will create a select query and execute the query using query() method of pdo class/object.
$sql = 'SELECT lastname, firstname, course FROM students ORDER BY lastname';
$q = $pdo->query($sql);
The query() method of the PDO object returns a PDOStatement object, or false on failure.
3. Next, set the PDO::FETCH_ASSOC fetch mode for the PDOStatement object by using the setFetchMode() method.
The PDO::FETCH_ASSOC mode instructs the fetch() method to return a result set as an array indexed by column name.
$q->setFetchMode(PDO::FETCH_ASSOC);
4. Now we will fetch row one by one from result set ($q) using fetch() method of PDOStatement object.
while ($r = $q->fetch()):
<?php echo htmlspecialchars($r['lastname'])
<?php echo htmlspecialchars($r['firstname']);
<?php echo htmlspecialchars($r['course']);
endwhile;
======================================================================
Putting it all together.
<?php
$host = 'localhost';
$dbname = 'classicmodels';
$username = 'root';
$password = '';
try
{
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$sql = 'SELECT lastname,firstname,course from students ORDER BY lastname';
$q = $pdo->query($sql);
$q->setFetchMode(PDO::FETCH_ASSOC);
}
catch (PDOException $e)
{
die("Could not connect to the database $dbname :" . $e->getMessage());
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP MySQL Query Data Demo</title>
<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="css/style.css" rel="stylesheet">
</head>
<body>
<div id="container">
<h1>Employees</h1>
<table class="table table-bordered table-condensed">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Job Title</th>
</tr>
</thead>
<tbody>
<?php while ($row = $q->fetch()): ?>
<tr>
<td><?php echo htmlspecialchars($row['lastname']) ?></td>
<td><?php echo htmlspecialchars($row['firstname']); ?></td>
<td><?php echo htmlspecialchars($row['course']); ?></td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
</body>
</div>
</html>
