Solved

PHP PDO Prepared Statement not working, please help!

Posted on 2014-01-09
16
375 Views
Last Modified: 2014-01-09
Here is my sample code:

                $categoryID = 1;
                $productCode = 's%';
                
                $db = new PDO('mysql:host=localhost;dbname=my_database', 
                                  'username', 'password');
                    
                    $stmt = $db->prepare("SELECT * FROM products WHERE categoryID = :categoryID, AND productCode like :productCode");
                    $stmt->bindParam(':categoryID', $categoryID, PDO::PARAM_INT);
                    $stmt->bindParam(':productCode', $productCode, PDO::PARAM_STR);

                    $stmt->execute();
                    
                    $results = $stmt->fetchAll();
echo '-->Number of Records: '.count($results).'<p />';
                    
                    $stmt->closeCursor();

Open in new window

I know for a fact the result set returned should be 2 records, but somehow I keep getting zero records. Can anyone look at my code and tell me what I'm doing wrong?

Thanks in advance.
0
Comment
Question by:elepil
  • 7
  • 5
  • 3
16 Comments
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Maybe it should be 2 records but are you sure you are getting 2 records?
What do you get when you var_dump($results)
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
Comment Utility
Please add error_reporting(E_ALL) to the top of the script.

There are some PDO examples in this article that may be helpful to you.  PDO throws exceptions so it's appropriate to wrap the calls in try / catch blocks.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 

Author Comment

by:elepil
Comment Utility
To cathal.

var_dump($result) says:

array
    empty

When I'm in MySQL administrator and type in "SELECT * FROM products
WHERE categoryID = 1 AND productCode like 's%'
ORDER BY productID;", I do get 2 records.

Before I did this example, I was successful in getting 2 records with:

"SELECT * FROM products
                              WHERE categoryID = $categoryID
                              AND productCode like '$productCode'"

I also get 2 records. I cannot figure out what PHP is not liking in my code when I use Prepared Statements!
0
 

Author Comment

by:elepil
Comment Utility
To Ray Paseur.

Adding error_reporting(E_ALL) at the top of the script resulted in no distinguishable difference.

The sample code I attached was originally in a try/catch construct, I took it out to make it look less cluttered. I wasn't getting any PDOException errors.

So you see nothing wrong with my syntax?
0
 
LVL 58

Accepted Solution

by:
Gary earned 350 total points
Comment Utility
$stmt = $db->prepare("SELECT * FROM products WHERE categoryID = :categoryID, AND productCode like :productCode");

should be

$stmt = $db->prepare("SELECT * FROM products WHERE categoryID = :categoryID AND productCode like :productCode");

You had a comma in there
0
 

Author Comment

by:elepil
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for elepil's comment #a39769590
Assisted answer: 250 points for GaryC123's comment #a39769568
Assisted answer: 250 points for Ray_Paseur's comment #a39769575

for the following reason:

I found the problem!

It was my SQL statement:

 
                    $stmt = $db->prepare("SELECT * FROM products WHERE categoryID = :categoryID, AND productCode like :productCode");

Notice there is a comma after :categoryID, just before the AND? Hehe, you'd think MySQL should've flagged an error and caused PDO to throw an exception, but it didn't.

Thanks for the help guys.
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Isn't that what I said just above.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:elepil
Comment Utility
Yes, cathal, I finally noticed that. Odd why MySQL accepted it without generating an error, it's syntactically incorrect. As a result, PDO didn't throw an exception. I closed this ticket already, giving you and the other respondent 250 points each. Thanks for helping.
0
 

Author Comment

by:elepil
Comment Utility
Cathal,

Yes, I posted my findings, and seconds later, your post came through with you finding the issue as well.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Let's try it with something like this.  If this doesn't get sensible output, please post the CREATE TABLE statement and a few lines of representative data.  I'll set up a test on my server.
error_reporting(E_ALL);
$categoryID = 1;
$productCode = 's%';

try
{
    $db = new PDO('mysql:host=localhost;dbname=my_database', 'username', 'password');
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error('NO PDO Connection', E_USER_ERROR);
}

$sql = "SELECT * FROM products WHERE categoryID = :categoryID, AND productCode LIKE :productCode";
$stmt = $db->prepare($sql);
$stmt->bindParam(':categoryID',  $categoryID,  PDO::PARAM_INT);
$stmt->bindParam(':productCode', $productCode, PDO::PARAM_STR);

try
{
    $stmt->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

// VISUALIZE THE STATEMENT OBJECT
var_dump($stmt);

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $stmt->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}

// RETRIEVE ROWS
while ($row = $stmt->fetchAll(PDO::FETCH_OBJ))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    foreach ($row as $key => $obj)
    {
        echo "$key: ";
        print_r($obj);
        echo PHP_EOL;
    }
}

Open in new window

Best, ~Ray
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I believe that the query should have failed with that extraneous comma.  That's why we use the try / catch syntax -- so we can visualize any errors.
0
 

Author Comment

by:elepil
Comment Utility
Ray Paseur,

I think the same way you do, that the query should have failed. But my code was originally within a try/catch block, and no exceptions were generated, causing my confusion.

One last question though. In the real job market, do people use Prepared Statements a lot? PHP is unique in that using a single quote or double quote matters, and that the latter actually allows you to enter variable names within the string.

Thanks.
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Yes, they offer better security and enhanced performance
It eliminates the sql injection threat as the data is separate from the sql
You could use the same sql template thousands of times in the same page but you only need to tell the database what is once and then just keep passing the actual data.
As the old MySQL_query is eliminated, in PHP at least, prepared statements should become more widespread.
0
 

Author Comment

by:elepil
Comment Utility
By sql injection, do you mean hackers substituting the application's sql string for something pernicious?

I came from Java, and I always had to use Prepared Statements. But PHP is different in that you can use the variable names themselves if you enclose your SQL statement in double quotes.

From what you know of the current PHP job market though, do most PHP developers use Prepared Statements or not?
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Yes

You can still do the inserted values, but then you are defeating the purpose of PDO

Ray would probably have a better idea of its usage than I would.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses how to create an extensible mechanism for linked drop downs.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now