PHP PDO Prepared Statement not working, please help!

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.
elepilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
GaryCommented:
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
 
Ray PaseurCommented:
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
 
elepilAuthor 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!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
elepilAuthor 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?
0
 
GaryCommented:
$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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
elepilAuthor 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.
0
 
GaryCommented:
Isn't that what I said just above.
0
 
elepilAuthor 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.
0
 
elepilAuthor Commented:
Cathal,

Yes, I posted my findings, and seconds later, your post came through with you finding the issue as well.
0
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
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
 
elepilAuthor 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.
0
 
GaryCommented:
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
 
elepilAuthor 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?
0
 
GaryCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.