fetch all records from table with prepare statements

[Balderick 0]

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 work

I 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

[davej 251]

<?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();
...

[Balderick 0]

@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?

[dsonesuk 913]

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.

[Balderick 0]

Ok thanks a lot for your help and the valuable query from davej.

This really solved the problem.