PHP PDO Prepared Statement not working, please help!

elepil
elepil used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Year 2014
Top Expert 2014

Commented:
Maybe it should be 2 records but are you sure you are getting 2 records?
What do you get when you var_dump($results)
Most Valuable Expert 2011
Top Expert 2016
Commented:
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

Author

Commented:
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!
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
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?
Expert of the Year 2014
Top Expert 2014
Commented:
$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

Author

Commented:
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.
Expert of the Year 2014
Top Expert 2014

Commented:
Isn't that what I said just above.

Author

Commented:
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.

Author

Commented:
Cathal,

Yes, I posted my findings, and seconds later, your post came through with you finding the issue as well.
Most Valuable Expert 2011
Top Expert 2016

Commented:
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
Most Valuable Expert 2011
Top Expert 2016

Commented:
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.

Author

Commented:
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.
Expert of the Year 2014
Top Expert 2014

Commented:
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.

Author

Commented:
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?
Expert of the Year 2014
Top Expert 2014

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial