using prepared statements and fetchAll() to display to a table

Question

I'm trying to get my code SQL Injection safe, I am having trouble converting the pdo data to an array then comparing row data.

I have been reading up on how to prevent sql injection as well as fetchAll() documentation and how to handle SELECT statements with pdo.

Here is the relevant code. I believe it prepares the statement, then executes it, then the table is fetched and stored in $data where it is handed to $row, and then it looks up the player column and compares it with the logged in user to get the context based code to run. Where is the issue?


$stmt = $pdo->prepare("SELECT * FROM userdata");

$stmt->execute();

$data = $stmt->fetchAll();

echo "<table border='1'>
<tr>
<th>username</th>
<th>words</th>
</tr>";

while($row = $data)
{
echo $row['player'];
echo "<tr>";
echo "<td>" . $row['player'] . "</td>";
if($row['player'] == $logedInUsername)
{
    echo "<td>" . $row['words'] . "<a href='edityourword.php?edit=$row[words]'> edit</a></td>";
}
else
{
    echo "<td>" . $row['words'] . "</td>";
}
echo "</tr>";
}
echo "</table>";

My current error is reoccurring, here is the segment which the while loop keeps printing.

Notice: Undefined index: player on line 41

Notice: Undefined index: player on line 43

Notice: Undefined index: player on line 44

Notice: Undefined index: words on line 50

Notice: Undefined index: player on line 41

Notice: Undefined index: player on line 43

Notice: Undefined index: player on line 44

Notice: Undefined index: words on line 50

You probably want to use `foreach ( $data as $row)` instead of `while($row = $data)` — Nigel Ren, Jun 01 '19 at 18:35
Strangely, the last page you linked to explains this very situation. — Your Common Sense, Jun 01 '19 at 18:53
@YourCommonSense I was looking at that page for a good while, I just did not know the foreach method worked whereas the while loop was the problem. — nedsmith, Jun 01 '19 at 18:59

score 3 · Accepted Answer · answered Jun 01 '19 at 18:41

3

You have got two options. Either change the while loop to a foreach loop as @NigelRen suggested or use fetch method to fetch each record one by one from DB.

foreach ( $data as $row) {
    // ...
}
// or 
// Remove $data = $stmt->fetchAll();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    // ...
}

answered Jun 01 '19 at 18:41

Dharman

30,962
25
85
135

what does ```$data = $stmt->fetchAll()``` do to $stmt. ```//$data = $stmt->fetchAll(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC))``` works whilst ```$data = $stmt->fetchAll(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC))``` does not. – nedsmith Jun 01 '19 at 18:56
It fetches all the records matched in DB into a PHP array at once. [fetchAll](https://www.php.net/manual/en/pdostatement.fetchall.php). If you use `fetch` after it there are no more records to be read so it will not work. – Dharman Jun 01 '19 at 18:57

using prepared statements and fetchAll() to display to a table

1 Answers1