如何使用SQL找出兩個日期範圍內的資料

php7 php mysql 資料管理 php

stevenhappy99 2019-05-18 21:33:25 ‧ 2465 瀏覽

分享至

請問我有一個的資料表內有訂購時間欄位，現在想找出在這個訂購時間欄位裡兩個日期範圍內有包含今天的資訊，要如何下SQL 指令？我的訂購時間欄位時間戳記是年月日時分秒

Richie iT邦好手 10 級 ‧ 2019-05-19 00:05:55 檢舉

「訂購時間欄位裡兩個日期範圍內有包含今天的資訊」
不太理解

stevenhappy99 iT邦新手 5 級 ‧ 2019-05-19 11:16:48 檢舉

就是日期欄位裡的資料輸入兩個日期顯示這兩個範圍內的資料

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

dragonH

iT邦超人 5 級 ‧ 2019-05-19 12:15:03

最佳解答

我不熟php

但是你這有幾個問題

1.後端沒接前端post的值
2.你的欄位是 ordertime 但你的sql卻是查 column_time
還有那個.看起來是多餘的

test.php

<?php   
 $dbhost = 'localhost';   
 $dbuser = 'root';   
 $dbpass = '00000000';   
 $dbname = 'goodsapp';   
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL connection');
  
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);
  $ordertime = '';
  $ordertime2 = '';
  if (isset($_POST['ordertime'])) {
    $ordertime = $_POST['ordertime'];
  }
  if (isset($_POST['ordertime2'])) {
    $ordertime2 = $_POST['ordertime2'];
  }
  if ($ordertime !== '' AND $ordertime2 !== '') {
    $sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2'";    
  } else {
    $sql = "SELECT * FROM orders";    
  }

  $result = mysql_query($sql) or die('MySQL query error');
  echo "<table border = '1'>";
  echo "<tr>";
  echo "<th>ordertime</th>";
  echo "<th>totalmoney</th>";
  echo "</tr>"; 
  while($row = mysql_fetch_array($result))
  {
    echo "<tr>";
    echo "<td>".$row['ordertime']."</td>";
    echo "<td>".$row['totalmoney']."</td>";
    echo "</tr>";
  }
  echo "</table>";
?>

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

stevenhappy99 iT邦新手 5 級 ‧ 2019-05-19 14:04:05 檢舉

查詢日期可以了
如果我加入查詢總金額是這樣改嗎
我剛可以查總金額的資料，但是如果都不存在的總金額顯示是表格而已

<?php   
 $dbhost = 'localhost';   
 $dbuser = 'root';   
 $dbpass = '00000000';   
 $dbname = 'goodsapp';   
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL connection');
  
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);
  $ordertime = '';
  $ordertime2 = '';
  $totalmoney = '';
  if (isset($_POST['ordertime'])) {
    $ordertime = $_POST['ordertime'];
  }
  if (isset($_POST['ordertime2'])) {
    $ordertime2 = $_POST['ordertime2'];
  }
  if (isset($_POST['totalmoney'])) {
    $totalmoney = $_POST['totalmoney'];
  }
  if ($ordertime !== '' AND $ordertime2 !== '') {
    $sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2'";    
  } else {
    $sql = "SELECT * FROM orders";    
  }
  if ($totalmoney !== '' ) {
    $sql = "SELECT * FROM orders WHERE totalmoney = '$totalmoney'";    
  } else {
    $sql = "SELECT * FROM orders";    
  }
  

  $result = mysql_query($sql) or die('MySQL query error');
  echo "<table border = '1'>";
  echo "<tr>";
  echo "<th>訂單編號</th>";
  echo "<th>姓名</th>";
  echo "<th>訂購時間</th>";
  echo "<th>總額</th>";
  echo "<th>處理時間</th>";
  echo "</tr>"; 
  while($row = mysql_fetch_array($result))
  {
    echo "<tr>";
	echo "<td>".$row['orderid']."</td>";
	echo "<td>".$row['name']."</td>";
    echo "<td>".$row['ordertime']."</td>";
    echo "<td>".$row['totalmoney']."</td>";
	echo "<td>".$row['chargetime']."</td>";
    echo "</tr>";
  }
  echo "</table>";
?>

dragonH iT邦超人 5 級 ‧ 2019-05-19 14:43:01 檢舉

stevenhappy99對

如果你不喜歡沒資料還秀出表格的話

可以參考這種方式

  if (mysql_num_rows($result)) {
    echo "<table border = '1'>";
    echo "<tr>";
    echo "<th>ordertime</th>";
    echo "<th>totalmoney</th>";
    echo "</tr>";
    while($row = mysql_fetch_array($result))
    {
      echo "<tr>";
      echo "<td>".$row['ordertime']."</td>";
      echo "<td>".$row['totalmoney']."</td>";
      echo "</tr>";
    }
    echo "</table>";
  } else {
    echo "查無資料";
  }

stevenhappy99 iT邦新手 5 級 ‧ 2019-05-19 16:02:45 檢舉

我剛剛那個程式好像加總金額欄位totalmoney就不行查日期了，查日期範圍會顯示全部日期資料，我回復到可以查日期範圍的程式碼再請教怎修改成可以額外查詢總金額資料

test.php

