How to write a prepared statement in PHP for a SELECT BETWEEN mysql query between two Date/Times?

Question

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

However, I am not able to get it to work with either prepared statements or PDO:

prepared statement:

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

I get the following error message:

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

Any advice would help me greatly. Thanks you!

Answer 1

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!

Answer 2

SELECT * FROM sample WHERE start_samp_date_time 
                   BETWEEN :start AND :end

They must be numeric, if in the DB they are numeric. If you want to pass them to an Unix date (in seconds), you can use strtotime.

Answer 3

When using mysqli prepared statement, looks like you are not storing the fetched result in a variable, check

while($column = $stmt1->fetch())

And the PDO statements should be something like this

$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->execute(array('start' => $p_smydate,'end' => $p_emydate);
print_r($sth);