doutong7216
2013-11-14 21:52 阅读 314
已采纳

按时间和日期过滤

I am trying to filter results from a MySQL DB but not having much luck.

The user will complete a form, with a from and to date (EG FROM 01/11/2013 TO 14/11/2013)

Here is my code to filter the results:

$dbserver = "localhost";
$dbname = "nameofDB";
$dbusername = "username";
$dbpassword = "password";

$mysqli = new mysqli($dbserver, $dbusername, $dbpassword, $dbname);

$query = "SELECT * FROM transfer WHERE personID = 84587749 AND DATE(time) BETWEEN ? AND ?";

if($stmt = $mysqli->prepare($query)){

    /*
    Binds variables to prepared statement


    i    corresponding variable has type integer
    d    corresponding variable has type double
    s    corresponding variable has type string
    b    corresponding variable is a blob and will be sent in packets
    */

   $to = $_POST['to'];
   $from = $_POST['from'];

   $stmt->bind_param('ss', $from, $to);


   /* execute query */
   $stmt->execute();

   /* Get the result */
   $result = $stmt->get_result();

   while ($row = $result->fetch_assoc()) {
        // Configure this how you want to print out each row.
        echo 'Details: '.$row['details'].'<br>';
        echo 'Time: '.$row['time'].'<br>';
        echo 'Balance: '.$row['balance'].'<br>';
        echo '<br><br>';
   }

   /* free results */
   $stmt->free_result();

   /* close statement */
   $stmt->close();
}

/* close connection */
$mysqli->close();

This is showing no results, can anyone help?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dtl19910708 dtl19910708 2013-11-14 21:55

    What are the values you're getting from $_POST? Unless they correspond exactly to a mysql standard date/time string, e.g.

     yyyy-mm-dd
    

    Then you cannot directly stuff those $_POSt values into your query without appropriate translation logic.

    e.g.

    ... WHERE DATE(time) BETWEEN 'Jan 31' and 'Feb 28'
    

    is not a valid comparison, but

    ... WHERE DATE(TIME) BETWEEN '2013-01-31' and '2013-02-28' 
    

    IS valid.

    点赞 评论 复制链接分享

相关推荐