Solved

PHP PDO Prepared Statement not working, please help!

Posted on 2014-01-09
16
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
16 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39769568
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 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
ID: 39769575
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
ID: 39769590
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:elepil
ID: 39769599
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
ID: 39769606
$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
ID: 39769623
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
ID: 39769613
Isn't that what I said just above.
0
 

Author Comment

by:elepil
ID: 39769614
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
ID: 39769619
Cathal,

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

Expert Comment

by:Ray Paseur
ID: 39769633
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 110

Expert Comment

by:Ray Paseur
ID: 39769653
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
ID: 39769679
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
ID: 39769699
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
ID: 39769703
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
ID: 39769808
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
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 …

732 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