iT邦幫忙

0

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

我有兩個PHP黨,一個是historyorder.php輸入查詢訂單資料,另一個是test.php接收資料並執行php顯示資料,查詢總額跟姓名可以,為什麼我可以查兩個購買時間日期範圍內卻不能查兩個編號內範圍,我的處理時間兩個範圍內也不行會顯示無此資料 我是依兩個購買時間範圍內這個可以的去修改,可以請教一下怎麼修改嗎 我的時間戳記是年月日時分秒

歷史訂單查詢資料庫
資料庫goodsapp
資料表orders幾個欄位
orderid 訂單編號
name 姓名
ordertime 購買時間
totalmoney 總額
chargetime 處理時間

historyorder.php

輸入購買時間
 <form action="test.php" method="Post">
 日期:
 <input type="Text" name="ordertime">
 至日期:
  <input type="Text" name="ordertime2">
或總金額:
  <input type="Text" name="totalmoney">
 訂單編號:
  <input type="Text" name="orderid">
  至編號:
  <input type="Text" name="orderid2">
 姓名:
  <input type="Text" name="name">
  處理時間:
  日期
  <input type="Text" name="chargetime">
   至日期:
  <input type="Text" name="chargetime2">
  
  

 <input type="Submit"value='查詢'>
 
 </form>

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 = '';
  $totalmoney = '';
  $orderid = '';
  $orderid2 = '';
  $name = '';
  $chargetime = '';
  $chargetime2 = '';

  if (isset($_POST['ordertime'])) {
    $ordertime = $_POST['ordertime'];
  }
  if (isset($_POST['ordertime2'])) {
    $ordertime2 = $_POST['ordertime2'];
  }
  if (isset($_POST['totalmoney'])) {
    $totalmoney = $_POST['totalmoney'];
  }
   if (isset($_POST['orderid'])) {
    $orderid = $_POST['orderid'];
  }
    if (isset($_POST['orderid2'])) {
    $orderid2 = $_POST['orderid2'];
  }
     if (isset($_POST['name'])) {
    $name = $_POST['name'];
  }
       if (isset($_POST['chargetime'])) {
    $chargetime = $_POST['chargetime'];
  }
       if (isset($_POST['chargetime2'])) {
    $chargetime2 = $_POST['chargetime2'];
  }
  if ($ordertime !== '' AND $ordertime2 !== '' And $totalmoney !== '') {
    $sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2' AND totalmoney = '$totalmoney' ORDER BY ordertime DESC";    
  } else if ($ordertime !== '' AND $ordertime2 !== '') {
    $sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2' ORDER BY ordertime DESC";  
  } else if ($totalmoney !== '') {
    $sql = "SELECT * FROM orders WHERE totalmoney = '$totalmoney' ORDER BY ordertime DESC";   
  } else if ($orderid !== '') {
    $sql = "SELECT * FROM orders WHERE orderid = '$orderid' ";
  }  else if ($orderid !== '' AND $orderid2 !== '') {
    $sql = "SELECT * FROM orders WHERE orderid BETWEEN '$orderid' AND '$orderid2' ORDER BY orderid DESC"; 
  } else if ($name !== '') {
    $sql = "SELECT * FROM orders WHERE name = '$name' ";   	
 } else if ($chargetime !== '') {
    $sql = "SELECT * FROM orders WHERE chargetime = '$chargetime' ";
 } else if ($chargetime !== '' AND $chargetime2 !== '') {
    $sql = "SELECT * FROM orders WHERE chargetime BETWEEN '$chargetime' AND '$chargetime2' ORDER BY chargetime DESC";   		
 }else 
  {
    $sql = "SELECT * FROM orders ORDER BY ordertime DESC";  
  }

  $result = mysql_query($sql) or die('MySQL query error');
  if (mysql_num_rows($result)) {
    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>";
  } else {
    echo "查無資料";
  }
?>
看更多先前的討論...收起先前的討論...
dragonH iT邦新手 2 級 ‧ 2019-05-21 17:23:04 檢舉
我的額度滿了 只好在這裡回答

你的

else if ($orderid !== '' AND $orderid2 !== '')

要放在

else if ($orderid !== '')

之前

不然 else if ($orderid !== '') 會先被觸發

同理

else if ($chargetime !== '' AND $chargetime2 !== '')

也是

if else語句符合條件後

會中斷執行後面未執行的判斷

你需要順一下你的判斷

然後最好給一下你database的資料當範本

有的時候也可能是你資料有問題
資料庫要怎麼上傳到這我剛用這網站
dragonH iT邦新手 2 級 ‧ 2019-05-21 17:38:14 檢舉
文字就好
ex:
name: '姓名', 訂單編號: 1234567 .....

不過你可先試試是我上面說的

或許問題就解決了
範圍是可以了但是如果輸入單個購買時間跟處理時間不會出現資料
<?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 = '';
$totalmoney = '';
$orderid = '';
$orderid2 = '';
$name = '';
$chargetime = '';
$chargetime2 = '';

