Hi.
Im trying to let people filter their message by searching. Searching by subject and message works, searching by usernames wont.
Here is the sql query:
I worked with the piece "ON u.id = (jms.toid OR jms.fromid)", because if I change that to "ON (u.id = jms.toid OR u.id = jms.fromid)", it works, but the messages are loaded twice, when you do not search or you dont search by username. So that's not an option
So there was last thing i could think of: "ON u.id = jms.toid OR ON u.id = jms.fromid" or "(ON u.id = jms.toid OR ON u.id = jms.fromid)". They both result into a failing error.
Any ideas?
Grtz
Im trying to let people filter their message by searching. Searching by subject and message works, searching by usernames wont.
Here is the sql query:
| Code: |
| SELECT jms.id, jms.toid, jms.toids, jms.subject, jms.message, jms.datetime, jms.system, jms.statefrom
FROM jos_jms AS jms LEFT JOIN jos_users AS u ON u.id = (jms.toid OR jms.fromid) WHERE jms.fromid = '62' AND jms.outbox = '1' AND (LOWER( jms.subject) LIKE '%cas%' OR LOWER( jms.message) LIKE '%cas%' OR LOWER( u.username) LIKE '%cas%') ORDER BY u.username LIMIT 0, 20 |
I worked with the piece "ON u.id = (jms.toid OR jms.fromid)", because if I change that to "ON (u.id = jms.toid OR u.id = jms.fromid)", it works, but the messages are loaded twice, when you do not search or you dont search by username. So that's not an option
So there was last thing i could think of: "ON u.id = jms.toid OR ON u.id = jms.fromid" or "(ON u.id = jms.toid OR ON u.id = jms.fromid)". They both result into a failing error.
Any ideas?
Grtz
