FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » Why does my PDO query with named parameters return a blank result?
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Why does my PDO query with named parameters return a blank result? [message #179317] Wed, 03 October 2012 13:56 Go to next message
tom.rankin51 is currently offline  tom.rankin51
Messages: 17
Registered: September 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
I am converting my MySQL code over to PDO to take advantage of prepared statements and parameters but problems have arisen when trying to add parameters.

The code I am trying to get working is:

include ("foo/bar.php");

try {
$DBH = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
}
catch(PDOException $e) {
echo $e->getMessage();
}

$mydate=date("Y-m-d",strtotime("-3 months"));

$foo_query=$DBH->prepare("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate AND postdate > '$mydate' ORDER BY postdate DESC");
$foo_query->execute( array('postdate' => $_REQUEST['postdate']) );

$DBH=null;

In English, this is meant to read "take the current date and set it back 3 months calling it $mydate, then select all of those fields (also taking the first 20 words of the body and calling it 'preview_text') from my table where the postdate is equal to the parameter postdate and where postdate is greater than $mydate".

I am then displaying the results in the following (note this code is abridged):

$foo_query->setFetchMode(PDO::FETCH_ASSOC);
while($r=$foo_query->fetch()) {
echo $r["id"];
echo $r["title"];
echo date("d-M-Y",strtotime($r["postdate"]));
echo nl2br ($r["preview_text"]); }

Now, while the SELECT query is written as:

("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate > '$mydate' ORDER BY postdate DESC")

....it displays exactly what I need it to, but of course while this is prepared, it contains no parameters so only achieves 50% of the goal.

I was under the impression the way of preparing the statement that I outlined initially would be fine as per the tutorials and advice I have already been given. I have also been advise that AND is useless in my initial query, but it was not explained why.

I have tried to print my error and it brings up no error. Manually sending the query displays correctly when typing the following, but that does not utilise $mydate:

("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = '2012-09-30 08:38:23' ORDER BY postdate DESC")

The query does not display at all when typing any of the following:

("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate ORDER BY postdate DESC")
("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate AND postdate > '$mydate' ORDER BY postdate DESC")
("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = '2012-09-30 08:38:23' AND postdate > '2012-07-01 00:01' ORDER BY postdate DESC")

I thought that I might need to bind the parameter in some way, but the tutorials I've read do not specifically say that I should always bind them. Either way, I attempted to bind the statement as follows with no success. The query seemingly ignores the bind so the state of the query acts as all of the above permutations without binding:

$foo_query->bindParam (
":postdate", strtotime ( $_REQUEST['postdate']), PDO::PARAM_INT);
$foo_query->execute();

What am I doing wrong, that isn't happening when I don't add parameters? Should I be declaring the :postdate parameter somewhere other than in the SELECT query?

For the purposes of preventing SQL injection I would like to use named parameters as well as preparing the statement, but if I can't figure it out then I will just have to not used named parameters.

Thanks in advance
Tom
Re: Why does my PDO query with named parameters return a blank result? [message #179318 is a reply to message #179317] Wed, 03 October 2012 14:45 Go to previous messageGo to next message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 10/3/2012 1:56 PM, tom(dot)rankin51(at)googlemail(dot)com wrote:
> I am converting my MySQL code over to PDO to take advantage of prepared statements and parameters but problems have arisen when trying to add parameters.
>
> The code I am trying to get working is:
>
> include ("foo/bar.php");
>
> try {
> $DBH = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
> }
> catch(PDOException $e) {
> echo $e->getMessage();
> }
>
> $mydate=date("Y-m-d",strtotime("-3 months"));
>
> $foo_query=$DBH->prepare("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate AND postdate > '$mydate' ORDER BY postdate DESC");
> $foo_query->execute( array('postdate' => $_REQUEST['postdate']) );
>
> $DBH=null;
>
> In English, this is meant to read "take the current date and set it back 3 months calling it $mydate, then select all of those fields (also taking the first 20 words of the body and calling it 'preview_text') from my table where the postdate is equal to the parameter postdate and where postdate is greater than $mydate".
>

No, you're selecting rows where postdate is equal to 3 months ago AND
postdate is greater than the passed date. If the passed date is later
than 3 months ago, the result is a null set (zero rows returned).

> I am then displaying the results in the following (note this code is abridged):
>
> $foo_query->setFetchMode(PDO::FETCH_ASSOC);
> while($r=$foo_query->fetch()) {
> echo $r["id"];
> echo $r["title"];
> echo date("d-M-Y",strtotime($r["postdate"]));
> echo nl2br ($r["preview_text"]); }
>
> Now, while the SELECT query is written as:
>
> ("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate > '$mydate' ORDER BY postdate DESC")
>

> ...it displays exactly what I need it to, but of course while this is prepared, it contains no parameters so only achieves 50% of the goal.
>

That's because you're not comparing to ensure postdate is equal to 3
months ago.

> I was under the impression the way of preparing the statement that I outlined initially would be fine as per the tutorials and advice I have already been given. I have also been advise that AND is useless in my initial query, but it was not explained why.
>
> I have tried to print my error and it brings up no error. Manually sending the query displays correctly when typing the following, but that does not utilise $mydate:
>

There's no error in your query, just zero rows returned as noted above.

> ("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = '2012-09-30 08:38:23' ORDER BY postdate DESC")
>
> The query does not display at all when typing any of the following:
>
> ("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate ORDER BY postdate DESC")
> ("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate AND postdate > '$mydate' ORDER BY postdate DESC")
> ("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = '2012-09-30 08:38:23' AND postdate > '2012-07-01 00:01' ORDER BY postdate DESC")
>

Again, check your WHERE conditions. For instance, do you have a row
with a postdate exactly equal to 2012-09-30 08:38:23?

Also note that since you're comparing for an exact match on postdate,
the ORDER BY postdate is meaningless (all returned rows will have the
same postdate).

> I thought that I might need to bind the parameter in some way, but the tutorials I've read do not specifically say that I should always bind them. Either way, I attempted to bind the statement as follows with no success. The query seemingly ignores the bind so the state of the query acts as all of the above permutations without binding:
>
> $foo_query->bindParam (
> ":postdate", strtotime ( $_REQUEST['postdate']), PDO::PARAM_INT);
> $foo_query->execute();
>
> What am I doing wrong, that isn't happening when I don't add parameters? Should I be declaring the :postdate parameter somewhere other than in the SELECT query?
>
> For the purposes of preventing SQL injection I would like to use named parameters as well as preparing the statement, but if I can't figure it out then I will just have to not used named parameters.
>
> Thanks in advance
> Tom
>

Check your SQL to ensure it's doing what you want it to do.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
Re: Why does my PDO query with named parameters return a blank result? [message #179319 is a reply to message #179317] Wed, 03 October 2012 15:13 Go to previous messageGo to next message
tom.rankin51 is currently offline  tom.rankin51
Messages: 17
Registered: September 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Apologies for the ORDER BY DESC further on in the code - I caught that and forgot to remove it.

I understand what I've done with the AND - it makes sense and explains things a lot. However...

....when I've been reading about PDO and named parameters, I always see examples typed as WHERE foo = :foo I struggle to see why it works in an example, and why it does not in one of my failed attempts? Do parameters have to be declared?

I've tried different permutations of the postdate and $mydate part, and I'm really not sure how I can make it say postdate > $mydate by using parameters. Is there an accepted way, or standard way of doing this?
Re: Why does my PDO query with named parameters return a blank result? [message #179320 is a reply to message #179319] Wed, 03 October 2012 17:26 Go to previous message
Michael Fesser is currently offline  Michael Fesser
Messages: 215
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
.oO(tom(dot)rankin51(at)googlemail(dot)com)

> ...when I've been reading about PDO and named parameters, I always see
> examples typed as WHERE foo = :foo I struggle to see why it works in an
> example, and why it does not in one of my failed attempts? Do
> parameters have to be declared?

JFTR: There are two ways of declaring parameters in a query. One is to
give them a name like in your example above, the other is to simply
write them as a question mark: "... WHERE foo = ? ..." But besides the
different naming, the way they work is the same.

Next steps are to prepare the query and then bind the actual variables
or values to these parameters, using either bindParam() or bindValue().

So far, so good. But you have a problem or misunderstanding when dealing
with the dates. Given your examples, the column 'postdate' seems to be
of type DATETIME, which is a string type. But the way you pass
$_REQUEST['postdate'] to the DB, it will go in as a Unix timestamp,
which is an integer - this will never match!

Your parameter binding should look like:

$foo_query->bindParam (
":postdate", $_REQUEST['postdate'], PDO::PARAM_STR);

This assumes that $_REQUEST['postdate'] contains a valid date/time in
the required format.

Or you could do the comparison as Unix timestamps, but then you would
have to change your query:

....
WHERE UNIX_TIMESTAMP(postdate) = :postdate
....

and keep the binding as it is. But I would do it the first way.

> I've tried different permutations of the postdate and $mydate part, and
> I'm really not sure how I can make it say postdate > $mydate by using
> parameters. Is there an accepted way, or standard way of doing this?

Does $mydate have to be a parameter? Is it expected to change?

But what you can do anyway is to do the calculation in SQL instead of
using strtotime():

....
WHERE postdate = :postdate
AND postdate > NOW() - INTERVAL 3 MONTH
....

Micha

--
http://mfesser.de/
Fotos | Blog | Flohmarkt
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Is Symfony 2 on-topic in this newsgroup?
Next Topic: How to run a batch file using PHP?
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Oct 18 23:42:36 EDT 2017

Total time taken to generate the page: 0.00626 seconds