$SQL = "select cus.cus_id,cus.cus_firstname , cus.cus_lastname ,(select provinceName from elvira_crm.province where provinceId = cus.cus_province) as province , (select districtName from elvira_crm.district where districtId = cus.cus_district) as district , (select amphurName from elvira_crm.amphur where amphurId = cus.cus_sub_district) as amphur, cus_address,cus_postalcode , cus.cus_mobilephone from elvira_crm.customer as cus ";
if($listdata == "รหัสลูกค้า") ปกติ
{
$SQL .= " WHERE cus_id LIKE '%$text%' order by cus_id asc";
}
else if($listdata == "ชื่อ") ปกติ
{
$SQL .= " WHERE cus_firstname LIKE '%$text%' order by cus_id asc";
}
else if($listdata == "เบอร์โทรศัพท์") ปกติ
{
$SQL .= " WHERE cus_mobilephone LIKE '%$text%' order by cus_id asc";
}
else if($listdata == "อําเภอ/เขต") **** ไม่ออก
{
$SQL = "select districtName from elvira_crm.district where districtId = cus.cus_district as district , (select amphurName from elvira_crm.amphur where amphurId = cus.cus_sub_district) as amphur where districtName LIKE '%$text%' order by cus_id asc";
}
SELECT cus.cus_id, cus.cus_firstname, cus.cus_lastname, (
SELECT provinceName
FROM elvira_crm.province
WHERE provinceId = cus.cus_province
) AS province, (
SELECT districtName
FROM elvira_crm.district
WHERE districtId = cus.cus_district
) AS district, (
SELECT amphurName
FROM elvira_crm.amphur
WHERE amphurId = cus.cus_sub_district
AND amphurName LIKE 'บางกะปิ' //ส่วนที่ใช้ทดสอบ
) AS amphur, cus_address, cus_postalcode, cus.cus_mobilephone
FROM elvira_crm.customer AS cus
ผลออกมาดังรูปครับ
คำถาม ถ้าต้องการให้โชว์ข้อมูลที่มี amphurName LIKE 'บางกะปิ' อย่างเดียวต้องทำอย่างไรครับ
SELECT cus.cus_id, cus.cus_firstname, cus.cus_lastname, (
SELECT provinceName
FROM elvira_crm.province
WHERE provinceId = cus.cus_province
) AS province, (
SELECT districtName
FROM elvira_crm.district
WHERE districtId = cus.cus_district
) AS district, (
SELECT amphurName
FROM elvira_crm.amphur
WHERE amphurId = cus.cus_sub_district IS NOT NULL
AND amphurName LIKE 'บางกะปิ'
) AS amphur, cus_address, cus_postalcode, cus.cus_mobilephone
FROM elvira_crm.customer AS cus
SELECT cus.cus_id, cus.cus_firstname, cus.cus_lastname, (
SELECT provinceName
FROM elvira_crm.province
WHERE provinceId = cus.cus_province
) AS province, (
SELECT districtName
FROM elvira_crm.district
WHERE districtId = cus.cus_district
) AS district, (
SELECT amphurName
FROM elvira_crm.amphur
WHERE amphurId = cus_sub_district IS NOT NULL
AND amphurName LIKE '%บางกะปิ%'
ORDER BY amphurName ASC
) AS amphur, cus_address, cus_postalcode, cus.cus_mobilephone
FROM elvira_crm.customer AS cus
ในPHP
Code (PHP)
else if($listdata == "amphur"){
$SQL = "SELECT cus.cus_id, cus.cus_firstname, cus.cus_lastname, (
SELECT provinceName
FROM elvira_crm.province
WHERE provinceId = cus.cus_province
) AS province, (
SELECT districtName
FROM elvira_crm.district
WHERE districtId = cus.cus_district
) AS district, (
SELECT amphurName
FROM elvira_crm.amphur
WHERE amphurId = cus_sub_district IS NOT NULL
AND amphurName LIKE '%$text%'
ORDER BY amphurName ASC
) AS amphur, cus_address, cus_postalcode, cus.cus_mobilephone
FROM elvira_crm.customer AS cus ";
Load balance : Server 04
