How to properly fetch object using PDO and php

Question

I have implemented a search box in my website, which worked perfectly until i decided to protect against sql injection. I decided to go with PDO as many people have suggested that its better than using mysql_string_escape. when i try to fetch data from my database it returns nothing, but i know there are records in there.

My code

db.php

    $mysqli = new mysqli("localhost","root", "", "1000_AD");

result.php

         <?php

       if (isset($_GET['search'])){

      $search_query = $_GET['user_query'];

      $get_foo = $mysqli->prepare("SELECT * FROM `food` WHERE `food_keywords` 
      LIKE ?");

       $search_query = "%".$search_query."%";

       $get_foo->bind_param('s',$search_query);


    $get_foo->execute();

    $obj = $get_foo->fetch();

    if($obj==0){

     echo "<h4>No results where found!</h4>";

      }

      //fetch results set as object and output HTML
      while($list = $get_foo->fetch())
     {
       $foo_id = $list['food_id'];

       echo '<div class="food">'; 
        echo '<form method="post" action="basket.php" id = "add-basket-form" 
         >';
        echo '<h4>'.$list['food_title'].'</h4>';
  echo '<div class="pic"><img src="admin/food_images/'.$list['food_image'].'" 
      width= "180" height= "160"></div>';
     echo "<p><b>£".$list['food_price']."</b></p>";
      echo '<div class="sp-quantity">
     <div class="sp-minus fff"> <a class="ddd">-</a> 
    </div><div class="sp-input"> <input type="text" name= "product_qty" class 
      ="quntity-input" value="1" /></div>
      <div class="sp-plus fff"> <a class="ddd">+</a> </div></div>';
    echo '<div class = "btn"><a href="info.php?foo_id='.$list['food_id'].'" 
          style="float:left">INFO</a></div>';
     echo '<div class = "add_b"><button>Add to Basket</button></div>';
        echo '<input type="hidden" name="food_code"   
         value="'.$list['food_id'].'" />';
        echo '<input type="hidden" name="type" value="add" />';
        echo '</form>';
        echo '</div>';
        }
             }

        ?>

it successfully gets the number of rows, because it echo's the buttons twice to show two records where found, which is correct. The problem is..it doesn't fetch the title,image or price from the database. i've tried many suggestions such as

           while($list = $get_foo->fetchAll(PDO::FETCH_ASSOC))

i get the error undefined method fetch all, i discovered that you need to install a driver for fetchAll to work. which i dont want to do. How can i correctly fetch data from my database?

@D4V1D ok , this is my first attempt at this. How do i correctly use pdo? — , Mar 29 '15 at 13:12
And `fetchAll` wouldn't require "to install a driver". It doesn't make sense in a `while ($list = ...)` condition though. Fetching row by row is often sufficient, and `foreach ($result as $list)` would be simpler (if you were using PDO). Also use a HEREDOC in place of the tedious `echo ..` string patching. — mario, Mar 29 '15 at 13:12
Here's a good tutorial for using PDO http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059 — priyank, Mar 29 '15 at 13:20

score 0 · Answer 1 · edited May 23 '17 at 11:43

In this example you are not connecting to the database using PDO and therefore the data you're trying to receive back in your query is invalid

 while($list = $get_foo->fetchAll(PDO::FETCH_ASSOC))

The best place to learn how to connect, retrieve and insert data using PDO (along with preparing statements which is the feature to prevent SQL injection) is:

How can I properly use a PDO object for a parameterized SELECT query

http://php.net/manual/en/book.pdo.php

score 0 · Accepted Answer · answered Mar 29 '15 at 16:11

As you guys said, i wasn't creating a PDO connection instead i was using mysqli. Sorry for the mistake, i've created the PDO connection successfully and rewrittten the code. it's working fine now

Solution:

db.php

          <?php
          $host   = 'localhost';
          $dbname = 'name';
          $username = "root";
          $password = "";
       $conn = new PDO('mysql:host=localhost;dbname=name', $username, 
       $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        ?>

and then replaced the following lines:

result.php

       if (isset($_GET['search'])){

       $search_query = $_GET['user_query'];

      $stmt = $conn->prepare("SELECT * FROM `food` 
       WHERE `food_keywords` LIKE :search ");

       $search_query = "%".$search_query."%";

      $stmt->bindParam(':search',$search_query, PDO::PARAM_STR);


       $stmt->execute();


    //fetch results set as object and output HTML
    while($obj = $stmt->fetch())
    {
        //rest goes here

      }
          }
             ?>

How to properly fetch object using PDO and php

2 Answers2