Selecting table data with PDO statements
Solution 1
You are doing too much actually:
$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $dbh->query($query);
The problematic line is:
$result = $dbh->query($query);
Check with http://php.net/pdo.query, the parameter is a string, actually the SQL string you already use above, not the result value of a PDO::prepare()
call.
For your simple query you can just do:
$result = $dbh->query("SELECT * FROM students");
Or if you like to prepare:
$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $query;
The later is some boilerplate if you want to insert variables into the query, that's why you prepare it.
The next problem is with the foreach
line:
foreach($result as $row);
You are terminating the loop immediately because of the semicolon ;
at the end. Remove that semicolon so that the following angle-bracketed code-block becomes the body of the foreach-loop.
Solution 2
Your code is wrong:
$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $dbh->query($query);
After executing a prepared statement, you can just call fetchAll()
on it:
$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $query->fetchAll();
The rest of your code will work fine once you remove the semicolon after the foreach.
RaGe10940
Updated on May 18, 2020Comments
-
RaGe10940 almost 4 years
I have a php script that selects data via mysql_, however recently I have been reading that PDO is the way to go and that mysql_ is becoming depreciated. Now I am converting that script to PDO.
My question is though, I am not using $_POST to select. I just want to select the entire table with all of its data so I enter this query :
$query = $dbh->prepare("SELECT * FROM students"); $query->execute(); $result = $query->fetchall(); // or you can just $result = $query as hakre proposed!
so then like I did with my old depreciated mysql_ version of the script I used the echo to echo a table with the data in it.
echo "<table border='2'> <tr> <th>ID</th> <th>A Number</th> <th>First Name</th> <th>Last Name</th> <th>Why</th> <th>Comments</th> <th>Signintime</th> </tr>" ; foreach($result as $row) { echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td><a href=Student.php?studentA_num=" . $row['anum'] . ">" .$row['anum'] . " </a></td>"; echo "<td>" . $row['first'] . "</td>"; echo "<td>" . $row['last'] . "</td>"; echo "<td>" . $row['why'] . "</td>"; echo "<td>" . $row['comments'] . "</td>"; echo "<td>" . $row['signintime'] . "</td>"; echo "<td> <input type=\"button\" value=\"Start Session\"onClick=\accept.php?id=" . $row['id'] . "&start=true></td>"; } echo "</tr>"; echo "</table>";
now using this, I can not get a single output to my table.
My question is am I missing something from my select statements? Or am I not fetching any rows? Also I the connection settings set in another script called connect.php that is required by init.php (at the top of all of my pages)
Edit : 1
Edited the code so it now works, also adding a picture to show others how it should look! Hopefully some one can put this to some sort of use!
-
RaGe10940 over 11 yearssuch a foolish mistake on my part! Thank you to the both of you guys!
-
hakre over 11 yearsYou are new. In case you run into a problem, start to verify from top to bottom. You can look into variables with
var_dump()
, often helpful. Also set your error reporting for development to on and to the highest level.