PHP Introduction | Database Access Using PDO | Listing Table Data with PDO

Tags:

Now let us actually access the database. First, we will retrieve the records stored in a table.

To retrieve record data, use a method that directly executes an SQL query, or command. This method is called query and is called as follows.

$variable = $pdo->query(query);

Specify the query text to execute as the argument, and the execution result is returned. To obtain all data from a table at once, the query is roughly as follows.

select * from table_name

The return value of this query method is an instance of a class called PDOStatement. You can call its fetch method to retrieve the records one by one. Therefore, use a loop to take out as many records as necessary and process them.

while ($variable = $pdostatement->fetch(fetch method specification)) {
    ...... process the retrieved record ......
}

The argument to fetch specifies how values are retrieved. Usually, specify PDO::FETCH_ASSOC. This extracts values as an associative array with each column name set as a key. After that, take the necessary values from the retrieved associative array and process them.

Let us create an example that retrieves and lists records from the previous sampletable table.

<?php
$result = "";
try {
    $pdo = new PDO("mysql:host=localhost:3306;dbname=mysampledata;charset=utf8", "root","1234");
    $statement = $pdo->query("select * from sampletable");
    while($record = $statement->fetch(PDO::FETCH_ASSOC)){
        $result .= "<tr>";
        foreach($record as $column){
            $result .= "<td>" . $column . "</td>";
        }
        $result .= "</tr>";
    }
} catch(PDOException $e){
    $result = "#ERR:" . $e->getMessage();
}
$pdo = null;
?>
<!DOCTYPE html>
<html lang="ko">
    <head> 
        <meta http-equiv="Content-Type"  content="text/html; charset=UTF-8" /> 
        <title>sample page</title>
        <style>
        h1 { font-size:14pt;
            padding:5px;
            background-color:#AAFFFF; }
        table tr td {
            padding:5px;
            background-color:#DDFFCC; }
        </style>
    </head>
    <body>
        <h1>Hello PHP!</h1>
        <table>
        <?php echo $result; ?>
        </table>
    </body>
</html>

Create this as a file named index.php. Here, $pdo->query executes select * from sampletable, and the obtained results are retrieved with fetch and arranged into a table.

At the end, there is a statement $pdo = null;. This releases the created PDO object. By explicitly setting the variable that stores the PDO object to null, the connection to the database is closed and resources are released. You can also release the variable with the unset function as follows, which has the same effect.

unset($pdo);