<?php   
	header("Content-Type: text/html; charset=utf-8");
	include("connsql.php");  //連結資料庫檔案
	$seldb = @mysql_select_db("goodsapp");  //連結資料庫
	if (!$seldb) die("資料庫選擇失敗！");
  
  mysql_query("SET NAMES 'utf8'");
  $ordertime = '';
  $ordertime2 = '';
  if (isset($_POST['ordertime'])) {
    $ordertime = $_POST['ordertime'];
  }
  if (isset($_POST['ordertime2'])) {
    $ordertime2 = $_POST['ordertime2'];
  }
  if ($ordertime !== '' AND $ordertime2 !== '') {
    $sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2'";    
  } else {
    $sql = "SELECT * FROM orders";    
  }

  $result = mysql_query($sql) or die('MySQL query error');
  echo "<table border = '1'>";
  echo "<tr>";
  echo "<th>訂單編號</th>";
   echo "<th>姓名</th>";
  echo "<th>訂購時間</th>";
  echo "<th>總額</th>";
  echo "<th>處理時間</th>";
  echo "</tr>"; 
  while($row = mysql_fetch_array($result))
  {
    echo "<tr>";
	echo "<td>".$row['orderid']."</td>";
	echo "<td>".$row['name']."</td>";
    echo "<td>".$row['ordertime']."</td>";
    echo "<td>".$row['totalmoney']."</td>";
	echo "<td>".$row['chargetime']."</td>";
    echo "</tr>";
  }
  echo "</table>";
?>

dragonH iT邦超人 5 級 ‧ 2019-05-19 16:44:40 檢舉

初學的話

就囉嗦一點

根據每個值來決定sql查詢語句

<?php   
 $dbhost = 'localhost';   
 $dbuser = 'root';   
 $dbpass = '';   
 $dbname = 'goodsapp';   
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL connection');
  
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);
  $ordertime = '';
  $ordertime2 = '';
  $totalmoney = '';
  if (isset($_POST['ordertime'])) {
    $ordertime = $_POST['ordertime'];
  }
  if (isset($_POST['ordertime2'])) {
    $ordertime2 = $_POST['ordertime2'];
  }
  if (isset($_POST['totalmoney'])) {
    $totalmoney = $_POST['totalmoney'];
  }
  if ($ordertime !== '' AND $ordertime2 !== '' And $totalmoney !== '') {
    $sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2' AND totalmoney = '$totalmoney'";    
  } else if ($ordertime !== '' AND $ordertime2 !== '') {
    $sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2'";  
  } else if ($totalmoney !== '') {
    $sql = "SELECT * FROM orders WHERE totalmoney = '$totalmoney'";   
  } else {
    $sql = "SELECT * FROM orders";  
  }

  $result = mysql_query($sql) or die('MySQL query error');
  if (mysql_num_rows($result)) {
    echo "<table border = '1'>";
    echo "<tr>";
    echo "<th>ordertime</th>";
    echo "<th>totalmoney</th>";
    echo "</tr>";
    while($row = mysql_fetch_array($result))
    {
      echo "<tr>";
      echo "<td>".$row['ordertime']."</td>";
      echo "<td>".$row['totalmoney']."</td>";
      echo "</tr>";
    }
    echo "</table>";
  } else {
    echo "查無資料";
  }
?>

你可以試著改改看如何查詢金額區間

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 2019-05-18 21:55:41

SELECT * FROM table 
WHERE column_time BETWEEN '2008-08-08 20:00:00' AND '2008-12-31 23:59:59'

http://www.bkjia.com/Mysql/1084095.html

回應 2
分享
檢舉

stevenhappy99 iT邦新手 5 級 ‧ 2019-05-19 11:02:02 檢舉

顯示不出來資料，我的欄位英文名稱是ordertime，輸入兩個日期這兩變數，不知這樣設對不對
我PO我的程式可以幫看看嗎

input.php

<html><head>
 <title>輸入查詢資料</title>
 </head><body>
 <form action="test.php" method="Post">
 開始日期:
 <input type="Text" name="ordertime">
 結束日期:
  <input type="Text" name="ordertime2">

 <input type="Submit">
 
 </form>
 </body></html>
 
 
 <html><head>
<title>查詢結果</title>
</head><body>
<?
$ordertime=$_REQUEST["ordertime"];
$ordertime2=$_REQUEST["ordertime2"];
?>

</body></html>

test.php

<?php   
 $dbhost = 'localhost';   
 $dbuser = 'root';   
 $dbpass = '00000000';   
 $dbname = 'goodsapp';   
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL connection');
  
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);   
  $sql = "SELECT * FROM orders WHERE column_time BETWEEN '.$ordertime' AND '.$ordertime2'";
  $result = mysql_query($sql) or die('MySQL query error');
  
  while($row = mysql_fetch_array($result))
  {
   echo $row['ordertime']." ";
   echo $row['totalmoney']."<br>";   
  }
?>

rogeryao iT邦超人 7 級 ‧ 2019-05-19 14:09:06 檢舉

test.php

<?php   
 $dbhost = 'localhost';   
 $dbuser = 'root';   
 $dbpass = '00000000';   
 $dbname = 'goodsapp'; 
 //
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL connection');
  
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);   
  //
  $ordertime = '';
  $ordertime2 = '';
  if (isset($_POST['ordertime'])) {
    $ordertime = $_POST['ordertime'];
  }
  if (isset($_POST['ordertime2'])) {
    $ordertime2 = $_POST['ordertime2'];
  }
  //
  $sql = "SELECT ordertime,totalmoney FROM orders WHERE ordertime BETWEEN '".$ordertime."' AND '".$ordertime2."' "."order by ordertime ";
  // echo $sql;
  $result = mysql_query($sql) or die('MySQL query error');
  //
  echo "<table border = '1'>";
  echo "<tr>";
  echo "<td>ordertime</td>";
  echo "<td>totalmoney</td>";
  echo "</tr>"; 
  while($row = mysql_fetch_array($result))
  {
    echo "<tr>";
    echo "<td>".$row['ordertime']."</td>";
    echo "<td align='right'>".$row['totalmoney']."</td>";
    echo "</tr>";
  }
  echo "</table>";  
?>

登入發表回應