FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Searching by username fails





devroom
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:
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 Very Happy

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? Cool

Grtz
devroom
Solved it Cool

The problem was that if sql selects the wrong one, it selects the active user as he must have sent or received the message.
It very logical. Anyway it is solved by putting u.id != active-user-id
Related topics
Visitor ip PHP (E107)
I searching for a site
The noob needs help/Plz
How did you come up with your username?
(official) Firefox & Thunderbird 1.5 Beta2 Now Available
Searching for script masters FLASH 8 USERS OR PROGRAMMING
searching web hosting space
So what's the history of your frihost names?
[Resolved]My username has not been shown as I typed it
How to park a domain?
how can i make a php script for enter username and password?
AUTOMATIC USERNAME CHANGE+TWIST
Help with PhpBB
Emailing Username And Password
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.