Advertisement
PHP

The Problem with PHP's Prepared Statements

by

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.


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>
Related Posts
  • Code
    WordPress
    Mastering WordPress Meta Data: Querying Posts and Users by Meta DataMetadata
    So far in this series you've learned how to access WordPress meta data, and work with the arrays in which they are returned. We don't just add custom fields to WordPress posts so we can display this information, but it also so we can sort by it. Now that you know how to retrieve and display meta data, it's time to learn how to customize the WordPress loop to return only posts with specific meta values.Read More…
  • Code
    WordPress
    Mastering WordPress Meta Data: Working With LoopsMetadata
    In the first two parts of this series, we covered what meta data is in WordPress and how to work with the arrays that are typically returned. Now that you've learned to do the detective work necessary to find the structure of an array, it's time to learn to use loops to automate the process of outputting an array. Once you learn this important skill you will never have to write repetitive HTML markup again.Read More…
  • Code
    WordPress
    Mastering WordPress Meta Data: Understanding and Using ArraysMetadata
    In the first part of this series, we covered what WordPress meta data is, how it can be retrieved, and the various data types (such as objects or arrays) in which it can be returned. Now it's time to learn about the different types of arrays. When you write an array manually you know what its structure is an what the name of each index is. But when you are building arrays by querying a database, you are going to need to do some detective work to determine the structure of the data returned and the names of the indexes.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Features: Custom Queries with WP_QueryApplication foundation 400
    We've been looking at how WordPress can be used as a foundation for application development, but one of the things that we've yet to cover that most modern frameworks offer is how to query the database to retrieve results for any given view. Specifically, we haven't talked about how to get information out of the database and insert it into our pages.Read More…
  • Code
    PHP
    Creating a Photo Tag Wall With Twilio Picture Messaging & PHPProcedural to oop php retina preview
    Twilio's recently announced Picture Messaging has vastly opened up what we can do with text messaging, now we can attach photos to our text messages and have them get used in different ways. In our case, we are going to build a Photo Tag Wall, which will contain photos linked to tags that will be displayed on a website.Read More…
  • Web Design
    HTML/CSS
    A Simple Mixin Alternative to Standard CSS GridsMixin grid retina
    Over the past few years CSS grid systems have become a very popular way of rapidly producing layout scaffolding in web design. They can be a fantastic time-saving resource, with talented coders having intelligently crafted systems which cater to a wide array of possible layouts designers might need to create. However, this isn't the only way to approach grid-based web layouts, and today we're going to examine an alternative.Read More…