De Assis Moneira De Assis Moneira - 4 months ago 8
SQL Question

SELECT WITH LIKE AND WHERE QUERY in PHP

i am having issues of selecting a query with a condition in PHP

SELECT AES_DECRYPT(v_name, 'k7ewjks93ls82!!') as v_name,
AES_DECRYPT(v_email, 'k7ewjks93ls82!!') as v_email,
AES_DECRYPT(v_id_no, 'k7ewjks93ls82!!') as v_id_no,
AES_DECRYPT(v_phone, 'k7ewjks93ls82!!') as v_phone,
v_id, c_id, v_purpose
FROM visitor
WHERE c_id = '1' AND
AES_DECRYPT(v_name, 'k7ewjks93ls82!!') LIKE '%a%' OR
AES_DECRYPT(v_email, 'k7ewjks93ls82!!') LIKE '%a%' OR
AES_DECRYPT(v_id_no, 'k7ewjks93ls82!!') LIKE '%a%' OR
AES_DECRYPT(v_phone, 'k7ewjks93ls82!!') LIKE '%a%' OR
v_purpose LIKE '%a%'


I am trying to query search with 'a' but using c_id = 1, but these are the actual results im getting

I need to be querying viewingthese results instead.

Please help me out. Thanks

Answer

You just need parentheses in the right places. I think you intend:

SELECT AES_DECRYPT(v_name, 'k7ewjks93ls82!!') as v_name, AES_DECRYPT(v_email, 'k7ewjks93ls82!!') as v_email,
       AES_DECRYPT(v_id_no, 'k7ewjks93ls82!!') as v_id_no, AES_DECRYPT(v_phone, 'k7ewjks93ls82!!') as v_phone,
       v_id, c_id, v_purpose
FROM visitor
WHERE c_id = '1' AND
      (AES_DECRYPT(v_name, 'k7ewjks93ls82!!') LIKE '%a%' OR
       AES_DECRYPT(v_email, 'k7ewjks93ls82!!') LIKE '%a%' OR
       AES_DECRYPT(v_id_no, 'k7ewjks93ls82!!') LIKE '%a%' OR
       AES_DECRYPT(v_phone, 'k7ewjks93ls82!!') LIKE '%a%' OR
       v_purpose LIKE '%a%'
      );

(Note: I'm not sure if v_purpose should be in the OR logic.)

If you are learning SQL/programming, then use parentheses whenever you mix AND and OR operators. That will prevent such problems in the future.