如何在PHP中为两个日期/时间之间的SELECT SELECT编写SELECT语句? [英] How to write a prepared statement in PHP for a SELECT BETWEEN mysql query between two Date/Times?

问题描述

我是新来的，最近才学到有关准备好的语句和PDO的知识.我已经能够为我的UPDATE和从表" WHERE"中的常规选择字段"创建准备好的语句.但是，在从表"中创建选择"字段时遇到了麻烦，其中字段"在之间"查询.我可以这样做:

I am new and have just recently learned about prepared statements and PDO. I have been able to create prepared statements for my UPDATE's and regular "SELECT 'field' from 'table' WHERE' statements. However, I have been having trouble creating a 'SELECT 'field' from 'table' where 'field' BETWEEN' query. I am able to do it this way:

 $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一起使用:

However, I am not able to get it to work with either prepared statements or 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();

}

因为获取"不会返回任何内容.

as the 'fetch' does not return anything.

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

我收到以下错误消息:

致命错误:带有消息的未捕获异常'PDOException' 'SQLSTATE [42000]:语法错误或访问冲突:1064您有一个 您的SQL语法错误；检查与您的手册相对应的手册 适用于'='附近的正确语法的MySQL服务器版本2014-10-01 00:00:00'AND end ='2014-11-30 00:00:00''在第1行 C:\ xampp_2 \ htdocs \ series \ dynamic \ query_date_results.php:54堆栈 跟踪:

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

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

在第54行的C:\ xampp_2 \ htdocs \ series \ dynamic \ query_date_results.php中抛出#1 {main}

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

任何建议都会对我有很大帮助.谢谢！

Any advice would help me greatly. Thanks you!

推荐答案

我能够使用@Prix的建议并将"ii"更改为"ss".我没有意识到日期/时间被存储为字符串.谢谢大家的帮助！

I was able to use @Prix 's suggestion and changed the 'ii' to 'ss'. I was unaware that the date/time was being stored as a string. Thank you all for your help!

这篇关于如何在PHP中为两个日期/时间之间的SELECT SELECT编写SELECT语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！