Link to home
Start Free TrialLog in
Avatar of elepil
elepil

asked on

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.
Avatar of Gary
Gary
Flag of Ireland image

Maybe it should be 2 records but are you sure you are getting 2 records?
What do you get when you var_dump($results)
SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of elepil
elepil

ASKER

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!
Avatar of elepil

ASKER

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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of elepil

ASKER

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.
Isn't that what I said just above.
Avatar of elepil

ASKER

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.
Avatar of elepil

ASKER

Cathal,

Yes, I posted my findings, and seconds later, your post came through with you finding the issue as well.
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
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.
Avatar of elepil

ASKER

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.
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.
Avatar of elepil

ASKER

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?
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.