請問各位SQL語法前輩
我想輸入某人的姓名、手機的部份資料,就能找出這個人
欄位有1.姓名、2.電話、3.手機、4.住址、5.備註
其中1.姓名及3.手機是必填
2.電話、4.住址、5.備註可以是null
我的語法是這樣
SELECT * FROM customer WHERE name LIKE %$name% AND cell LIKE %$cell% AND tel LIKE %$tel% AND add LIKE %$add% AND memo LIKE %$memo%
最後結果是有null的欄位,全都不會出來
是我語法有問題,還是在null欄位中要填上什麼東西呢?
你可以試試
SELECT * FROM customer WHERE name LIKE %$name% AND cell LIKE %$cell% AND isnull(tel,'') LIKE %$tel% AND isnull(add,'') LIKE %$add% AND isnull(memo,'') LIKE %$memo%
這是 SQL Server 的用法!
既然用 Php 要不要試試看
檢查哪個欄位有值
再把條件值 設進去 SQL command
不用把所有欄位全設了 ~
最白話的方式:
SELECT * FROM customer WHERE (name LIKE %$name% OR name is null) AND ( (cell LIKE %$cell% OR cell is null) AND (tel LIKE %$tel% OR tel is null) AND (add LIKE %$add% OR add is null) AND (memo LIKE %$memo% OR memo is null)