iT邦幫忙

1

選擇性輸入查詢資料

php
qazaq54321 2022-07-19 09:56:57 ‧ 794 瀏覽

  • 分享至 

  • xImage

我要如何只打其中的幾格就跑出相對應的資料出來呢

<form method = "POST" >
Month : <input type="text"  name="Month">
Week : <input type="text"  name="Week">
Dai : <input type="text"  name="Dai">
PLANT : <input type="text"  name="PLANT"><br><br>
CUST : <input type="text"  name="CUST">
PKG_GRP : <input type="text"  name="PKG_GRP">
PKG_CODE : <input type="text"  name="PKG_CODE">
PKG_DESC : <input type="text"  name="PKG_DESC"><br><br>
LEAD_COUNT : <input type="text"  name="LEAD_COUNT">
DEVICE_FAMILY : <input type="text"  name="DEVICE_FAMILY">
DEVICE_TYPE : <input type="text"  name="DEVICE_TYPE">
PKG_TYPE : <input type="text"  name="PKG_TYPE"><br><br>
APPLICATION : <input type="text"  name="APPLICATION">
DIE_SIZE : <input type="text"  name="DIE_SIZE">
SUBSTRATE_MFG_PROCESS : <input type="text"  name="SUBSTRATE_MFG_PROCESS">
RUN_TYPE : <input type="text"  name="RUN_TYPE"><br><br>
ISSUE_QTY : <input type="text"  name="ISSUE_QTY">
IN_QTY : <input type="text"  name="IN_QTY">
OUT_QTY : <input type="text"  name="OUT_QTY">
DDD : <input type="text"  name="DDD"><br><br>
FAB_DEF : <input type="text"  name="FAB_DEF">
EXP_DEF : <input type="text"  name="EXP_DEF">
FE_DEF : <input type="text"  name="FE_DEF">
BE_DEF : <input type="text"  name="BE_DEF"><br><br>
TEST_IN : <input type="text"  name="TEST_IN">
OS_QTY : <input type="text"  name="OS_QTY">
TEST_OUT : <input type="text"  name="TEST_OUT">
QD_DEF : <input type="text"  name="QD_DEF"><br><br>
<input type="submit" value="尋找" name="submit" >
<input type="submit" value="清除" name="submit1" ><br>

<?php
if (isset($_POST['submit'])){
	$Month = $_POST['Month'];
	$Week = $_POST['Week'];
	$Dai = $_POST['Dai'];
	$PLANT = $_POST['PLANT'];
	$CUST = $_POST['CUST'];
	$PKG_GRP = $_POST['PKG_GRP'];
	$PKG_CODE = $_POST['PKG_CODE'];
	$PKG_DESC = $_POST['PKG_DESC'];
	$LEAD_COUNT = $_POST['LEAD_COUNT'];
	$DEVICE_FAMILY = $_POST['DEVICE_FAMILY'];
	$DEVICE_TYPE = $_POST['DEVICE_TYPE'];
	$PKG_TYPE = $_POST['PKG_TYPE'];
	$APPLICATION = $_POST['APPLICATION'];
	$DIE_SIZE = $_POST['DIE_SIZE'];
	$SUBSTRATE_MFG_PROCESS = $_POST['SUBSTRATE_MFG_PROCESS'];
	$RUN_TYPE = $_POST['RUN_TYPE'];
	$ISSUE_QTY = $_POST['ISSUE_QTY'];
	$IN_QTY = $_POST['IN_QTY'];
	$OUT_QTY = $_POST['OUT_QTY'];
	$DDD = $_POST['DDD'];
	$FAB_DEF = $_POST['FAB_DEF'];
	$EXP_DEF = $_POST['EXP_DEF'];
	$FE_DEF = $_POST['FE_DEF'];
	$BE_DEF = $_POST['BE_DEF'];
	$TEST_IN = $_POST['TEST_IN'];
	$OS_QTY = $_POST['OS_QTY'];
	$TEST_OUT = $_POST['TEST_OUT'];
	$QD_DEF = $_POST['QD_DEF'];
	$product="select  *  from a4 where Month = '$Month' or Week = '$Week' or Dai = '$Dai' or CUST = '$CUST' or PKG_GRP = '$PKG_GRP' or PKG_CODE = '$PKG_CODE'
or PKG_DESC = '$PKG_DESC' or LEAD_COUNT = '$LEAD_COUNT' or RUN_TYPE = '$RUN_TYPE' or ISSUE_QTY = '$ISSUE_QTY' or IN_QTY = '$IN_QTY'
or OUT_QTY = '$OUT_QTY' or OUT_QTY = '$OUT_QTY' or DDD = '$DDD' or FAB_DEF = '$FAB_DEF' or EXP_DEF = '$EXP_DEF' or FE_DEF = '$FE_DEF'
or BE_DEF = '$BE_DEF' or TEST_IN = '$TEST_IN' or OS_QTY = '$OS_QTY' or TEST_OUT = '$TEST_OUT' or QD_DEF = '$QD_DEF'";
	$result = $project->query($product);
	$row = $result->fetchALL(PDO::FETCH_ASSOC);
    $row_cnt = $result->rowCount();
		if($row_cnt==0){
			echo "沒有相似品項";}
		else{
			echo "</br>"; 
			echo "<table  border='1'   width='1000' height='100'  align='center' >";
			echo "<tr>";
			echo "<th>Month</th>";
			echo "<th>Week</th>";
			echo "<th>Dai</th>";
			echo "<th>PLANT</th>";
			echo "<th>CUST</th>";
			echo "<th>PKG_GRP</th>";
			echo "<th>PKG_CODE</th>";
			echo "<th>PKG_DESC</th>";
			echo "<th>LEAD_COUNT</th>";
			echo "<th>DEVICE_FAMILY</th>";
			echo "<th>DEVICE_TYPE</th>";
			echo "<th>PKG_TYPE</th>";
			echo "<th>APPLICATION</th>";
			echo "<th>DIE_SIZE</th>";
			echo "<th>SUBSTRATE_MFG_PROCESS</th>";
			echo "<th>RUN_TYPE</th>";
			echo "<th>ISSUE_QTY</th>";
			echo "<th>IN_QTY</th>";
			echo "<th>OUT_QTY</th>";
			echo "<th>DDD</th>";
			echo "<th>FAB_DEF</th>";
			echo "<th>EXP_DEF</th>";
			echo "<th>FE_DEF</th>";
			echo "<th>BE_DEF</th>";
			echo "<th>TEST_IN</th>";
			echo "<th>OS_QTY</th>";
			echo "<th>TEST_OUT</th>";
			echo "<th>QD_DEF</th>";
			echo "</tr>";		
			foreach($row as $row1){
			echo "<tr>";
			foreach($row1 as $key => $value){
			echo "<td>".$value."</td>";
			}
			echo "</tr>";	
			}		
			echo "</table>";
}	}
?>
</form>
</div>

https://ithelp.ithome.com.tw/upload/images/20220719/20148269lfG8euSvB6.png

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

0
海綿寶寶
iT邦大神 1 級 ‧ 2022-07-19 10:11:50
最佳解答

參考這篇的寫法
範例是 AND，你自己改成 OR 即可

