为什么带有命名参数的查询返回空白结果? [英] Why does my query with named parameters return a blank result?

问题描述

我正在将我的MySQL代码转换为PDO，以利用准备好的语句.我最初遇到的是致命错误，如问题.我已经解决了该问题，但是在尝试添加参数时出现了更多问题.

I am converting my MySQL code over to PDO to take advantage of prepared statements. I was originally getting a fatal error as described in this question. I had solved that issue, but it brought up more problems 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;

在英语中，这表示要读"以当前日期并将其设置为3个月，将其命名为$ mydate，然后选择所有这些字段(也输入正文的前20个单词，并将其称为'我的表格中的Preview_text')，其中postdate等于参数postdate，并且postdate大于$ mydate .

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 is now 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"]); }

现在，当SELECT查询写为:

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")

...它完全显示了我需要的内容，但是当然，在准备过程中，它不包含任何参数，因此只能达到目标的50％.

...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 tried to print my error and it brings up no error. I manually sent the query as follows and get no returns. Note I have tried the date strings in all manner of permutations:

("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = 20121001 AND postdate > 20120701 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' 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 am thinking that I should bind the parameter in some way, but the tutorials I've read do not specifically say that I should always bind them - I'm unsure of how to proceed.

我试图将语句绑定如下，但没有成功.该查询似乎忽略了绑定，因此查询状态充当以上所有未绑定的排列:

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

我在做什么错，当我不添加参数时不会发生这种情况?我应该在SELECT查询之外的其他地方声明:postdate参数吗?

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?

出于防止SQL注入的目的，我想使用命名参数以及准备语句，但是如果我无法弄清楚，那么我将不得不不使用命名参数.

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.

推荐答案

通过数组使用参数时，将使用默认参数类型PDO::PARAM_STR

When using parameter through a array, you are binding with the default parameter type PDO::PARAM_STR

取决于数据类型:

• MySQL时间戳数据类型:相同，您将其作为字符串传递
• PHP Unix时间戳，它是一个整数:您将为它传递一个整数.

我建议您这样更改代码:

I suggest you change your code like this:

$foo_query=$DBH->prepare(
   "SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body 
    FROM BarTable WHERE postdate = :postdate 
    ORDER BY postdate DESC");
$foo_query->bindParam (
   ":postdate", strtotime ( $_REQUEST['postdate']), PDO::PARAM_INT); 
//                                                  ^^^ bind as integer
$foo_query->execute(); 

这篇关于为什么带有命名参数的查询返回空白结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！