if (isset($_POST['ordertime'])) {
$ordertime = $_POST['ordertime'];
}
if (isset($_POST['ordertime2'])) {
$ordertime2 = $_POST['ordertime2'];
}
if (isset($_POST['totalmoney'])) {
$totalmoney = $_POST['totalmoney'];
}
if (isset($_POST['orderid'])) {
$orderid = $_POST['orderid'];
}
if (isset($_POST['orderid2'])) {
$orderid2 = $_POST['orderid2'];
}
if (isset($_POST['name'])) {
$name = $_POST['name'];
}
if (isset($_POST['chargetime'])) {
$chargetime = $_POST['chargetime'];
}
if (isset($_POST['chargetime2'])) {
$chargetime2 = $_POST['chargetime2'];
}
if ($ordertime !== '' AND $ordertime2 !== '' And $totalmoney !== '') {
$sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2' AND totalmoney = '$totalmoney' ORDER BY ordertime DESC";
} else if ($ordertime !== '' AND $ordertime2 !== '') {
$sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND '$ordertime2' ORDER BY ordertime DESC";
} else if ($totalmoney !== '') {
$sql = "SELECT * FROM orders WHERE totalmoney = '$totalmoney' ORDER BY ordertime DESC";
} else if ($orderid !== '' AND $orderid2 !== '') {
$sql = "SELECT * FROM orders WHERE orderid BETWEEN '$orderid' AND '$orderid2' ORDER BY orderid DESC";
} else if ($chargetime !== '' AND $chargetime2 !== '') {
$sql = "SELECT * FROM orders WHERE chargetime BETWEEN '$chargetime' AND '$chargetime2' ORDER BY chargetime DESC";
} else if ($chargetime !== '') {
$sql = "SELECT * FROM orders WHERE chargetime = '$chargetime' ";
} else if ($orderid !== '') {
$sql = "SELECT * FROM orders WHERE orderid = '$orderid' ";

} else if ($name !== '') {
$sql = "SELECT * FROM orders WHERE name = '$name' ";

}else
{
$sql = "SELECT * FROM orders ORDER BY ordertime DESC";
}

$result = mysql_query($sql) or die('MySQL query error');
if (mysql_num_rows($result)) {
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><a href = 'showorder.php?order=".$row['orderid']."'data-ajax='false'>".$row['orderid']."</a></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>";
} else {
echo "查無資料";
}
?>
dragonH iT邦新手 2 級 ‧ 2019-05-21 20:32:55 檢舉
這就要看你的時間是長怎樣了

2 個回答

0
海綿寶寶
iT邦超人 1 級 ‧ 2019-05-22 09:45:43

算一算你總共有 8 個查詢條件欄位
排列組合出來的查詢條件
就快比 Google 搜尋還複雜

建議你在
$result = mysql_query($sql) or die('MySQL query error');
之前加一列echo $sql;

然後再一個一個輸入你要查詢的各種組合
並且確認是否是你預期的 SQL 指令
這樣才會是最根本的解決方法

0
WQ
iT邦新手 4 級 ‧ 2019-05-22 14:07:39

小弟給2個建議,版大可以參考。(我個人都是這樣子作業的)
1.在傳遞參數時,儘可能"在client端做完能夠做的判斷",包括組合值,也就是有A值必有B值,有A,B值C,D值等類似這些作業(javascript)。所以下你列的判斷就真的....(也是要的,但寫法如建議2)

if (isset($_POST['ordertime'])) {$ordertime = $_POST['ordertime'];}
....中間很多....
if (isset($_POST['chargetime2'])) {$chargetime2 = $_POST['chargetime2'];}

2.有參照建議1時,這時只要判斷有沒有"值",有值就串WHERE條件。
if ($ordertime !== '' AND $ordertime2 !== '' And $totalmoney !== '') {
$sql = "SELECT * FROM orders WHERE ordertime BETWEEN '$ordertime' AND
...這堆都不用...
} else if ($chargetime !== '') {
$sql = "SELECT * FROM orders WHERE chargetime = '$chargetime' ";

寫法如下
if (isset($_POST['ordertime'])) {$where=" AND ordertime>='$_POST['ordertime']' ";}
if (isset($_POST['ordertime2'])) {$where=" AND ordertime<='$_POST['ordertime2']' ";}
最後再組SQL
$sql = "SELECT * FROM orders WHERE 1=1 ".$where;

最後 時間區間也可以是 datetime >= formdatetime1 and datetime <= formdatetime2
雖然 BETWEEN 會比較快(印象中啦),但其實差不是很多....

寫完長這樣.......

$where="";
if (isset($_POST['ordertime']))   {$where = " and ordertime>=". $_POST['ordertime'];}
if (isset($_POST['ordertime2']))  {$where = $where." and ordertime<=". $_POST['ordertime2'];}
if (isset($_POST['totalmoney']))  {$where = $where." and totalmoney=". $_POST['totalmoney'];}
if (isset($_POST['orderid']))     {$where = $where." and orderid>=". $_POST['orderid'];}
if (isset($_POST['orderid2']))    {$where = $where." and orderid2<=". $_POST['orderid2'];}
if (isset($_POST['name']))        {$where = $where." and name='". $_POST['name']."'";}
if (isset($_POST['chargetime']))  {$where = $where." and chargetime >=".$_POST['chargetime'];}
if (isset($_POST['chargetime2'])) {$where = $where." and chargetime<=".$_POST['chargetime2'];}

$sql = "SELECT orderid,name,ordertime,totalmoney,chargetime FROM orders WHERE 1=1 ".$where;
看更多先前的回應...收起先前的回應...

想問最下面$sql 中 1=1是什麼意思

可以再解釋一下程式碼嗎

dragonH iT邦新手 2 級 ‧ 2019-05-22 16:43:33 檢舉

你可以複製他的code 然後去觀察不同條件下的$sql就知道了

1 = 1 的結果必定為true,

在這是為了配合 where

如果不加

sql語句組起來會變成 where and ordertime ....

這樣我想是一定會報錯的

參考

WQ iT邦新手 4 級 ‧ 2019-05-23 08:58:44 檢舉

DragonH 講解的很好,1=1 是恆等式 只是為了配合 組$WHERE...

我要發表回答

立即登入回答