看更多先前的回應...收起先前的回應...
qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 10:48:45 檢舉 
$product="select  *  from a4 where Month = '$Month' or Week = '$Week' or Dai = '$Dai' or CUST = '$CUST' or PKG_GRP = '$PKG_GRP' or PKG_CODE = '$PKG_CODE'
or PKG_DESC = '$PKG_DESC' or LEAD_COUNT = '$LEAD_COUNT' or RUN_TYPE = '$RUN_TYPE' or ISSUE_QTY = '$ISSUE_QTY' or IN_QTY = '$IN_QTY'
or OUT_QTY = '$OUT_QTY' or DDD = '$DDD' or FAB_DEF = '$FAB_DEF' or EXP_DEF = '$EXP_DEF' or FE_DEF = '$FE_DEF'
or BE_DEF = '$BE_DEF' or TEST_IN = '$TEST_IN' or OS_QTY = '$OS_QTY' or TEST_OUT = '$TEST_OUT' or QD_DEF = '$QD_DEF'";
	if($Month || !empty($Month)){
        $product .= " AND 'Month' = '$Month'";
    }
	if($Week || !empty($Week)){
        $product .= " AND 'Week' = '$Week'";
    }
	if($Dai || !empty($Dai)){
        $product .= " AND 'Dai' = '$Dai'";
    }
	if($CUST || !empty($CUST)){
        $product .= " AND 'CUST' = '$CUST'";
    }
	if($PKG_GRP || !empty($PKG_GRPh)){
        $product .= " AND 'PKG_GRP' = '$PKG_GRP'";
    }
	if($PKG_CODE || !empty($PKG_CODE)){
        $product .= " AND 'PKG_CODE' = '$PKG_CODE'";
    }
	if($PKG_DESC || !empty($PKG_DESC)){
        $product .= " AND 'PKG_DESC' = '$PKG_DESC'";
    }
	if($LEAD_COUNT || !empty($LEAD_COUNT)){
        $product .= " AND 'LEAD_COUNT' = '$LEAD_COUNT'";
    }
	if($RUN_TYPE || !empty($RUN_TYPE)){
        $product .= " AND 'RUN_TYPE' = '$RUN_TYPE'";
    }
	if($ISSUE_QTY || !empty($ISSUE_QTY)){
        $product .= " AND 'ISSUE_QTY' = '$ISSUE_QTY'";
    }
	if($IN_QTY || !empty($IN_QTY)){
        $product .= " AND 'IN_QTY' = '$IN_QTY'";
    }
	if($OUT_QTY || !empty($OUT_QTY)){
        $product .= " AND 'OUT_QTY' = '$OUT_QTY'";
    }
	if($DDD || !empty($DDD)){
        $product .= " AND 'DDD' = '$DDD'";
    }
	if($FAB_DEF || !empty($FAB_DEF)){
        $product .= " AND 'FAB_DEF' = '$FAB_DEF'";
    }
	if($EXP_DEF || !empty($EXP_DEF)){
        $product .= " AND 'EXP_DEF' = '$EXP_DEF'";
    }
	if($FE_DEF || !empty($FE_DEF)){
        $product .= " AND 'FE_DEF' = '$FE_DEF'";
    }
	if($BE_DEF || !empty($BE_DEF)){
        $product .= " AND 'BE_DEF' = '$BE_DEF'";
    }
	if($TEST_IN || !empty($TEST_IN)){
        $product .= " AND 'TEST_IN' = '$TEST_IN'";
    }
	if($OS_QTY || !empty($OS_QTY)){
        $product .= " AND 'OS_QTY' = '$OS_QTY'";
    }
	if($TEST_OUT || !empty($TEST_OUT)){
        $product .= " AND 'TEST_OUT' = '$TEST_OUT'";
    }
	if($QD_DEF || !empty($QD_DEF)){
        $product .= " AND 'QD_DEF' = '$QD_DEF'";
    }
	$result = $project->query($product);
	$row = $result->fetchALL(PDO::FETCH_ASSOC);
    $row_cnt = $result->rowCount();
		if($row_cnt==0){
			echo "沒有相似品項";}
		else{
			echo "</br>"; 
			echo "<table  border='1'   width='1000' height='100'  align='center' >";
			echo "<tr>";
			echo "<th>Month</th>";
			echo "<th>Week</th>";
			echo "<th>Dai</th>";
			echo "<th>PLANT</th>";
			echo "<th>CUST</th>";
			echo "<th>PKG_GRP</th>";
			echo "<th>PKG_CODE</th>";
			echo "<th>PKG_DESC</th>";
			echo "<th>LEAD_COUNT</th>";
			echo "<th>DEVICE_FAMILY</th>";
			echo "<th>DEVICE_TYPE</th>";
			echo "<th>PKG_TYPE</th>";
			echo "<th>APPLICATION</th>";
			echo "<th>DIE_SIZE</th>";
			echo "<th>SUBSTRATE_MFG_PROCESS</th>";
			echo "<th>RUN_TYPE</th>";
			echo "<th>ISSUE_QTY</th>";
			echo "<th>IN_QTY</th>";
			echo "<th>OUT_QTY</th>";
			echo "<th>DDD</th>";
			echo "<th>FAB_DEF</th>";
			echo "<th>EXP_DEF</th>";
			echo "<th>FE_DEF</th>";
			echo "<th>BE_DEF</th>";
			echo "<th>TEST_IN</th>";
			echo "<th>OS_QTY</th>";
			echo "<th>TEST_OUT</th>";
			echo "<th>QD_DEF</th>";
			echo "</tr>";		
			foreach($row as $row1){
			echo "<tr>";
			foreach($row1 as $key => $value){
			echo "<td>".$value."</td>";
			}
			echo "</tr>";	
			}		
		echo "</table>";
}}
//echo    $Month ;
//echo	$Week ;
//echo	$Dai ;
//echo	$PLANT ;
//echo	$CUST;
//echo	$PKG_GRP ;
//echo	$PKG_CODE;
//echo	$PKG_DESC ;
//echo	$LEAD_COUNT ;
//echo	$DEVICE_FAMILY;
?>

