php - 如何在 PHP 中为两个日期/时间之间的 SELECT BETWEEN mysql 查询编写准备好的语句？

Question

我是新手，最近刚刚了解准备好的语句和 PDO。我已经能够为我的 UPDATE 和常规的“SELECT 'field' from 'table' WHERE' 语句创建准备好的语句。但是，我一直无法从 'table' where 'field' BETWEEN' 创建 'SELECT 'field'查询。我可以这样做:

 $sql = "SELECT * FROM sample WHERE start_samp_date_time BETWEEN '2014-10-01 00:00:00' AND '2014-11-30 00:00:00'";
 $result = mysqli_query($dbc,"$sql");

if ($result->num_rows > 0) {
    // output data of each row

    $header_ct = 0;
    while($row = mysqli_fetch_assoc($result)){
        #print_r($row);
    }
}

但是，我无法让它与准备好的语句或 PDO 一起工作:

准备好的语句:

$stmt1 = $dbc->prepare("SELECT * FROM sample WHERE start_samp_date_time 
                        BETWEEN (?) AND (?)");
$stmt1 -> bind_param('ii', $p_smydate , $p_emydate);


if ($stmt1->execute()){

    $metaResults = $stmt1->result_metadata();
    $fields = $metaResults->fetch_fields();
    $statementParams='';
    //build the bind_results statement dynamically so I can get the results in an array
    foreach($fields as $field){
        if(empty($statementParams)){
            $statementParams.="\$column['".$field->name."']";
        }
        else{

            $statementParams.=", \$column['".$field->name."']";
        }

    }
    $statment="\$stmt1->bind_result($statementParams);";
    #echo "statement:".$statementParams."<br>";
    eval($statment);
    #print_r($fields);


    ?><table>
        <tr><?php


    #print headers
   foreach ($fields as $keys => $val){
            $name = $val->name; 
            ?><th class = "reg"><?php echo "{$name}"; ?></th> <?php
    } 
        ?></tr>
        <tr><?php

    #print out mysql query results
    while($stmt1->fetch()){
        foreach ($column as $keys => $val){
            echo "keys:".$keys.'<br>';
            echo "vals:".$val.'<br>';

            ?><td class = "reg"><?php echo "{$val}"; ?></td><?php 

        }
        #echo "column".$column['sample_name'];


    ?> </tr><?php
    }

    ?></table><?php 

} 
else {
    $error = 'true';
    die('execute() failed: ' . htmlspecialchars($stmt->error));

}
#echo 'done';
$stmt1 -> close();

因为“获取”不返回任何内容。

PDO:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $conn->prepare("SELECT * FROM sample WHERE start_samp_date_time 
                       BETWEEN start = :start AND end = :end");
$sth->bindParam(':start',$p_smydate);
$sth->bindParam(':end',$p_emydate);
$sth->execute();

print_r($sth);

我收到以下错误消息:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '2014-10-01 00:00:00' AND end = '2014-11-30 00:00:00'' at line 1' in C:\xampp_2\htdocs\series\dynamic\query_date_results.php:54 Stack trace:

#0 C:\xampp_2\htdocs\series\dynamic\query_date_results.php(54): PDOStatement->execute()

#1 {main} thrown in C:\xampp_2\htdocs\series\dynamic\query_date_results.php on line 54

任何建议都会对我有很大帮助。谢谢!

Answer 1

我能够使用@Prix 的建议并将“ii”更改为“ss”。我不知道日期/时间被存储为一个字符串。谢谢大家的帮助!