How to get data from row using id number in link using PDO prepared Statement

Question

The issue is that I am trying to just fetch one row using id number for that row, and then be able to echo out the results in html.
I am passing the id number in a link.
EXAMPLE: link.php?id=55

I have search and searched for examples, and I have found many script examples out there to go by.

<?php 
include("/includes/db.php"); 
$id=$_GET['id'];
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $sql = 'SELECT * FROM MyFAQlist WHERE id = '.$id.' ORDER BY visits DESC';
    $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>
<meta name="robots" content="noindex, nofollow">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Link</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.6.3/css/all.css" integrity="sha384-UHRtZLI+pbxtHCWp1t77Bi1L4ZtiqrqD80Kn4Z8NTSRyMA2Fd33n5dQ8lWUE00s/" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome-animation/0.2.1/font-awesome-animation.css" integrity="sha256-3NjHxD73dx5Pf2EgnPZPlzE+/KcUEhyR2kaGPH7vGCc=" crossorigin="anonymous" />
</head>
<body>
        <div id="container">
            <h1>LINK INFORMATION</h1>
            <table class="table table-bordered table-condensed">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>VISITS</th>
                        <th>DATE</th>
                        <th>NAME</th>
                        <th>HTML</th>
                    </tr>
                </thead>
                <tbody>
                    <?php while ($row = $q->fetch()): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($row['id']) ?></td>
                            <td><?php echo htmlspecialchars($row['visits']) ?></td>
                            <td><?php echo htmlspecialchars($row['reg_date']) ?></td>
                            <td><?php echo "<a target=\"_blank\" rel=\"noopener\" href=\"" . htmlspecialchars($row['TXTurl']). "\" >" . htmlspecialchars($row['TXTlinkname']). "</a>"; ?></td>
<td><textarea class="form-control clip Spud-Bud-clip" onclick="this.focus();this.select()"rows="2" cols="50"><a target="_blank" rel="noopener" href="<?php echo htmlspecialchars($row['TXTurl']) ?>"><?php echo htmlspecialchars($row['TXTlinkname']) ?></a></textarea></td>
                        </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
    </body>
</div>
</html>

I got it working but I am getting an error on line 8

Fatal error: Uncaught Error: Call to a member function setFetchMode() on boolean in...

I would appreciate your feedback and suggestions to correct the error and any other errors I made

There are no [prepared statements](https://secure.php.net/manual/en/pdo.prepared-statements.php) here. The query failed, which is why you're not getting a PDOStatement object in return. — aynber, Aug 08 '19 at 14:38
In addition, the code shown contains an sql injection vulnerability, if I am not mistaken. No escaping of the `$id` parameter, taken directly from evil outside user data `$_GET['id']`. — Sven, Aug 08 '19 at 18:15

score 0 · Accepted Answer · answered Aug 08 '19 at 18:35

If I understand correctly, your question is asking how to use prepared statements correctly with your example. Let me answer this question.

Take a look at the comments I made along my changes:

<?php
include "/includes/db.php";
$id = $_GET['id'];
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,        // enable PDO errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,   // fetch associative arrays by default
    PDO::ATTR_EMULATE_PREPARES => false,                // Use native prepared statements
];
//                      You should always specify charset VVVVV
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password, $options);

// Prepare and execute SQL passing in the value of $id
$MyFAQlist = $pdo->prepare('SELECT * FROM MyFAQlist WHERE id = ? ORDER BY visits DESC');
$MyFAQlist->execute([$id]);
?>

Instead of the while loop you can simply use foreach:

<?php foreach($MyFAQlist as $row): ?>

Getting error... Parse error: syntax error, unexpected end of file in file.php on line 17 — J D, Aug 10 '19 at 21:28
You probably missed some closing brace or something like this — Dharman, Aug 10 '19 at 21:30

How to get data from row using id number in link using PDO prepared Statement

1 Answers1