Balderick Posted August 28, 2016 Share Posted August 28, 2016 (edited) I have the following peace of code.I cant solve how to fetch all the records in the database.I have to use prepare but if I use place holders for $sql then it doesnt workI tried foreach and while but I dont know how to use it.Can someone please tell what code to use to fetch multiple records from a table? <?php // make connection to database $sql = "SELECT id_nr, name FROM table;"; $id_nr =1; if ($stmt = $conn->prepare($sql)) { $stmt->bind_param('i', $id_nr); $stmt->execute(); $stmt->store_result(); $num_of_rows = $stmt->num_rows; $stmt->bind_result($id_nr, $name); while ($stmt->fetch()) { echo ' id number : ' . $id_nr . '<br>'; echo ' name : ' . $name . '<br>'; } $stmt->free_result(); } $stmt->close(); mysqli_close($conn); } ?> Edited August 28, 2016 by Balderick Link to comment Share on other sites More sharing options...
davej Posted August 28, 2016 Share Posted August 28, 2016 <?php // make connection to database // (not shown) $sql = "SELECT id_nr, name FROM mytable;"; if ($stmt = $conn->prepare($sql)) { //$stmt->bind_param('i', $id_nr); // nothing to bind $stmt->execute(); $stmt->store_result(); $num_of_rows = $stmt->num_rows; $stmt->bind_result($id_nr, $name); echo '<table style="border:1px solid #555">'; echo '<tr><th>id</th><th>name</th></tr>'; while ($stmt->fetch()) { echo '<tr><td>'. $id_nr . '</td><td>' . $name . '</td></tr>'; } $stmt->free_result(); echo '</table>'; $stmt->close(); mysqli_close($conn); } ?> ...if you needed to bind a variable... $sql = "SELECT id_nr, name FROM mytable WHERE id_nr > ? ;"; $id_nr = 250; if ($stmt = $conn->prepare($sql)) { $stmt->bind_param('i', $id_nr); // 'i' = int $stmt->execute(); ... 1 Link to comment Share on other sites More sharing options...
Balderick Posted August 28, 2016 Author Share Posted August 28, 2016 @davej thanks for solving, this it helped a lot. I started the script because I thought I NEEDED $sql query with placeholders, which must make it safer. like this: $sql = "SELECT id_nr, name FROM WHERE ? = id_nr;"; I use prepare statements to prevent from mysql injection, but can you tell if using palceholders is more secure then without? Link to comment Share on other sites More sharing options...
dsonesuk Posted August 28, 2016 Share Posted August 28, 2016 If you are using a variable as part of where clause, then yes! Using placeholders would be more secure, your first example receives no such variable, so no possible SQL injection can take place. Link to comment Share on other sites More sharing options...
Balderick Posted August 29, 2016 Author Share Posted August 29, 2016 Ok thanks a lot for your help and the valuable query from davej. This really solved the problem. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now