Jump to content

fetch all records from table with prepare statements


Balderick

Recommended Posts

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 by Balderick
Link to comment
Share on other sites

<?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();
...
  • Like 1
Link to comment
Share on other sites

@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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...