iT邦幫忙

0

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

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

jerry00218 iT邦研究生 1 級 ‧ 2019-05-19 00:05:55 檢舉
「訂購時間欄位裡兩個日期範圍內有包含今天的資訊」
不太理解
就是日期欄位裡的資料 輸入兩個日期顯示這兩個範圍內的資料

2 個回答

1
dragonH
iT邦新手 2 級 ‧ 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>";
?>
看更多先前的回應...收起先前的回應...

查詢日期可以了
如果我加入查詢總金額是這樣改嗎
我剛可以查總金額的資料,但是如果都不存在的總金額 顯示是表格而已
https://ithelp.ithome.com.tw/upload/images/20190519/20117723dPks98HUim.jpg

<?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邦新手 2 級 ‧ 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 "查無資料";
  }

我剛剛那個程式好像加總金額欄位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邦新手 2 級 ‧ 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 "查無資料";
  }
?>

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

1
rogeryao
iT邦研究生 2 級 ‧ 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

顯示不出來資料,我的欄位英文名稱是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邦研究生 2 級 ‧ 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>";  
?>

我要發表回答

立即登入回答