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

Tags:

Now that we can add records and display a list, let us try “searching.” Searching is similar to the first “record list retrieval.” It retrieves data with select * from table. However, as it is, all records are retrieved, so we need to set a condition.

"select * from table where condition"

Add where after the table name and specify the condition after that. A condition specifies the value of a column, such as column = value. It is common to specify conditions using equality and inequality operators. For example, if you want to search for data where name is Sungjin in sampletable, write:

select * from sampletable where name = 'Sungjin'

Now let us modify the previous index.php to search by name and display the result. Modify the example as follows.

<?php
$result = "";
$query = "select * from sampletable";
if (isset($_POST['name'])){
    $fstr = $_POST['name'];
    if ($fstr != ''){
        $query .= " where name = '$fstr'";
    }
}
try {
    $pdo = new PDO("mysql:host=localhost:3306;dbname=mysampledata;charset=utf8", "root","1234");
    $statement = $pdo->query($query);
    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>
        <form method="post" action="./index.php">
            <tr><td>Search text:</td><td><input type="text" name="name"></td></tr>
            <tr><td></td><td><input type="submit" value="Submit"></td></tr>
        </form>
        </table>
        <hr>
        <table>
        <?php echo $result; ?>
        </table>
    </body>
</html>

This time, a form with one input field is displayed. If you write the name to search for and execute it, only records with that name are displayed.

First, the query is created as $query = "select * from sampletable";. This searches all records. Then if (isset($_POST['name'])) checks whether $_POST['name'] exists. If there is a value, the where clause is added to the query with $query .= " where name = '$fstr'";, so a condition is included in the query. This searches only records where name is $fstr.

At this point, if you do not know the word exactly, you cannot search for it. Therefore, in practice, searches often use a “fuzzy” search based on part of the entered word. In that case, use a comparison operator called like. This lets you use a wildcard (*) in the search text.

where name like 'Won*'

For example, if the where clause is written like this, all data whose name value starts with Won is found.