我改成這樣子了但他還是跑出一些不相干的資料出來

海綿寶寶 iT邦大神 1 級 ‧ 2022-07-19 10:55:06 檢舉

你在$result = $project->query($product);之前
加一列echo $product;把 SQL 指令顯示出來
就會知道是什麼原因了

qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 11:03:16 檢舉

select * from a4 where Month = '202105' or Week = '' or Dai = '' or CUST = '' or PKG_GRP = '' or PKG_CODE = ''or PKG_DESC = '' or LEAD_COUNT = '' or RUN_TYPE = '' or ISSUE_QTY = '1573' or IN_QTY = ''or OUT_QTY = '' or DDD = '' or FAB_DEF = '' or EXP_DEF = '' or FE_DEF = '' or BE_DEF = '' or TEST_IN = '' or OS_QTY = '' or TEST_OUT = '' or QD_DEF = '' AND 'Month' = '202105' AND 'ISSUE_QTY' = '1573'

qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 11:06:09 檢舉

他確實是抓到我有給參數的格子，但它顯示出來的不只有我有打參數的資料

海綿寶寶 iT邦大神 1 級 ‧ 2022-07-19 11:08:38 檢舉

改成這樣試試看

$product="select  *  from a4 where FALSE ";
	if($Month || !empty($Month)){
        $product .= " OR Month = '$Month'";
    }
	if($Week || !empty($Week)){
        $product .= " OR Week = '$Week'";
    }
	if($Dai || !empty($Dai)){
        $product .= " OR Dai = '$Dai'";
    }
	if($CUST || !empty($CUST)){
        $product .= " OR CUST = '$CUST'";
    }
	if($PKG_GRP || !empty($PKG_GRPh)){
        $product .= " OR PKG_GRP = '$PKG_GRP'";
    }
	if($PKG_CODE || !empty($PKG_CODE)){
        $product .= " OR PKG_CODE = '$PKG_CODE'";
    }
	if($PKG_DESC || !empty($PKG_DESC)){
        $product .= " OR PKG_DESC = '$PKG_DESC'";
    }
	if($LEAD_COUNT || !empty($LEAD_COUNT)){
        $product .= " OR LEAD_COUNT = '$LEAD_COUNT'";
    }
	if($RUN_TYPE || !empty($RUN_TYPE)){
        $product .= " OR RUN_TYPE = '$RUN_TYPE'";
    }
	if($ISSUE_QTY || !empty($ISSUE_QTY)){
        $product .= " OR ISSUE_QTY = '$ISSUE_QTY'";
    }
	if($IN_QTY || !empty($IN_QTY)){
        $product .= " OR IN_QTY = '$IN_QTY'";
    }
	if($OUT_QTY || !empty($OUT_QTY)){
        $product .= " OR OUT_QTY = '$OUT_QTY'";
    }
	if($DDD || !empty($DDD)){
        $product .= " OR DDD = '$DDD'";
    }
	if($FAB_DEF || !empty($FAB_DEF)){
        $product .= " OR FAB_DEF = '$FAB_DEF'";
    }
	if($EXP_DEF || !empty($EXP_DEF)){
        $product .= " OR EXP_DEF = '$EXP_DEF'";
    }
	if($FE_DEF || !empty($FE_DEF)){
        $product .= " OR FE_DEF = '$FE_DEF'";
    }
	if($BE_DEF || !empty($BE_DEF)){
        $product .= " OR BE_DEF = '$BE_DEF'";
    }
	if($TEST_IN || !empty($TEST_IN)){
        $product .= " OR TEST_IN = '$TEST_IN'";
    }
	if($OS_QTY || !empty($OS_QTY)){
        $product .= " OR OS_QTY = '$OS_QTY'";
    }
	if($TEST_OUT || !empty($TEST_OUT)){
        $product .= " OR TEST_OUT = '$TEST_OUT'";
    }
	if($QD_DEF || !empty($QD_DEF)){
        $product .= " OR QD_DEF = '$QD_DEF'";
    }
qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 11:10:44 檢舉

他還是全部跑出來了

海綿寶寶 iT邦大神 1 級 ‧ 2022-07-19 11:11:30 檢舉

第一列的 TRUE 要改成 FALSE

qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 11:20:01 檢舉

select * from a4 where FALSE OR 'Month' = '202105' OR 'LEAD_COUNT' = '576' OR 'ISSUE_QTY' = '1573' OR 'TEST_IN' = '1563'沒有相似品項

他變成都跑不出來了

海綿寶寶 iT邦大神 1 級 ‧ 2022-07-19 11:23:46 檢舉

再改(在上面)
把欄位名稱的單引號拿掉
/images/emoticon/emoticon10.gif

qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 11:30:11 檢舉

目前的狀況是:我打某幾個格子他可以判斷並且抓出準確值，打另外一些格子會跑不出來，又打另外幾個格子會全部都跑出來

海綿寶寶 iT邦大神 1 級 ‧ 2022-07-19 16:21:31 檢舉

整個畫面和程式碼 只做一件事
就是組成要拿去查詢用的 SQL 指令

你要自己去看最後的結果 SQL 指令
才能判斷是什麼原因

qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 16:26:06 檢舉

感謝您的回答，我會努力找問題的

登入發表回應
1
實習工程師
iT邦新手 2 級 ‧ 2022-07-19 10:13:58

判斷有沒有值，有值的話，組SQL語法。

$sqlStr = "select  *  from a4 where 1=1"
if($Month!=''){
    $sqlWhere +=  " And Month = '$Month' "
}

$product =  $sqlStr + $sqlWhere

大概程式碼長這樣，PHP語法不太會，請見諒。

qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 16:26:17 檢舉

感謝您的回答

登入發表回應
0
㊣浩瀚星空㊣
iT邦大神 1 級 ‧ 2022-07-19 15:06:34

我的媽耶!!看到很頭痛。
教你最簡單的組合方式。

首先，條件是你帶進來的 name 也就是資料庫的 name。
畢竟要簡單化。


$keyName = ['Month','Week','Dai','PLANT'];//後面的我懶的打，意思意思一下，反正就是所有要讀的欄位KEY都打上去。這是為了基本安全性。

$orWhere = [];
foreach($keyName AS $_key){
    if(isset($_POST[$_key]) && $_POST[$_key]){
        orWhere[] = $_key."='{$_POST[$_key]}'";
        //如果可以，$_POST[$_key]的值最好做一下拖位處理。這就保留給你自已想
    }
}
$sql = "SELECT * FROM db";
if($orWhere){
    $sql .= ' WHERE '.implode(' OR ',$orWhere);
}
echo $sql;//看一下組合

以上是單純的處理方式，但並不是很安全就是了。要注入還是有可能的。

qazaq54321 iT邦新手 5 級 ‧ 2022-07-19 16:27:49 檢舉

感謝您的回答，我學起來了，謝謝指教。
另外，請問有推薦學MySQL與php語法的書嗎

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