The Problem with PHP's Prepared Statements

PHP's prepared statements (for database access) are fantastic. Not only do they help secure your database queries, but they're also particularly more efficient for larger products. However, there are a couple issues that appear to make these methods less flexible than we'd hope. For one, we must utilize the bind_result method, and pass in a specific number of variables. However, what happens when this code is within a class, and we won't immediately know how many variables to pass? Luckily, there's a solution! I'll show you what it is in today's video tutorial.

Premium Members: Download this Video ( Must be logged in)
Subscribe to our YouTube page to watch all of the video tutorials!

Final Code


<?php

function read()
{
   $parameters = array();
   $results = array();

   $mysql = new mysqli('localhost', 'root', 'root', 'db') or die('There was a problem connecting to the database');
   $stmt = $mysql->prepare('SELECT body FROM posts') or die('Problem preparing query');
   $stmt->execute();

   $meta = $stmt->result_metadata();

   while ( $field = $meta->fetch_field() ) {

     $parameters[] = &$row[$field->name]; 
   }

   call_user_func_array(array($stmt, 'bind_result'), $parameters);

   while ( $stmt->fetch() ) {
      $x = array();
      foreach( $row as $key => $val ) {
         $x[$key] = $val;
      }
      $results[] = $x;
   }

   return $results;


}

$results = read();
?>
<!DOCTYPE html>

<html lang="en">
<head>
   <meta charset="utf-8">
   <title>untitled</title>
</head>
<body>
<?php foreach ($results as $row) : ?>

   <p> <?php echo $row['body']; ?> </p>
<?php endforeach; ?>
</body>
</html>

1
2	<?php
3
4	function read()
5	{
6	$parameters = array();
7	$results = array();
8
9	$mysql = new mysqli('localhost', 'root', 'root', 'db') or die('There was a problem connecting to the database');
10	$stmt = $mysql->prepare('SELECT body FROM posts') or die('Problem preparing query');
11	$stmt->execute();
12
13	$meta = $stmt->result_metadata();
14
15	while ( $field = $meta->fetch_field() ) {
16
17	$parameters[] = &$row[$field->name];
18	}
19
20	call_user_func_array(array($stmt, 'bind_result'), $parameters);
21
22	while ( $stmt->fetch() ) {
23	$x = array();
24	foreach( $row as $key => $val ) {
25	$x[$key] = $val;
26	}
27	$results[] = $x;
28	}
29
30	return $results;
31
32
33	}
34
35	$results = read();
36	?>
37	<!DOCTYPE html>
38
39	<html lang="en">
40	<head>
41	<meta charset="utf-8">
42	<title>untitled</title>
43	</head>
44	<body>
45	<?php foreach ($results as $row) : ?>
46
47	<p> <?php echo $row['body']; ?> </p>
48	<?php endforeach; ?>
49	</body>
50	</html>