I made a fast and extremely simple example of PHP’s own PDO class to handle a MySQL database for my class example. We will need a database table called ‘task’ in a MySQL server running on localhost for this to work. The table contains two fields:
id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY name VARCHAR(64)
Now the PHP side, here’s the ‘Index.php’ file:
<?php
include 'PDO.php'; // 1
$result = getTodo(); // 2
?>
<!doctype html>
<html>
<head>
<meta charset="utf8" />
</head>
<body>
<h2>Todo list:</h2>
<?php
foreach($result as $row) { // 3
echo $row['id'] . ' ' . $row['name']; // 4
}
?>
</body>
</html>
- Includes the PDO class file
- Calls the function ‘getTodo()’ in ‘PDO.php’, which returns all rows in the task table to the ‘$result’ variable
- Goes through the table of rows returned in the variable ‘$result’
- Each ‘$row’ is a row in the database table, here we print out the ID and the task name
Here’s the ‘PDO.php’ file, that will do all the talking with my database:
<?php
function getConn() { // 1
return new PDO('mysql:host=localhost;dbname=todo', 'pasteUserHere', 'pastePasswordHere' ); // 2
}
function getTodo() { // 3
$db = getConn(); // 4
$stmt = $db->prepare("SELECT id, name FROM task"); // 5
$stmt->execute(); // 6
return $stmt->fetchAll(); // 7
}
?>
- A separate function for the PDO connection, so it doesn’t need to be repeated
- Returns the PDO connection to the function caller (set your own information below)
- Function to get all the tasks out of the database
- Get the connection from the function above into the variable ‘$db’
- Prepare the select clause, with this you can also easily use prepared statements
- Execute the SQL command
- Return all the database rows to the function caller