www.webdeveloper.com
Results 1 to 4 of 4

Thread: MySql, exclude selected row from query or search results

  1. #1
    Join Date
    May 2010
    Posts
    23

    Exclamation MySql, exclude selected row from query or search results

    Hi everybody,

    I have spent hours scratching my head, to solve this this simple issue .

    Basically i have DB table that stores records of users and their details, which contains well over a million rows. The users can login into the system,then can search for other users detail,(name,gender & etc).

    The search work fines, the problem is that the search return the results that also include the current logged in users and his/her details. I don't want details of the current logged in user in the search results.


    I tried using "WHERE 'userID' != 'loggedinuserId'" , this works, but i wanted to know is their a more effective way going about it?


    thanks

  2. #2
    Join Date
    May 2010
    Location
    Effingham, IL
    Posts
    87

    NOT LIKE SQL Statement

    You can try using the 'not like' statement in the where part of SQL statement this would be better because you also could include blocked users so they those users wouldn't display as well. They way you do it now is okay if it works for you but this is what I would recommend because it is more flexible.
    Ryan Condron
    Freelance Web Developer
    http://www.rebelwebdevelopment.com

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    "WHERE 'userID' != 'loggedinuserId'" sounds perfect... but this depends on your query, if you select from a derived table it can not see the outer WHERE clause (so this would not be perfect).

    You need to show the entire query (or if it is dynamic- show the application code also).

    You can always use EXPLAIN SELECT to get more details-- you should also post, and some (anonymous) user data (if you aren't bound by a privacy policy).

    If you want to stress test it- you can use mysqlslap: http://dev.mysql.com/doc/refman/5.1/en/programs.html

    --------------------

    There is one way to make it really really fast, which is to have a SET column called userAttributes and one of the attibutes, lets say the first, is SET('loggedInUser'). Then you can SELECT .... WHERE !(userAttributes & 1), it should be faster because it wont have to compare the entire string- it can look at the BIGINT and just see that they're logged in.

    Cheers
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  4. #4
    Join Date
    May 2010
    Posts
    23

    Exclamation

    Quote Originally Posted by eval(BadCode) View Post

    There is one way to make it really really fast, which is to have a SET column called userAttributes and one of the attibutes, lets say the first, is SET('loggedInUser'). Then you can SELECT .... WHERE !(userAttributes & 1), it should be faster because it wont have to compare the entire string- it can look at the BIGINT and just see that they're logged in.

    Cheers

    Hi there, thanks to both of you.

    rebelweb2007 i will test your idea.
    eval(BadCode) can u please expand on how would the set attribute would work?


    a bit more info:

    there are two tables, one stores the user login and registration details, eg username, avatar, contact details & etc.

    the second table has tags stored that relate to that user, the table are linked by userID in both table.

    search is done in table two, as not all user will have tags, or want to be searchable.


    the query looks more like this:

    " SELECT `userId` FROM (`search_tbl`) WHERE (`userId` != 22) AND (`name` = 'blabla' OR `area` = '12345' OR `hair` = 'Black');" and so......

    the query returns all the users ID that match the search. all ids are stored in a array, which is used to loop through and get the user details from table one.


    thanks again

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles