Jump to content

fetch all records from table with prepare statements


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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...