SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to make a conditional SQL statement

    Hello,

    I want to make a conditional SQL. I have one table "users" and it has two columns (id, status)

    I need to search for users IDs based on their status. For example.

    Every 5 seconds I am querying from the database (by AJAX) as per the below description:

    Let us say that I have the below results on my wall (on html):
    id1: 1
    status2: "A"

    id2: 2
    status2: "B"

    I need to get IDs from table "users" but exclude the IDs from database if their statuses haven't changed on my wall.

    So the algorithm should be:

    Get all IDs from table "users" but exclude the IDs (1,2) from database (if and only if) their status = ("A", "B").

    Simply, I need to retrieve all IDs including the IDs (1,2). But for the IDs (1,2): I need to retrieve them in case their corresponding statuses are NOT EQUAL ("A", "B") respectively.

    But for IDs other than (1,2) like (3,4,5,...), I need to retrieve them without any condition.

    I would greatly appreciate if the solution provides an excellent performance when the database records are huge and the check every 5 seconds.

    Thanks in advance.

    Cheers,

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Get them all, and do the check for changed status in your scripting language?

  3. #3
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Get them all, and do the check for changed status in your scripting language?
    Thanks for your reply. I don't want to do any check and filtering in PHP. I need to do everything in Mysql query.

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    I think we need a little more info here. Why not just query all where status not in ('A', 'B')

  5. #5
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I think we need a little more info here. Why not just query all where status not in ('A', 'B')
    Thanks for your reply. No, I need to exclude the records which have no status changed. But if the status changed for those records, then I need to get them.

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    And where is the original data stored at to compare to the new? If you save a status change date, you can ensure that the change had occurred in the last n seconds. However you would have to run a different query to populate your original data set.

  7. #7
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    And where is the original data stored at to compare to the new? If you save a status change date, you can ensure that the change had occurred in the last n seconds. However you would have to run a different query to populate your original data set.
    The original data stored in the Javascript and each 5 seconds I am sending AJAX request with the original data. I can't work on time because the transition of the status might get changed with time but with the same value, for example:

    Status now: "hello"
    Status after 1 second: "hello 2"
    Status after 2 seconds: "hello" ==>same as the first one.

    So, I need to check that the status on my DB is different from the one I have on my Javascript to grab it and render it with HTML.

    Thanks.


Bookmarks

Posting Permissions

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