大家好我是一枚超新新手,這個問題已經困擾我五天了 求各位大神救救我QQ
因使用下拉式選單且可複選
從index.php POST過來的值
傳到SQL.php做迴圈
**因為傳入值為2個以上,所以用foreach
**
資料庫大概狀況為
某個病 有可以吃和不能吃的東西
當疾病有多個不能吃的東西可能和能吃的重疊
所以我用foreach語法去跑
**EX:把疾病1能吃的 去掉 疾病2不能吃的
**
但最後的產出包含原本的那個
**所以總結果還是會把不能吃的東西顯示在能吃的地方
**
請問這塊該如何解決呢?因為真的是超新手,這種西想了一個禮拜也解決不了QQ
SQL.php 存放SQL語法
$dis = array($diseasecategory2,$diseasesort2,$period2);//此三值為下拉是選單POST的值
for ($i=0; $i <count($diseasesort2) ; $i++) {
$sort = $dis[1][$i]; $catg=$dis[0][$i]; $per=$dis[2][$i];
for ($ni=0; $ni <count($diseasesort2) ; $ni++) {
# code...
$nf = $nfiv2[$ni]; $ne = $negg2[$ni]; $nv = $nveg2[$ni]; $no = $noil2[$ni]; $nd = $ndes2[$ni]; $nm = $nmilk2[$ni]; $nfr = $nfru2[$ni];
$fiv2[] = "SELECT 食物 FROM 食物表,建議飲食,治療分期,食物分類表 WHERE 治療分期.治療期=\"$per\"AND(SELECT 疾病編號 FROM 疾病總表 WHERE 疾病別=\"$catg\"AND 疾病種類=\"$sort\")=治療分期.疾病編號 AND 治療分期.治療期編號=建議飲食.治療期編號 AND 建議飲食.食物分類代號=食物分類表.分類代號 AND 食物分類表.分類代號=食物表.食物分類代號 AND 食物表.食物分類代號 LIKE \"A%\" AND NOT 食物 IN ($nf)";
$egg2[] = "SELECT 食物 FROM 食物表,建議飲食,治療分期,食物分類表 WHERE 治療分期.治療期=\"$per\"AND(SELECT 疾病編號 FROM 疾病總表 WHERE 疾病別=\"$catg\"AND 疾病種類=\"$sort\")=治療分期.疾病編號 AND 治療分期.治療期編號=建議飲食.治療期編號 AND 建議飲食.食物分類代號=食物分類表.分類代號 AND 食物分類表.分類代號=食物表.食物分類代號 AND 食物表.食物分類代號 LIKE \"B%\" AND NOT 食物 IN ($ne)";
以下相同語法省略
}
}
back.php
$sl=NULL;
$nl = NULL;
foreach ($eatlist2 as $ek2 => $ev2) {
foreach ($ev2 as $ekk2 => $evv2) {
for ($e=0; $e <count($evv2)/2 ; $e++) {
$sth78 = $conn->prepare($eatlist2[$ek2][$ekk2][$e]);
$sth78->execute();
for($p=0; $row = $sth78->fetch(); $p++){
echo $row['食物'];
}//for end
}}}
有試過在back.php使用 array_unique 和 array_intersect 但還是不能行
我也是個新手,這邊提供一點建議,能用SQL解決的問題就用SQL解決,例如A病能吃"蘋果,生梨,橘子",B病能吃"蘋果,生梨"不能吃"橘子",這個用SQL語法裡面的交集查詢就能得到解答,不需要簡單問題複雜化,以下是參考網址跟SQL語法:
/* food.sql內容 */
DROP TABLE IF EXISTS food;
CREATE TABLE food(f_id INTEGER PRIMARY KEY, f_name TEXT);
INSERT INTO food(f_id,f_name) VALUES('1','蘋果');
INSERT INTO food(f_id,f_name) VALUES('2','甘蔗');
INSERT INTO food(f_id,f_name) VALUES('3','生梨');
INSERT INTO food(f_id,f_name) VALUES('4','橘子');
INSERT INTO food(f_id,f_name) VALUES('5','鳳梨');
DROP TABLE IF EXISTS disease;
CREATE TABLE disease(d_id INTEGER PRIMARY KEY, d_name TEXT);
INSERT INTO disease(d_id,d_name) VALUES('1','糖尿病');
INSERT INTO disease(d_id,d_name) VALUES('2','高血壓');
INSERT INTO disease(d_id,d_name) VALUES('3','心臟病');
DROP TABLE IF EXISTS eatable;
CREATE TABLE eatable(e_id INTEGER PRIMARY KEY, f_id INTEGER, d_id INTEGER, e_stat INTEGER);
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('1','1','1','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('2','2','1','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('3','3','1','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('4','4','1','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('5','5','1','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('6','1','2','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('7','2','2','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('8','3','2','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('9','4','2','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('10','5','2','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('11','1','3','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('12','2','3','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('13','3','3','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('14','4','3','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('15','5','3','0');
/* SQL全部資料列表 */
SELECT eatable.e_id AS e_id,eatable.e_stat AS e_stat, food.f_name AS f_name,disease.d_name AS d_name
FROM eatable LEFT JOIN food on eatable.f_id=food.f_id LEFT JOIN disease on eatable.d_id=disease.d_id
ORDER BY d_name,e_stat DESC,f_name;
/* SQL糖尿病+高血壓可食未篩 */
SELECT eatable.e_id AS e_id,eatable.e_stat AS e_stat, food.f_name AS f_name,disease.d_name AS d_name
FROM eatable LEFT JOIN food on eatable.f_id=food.f_id LEFT JOIN disease on eatable.d_id=disease.d_id
WHERE (disease.d_id=1 AND e_stat=1) OR (disease.d_id=2 AND e_stat=1);
/* SQL糖尿病+高血壓可食已篩 */
SELECT eatable.f_id AS f_id,food.f_name FROM eatable LEFT JOIN food ON food.f_id=eatable.f_id
WHERE d_id=1 AND e_stat=1
INTERSECT
SELECT eatable.f_id AS f_id,food.f_name FROM eatable LEFT JOIN food ON food.f_id=eatable.f_id
WHERE d_id=2 AND e_stat=1;