Professional Web Applications Themes

complicated query - please help - MySQL

Query Summary I'm trying to check if informations provided by new users are already in my database. Things like email address, cellphone number and user name. Right now I'm asking this way: SELECT username, email, user_mobile_number FROM user_dossier_tbl AS ud, user_login_tbl AS ul WHERE (email="com" OR user_mobile_number="1234567890" OR username="tom") AND ud.user_id = ul.user_id; As a response I'm getting table with all existing user login names, emails and cellphone numbers ... then im parsing it with PHP. I was trying to find a way to ask MySQL in the way that the output table would be in a form: loginname | ...

  1. #1

    Default complicated query - please help

    Query Summary

    I'm trying to check if informations provided by new users are already in my
    database. Things like email address, cellphone number and user name.

    Right now I'm asking this way:

    SELECT username, email, user_mobile_number FROM user_dossier_tbl AS ud,
    user_login_tbl AS ul WHERE
    (email="com" OR user_mobile_number="1234567890" OR
    username="tom") AND ud.user_id = ul.user_id;

    As a response I'm getting table with all existing user login names, emails
    and cellphone numbers ... then im parsing it with PHP.

    I was trying to find a way to ask MySQL in the way that the output table
    would be in a form:

    loginname | email | cellphone
    1 | 0 | 1

    That way it would be easer to check it later in php. If in a column is 1 it
    means that there is a user with this login name...

    Please help construct thie query or point me where i can find example like
    mine.


    Ralph Guest

  2. #2

    Default Re: complicated query - please help

    Ralph wrote:
     


    SELECT COUNT(*) FROM table WHERE username = 'tom' UNION SELECT COUNT(*) FROM
    table WHERE email = 'com' UNION SELECT COUNT(*) FROM table
    WHERE user_mobile_number = '1234567890';

    Will give you something like

    +----------+
    | COUNT(*) |
    +----------+
    | 1 |
    | 0 |
    | 1 |
    +----------+


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  3. #3

    Default Re: complicated query - please help

    I think this is what you want:

    SELECT IF(email="com",1,0) as email,
    IF(user_mobile_number="1234567890",1,0) as
    user_mobile_number,
    IF(username="tom",1,0) as username
    FROM user_dossier_tbl
    WHERE email="com"
    OR user_mobile_number="1234567890"
    OR username="tom"

    In the where clause above, we find a row that matches any one of the
    criteria. In the select clause we test each field individually to see
    if it is the one that caused the match. It might be possible to remove
    the IF() functions, leaving only the test cases. I'm not sure and I
    don't have a MySQL server to test this on right now. Actually, I'm
    pretty sure that is the case.

    This will return an individual row for any row that matches any of the
    three entries. If instead, you want just one run that returns
    everything, add a SUM() function around each select field. This will
    give you the total number of rows that match the email, number, and
    username.

    The problem with your query was that you were doing a join of the table
    with itself. As a result, it was going through and finding a row that
    matched your criteria. Once found, it would add the same row again
    from the same table.


    Ralph wrote: 

    jinxidoru Guest

  4. #4

    Default Re: complicated query - please help

    HI

    Thank you for replying. The only problem with your query is that its
    selecting only from table user_dossier_tbl and username is in table
    user_login_tbl.

    i changed your query like this:

    SELECT SUM(IF(email="com",1,0)) as email,
    SUM(IF(user_mobile_number="1234567890",1,0)) as ser_mobile_number,
    SUM(IF(username="tom",1,0)) as username
    FROM user_dossier_tbl, user_login_tbl
    WHERE email="com"
    OR user_mobile_number="1234567890"
    OR username="tom";

    and my response is:

    email|ser_mobile_number|username
    2 | 0 | 0

    That would mean that i have two users with this email in the database... but
    i have only one.... :(

    Ralph

    "jinxidoru" <net> wrote in message
    news:googlegroups.com... 
    >[/ref]


    Ralph Guest

  5. #5

    Default Re: complicated query - please help

    Problem solved :) Thank you everyone

    SELECT IF(email="com",1,0) as email,
    IF(user_mobile_number="1234567890",1,0) as ser_mobile_number,
    IF(username="tom",1,0) as username
    FROM user_dossier_tbl as ud, user_login_tbl as ul
    WHERE (email="com" OR user_mobile_number="1234567890" OR
    username="tom") AND
    ud.user_id = ul.user_id;

    "jinxidoru" <net> wrote in message
    news:googlegroups.com... 
    >[/ref]


    Ralph Guest

  6. #6

    Default Re: complicated query - please help

    Whoops! I didn't notice that you were pulling from two different
    tables. I really do need to be more observant. Well, regardless, I'm
    glad that you found a solution.

    Ralph wrote: 
    > >[/ref][/ref]

    jinxidoru Guest

  7. #7

    Default Re: complicated query - please help

    One more thing. You're probably better off using two queries or a
    union instead of a join. The join is more expensive than the union,
    and it doesn't offer any benefit since the relation between the tables
    doesn't matter. All you care about is whether these values exist. The
    query you have will work, but it may cause the server to do a lot more
    work than necessary.

    Ralph wrote: 
    > >[/ref][/ref]

    jinxidoru Guest

  8. #8

    Default Re: complicated query - please help

    Hi

    Brian gave me the solution using union (thanks a lot btw) but i get 3 rows
    using it. Is it possible to return it in one row like you did but using
    unions?

    Brian's example:
    SELECT COUNT(*) FROM table WHERE username = 'tom' UNION SELECT COUNT(*) FROM
    table WHERE email = 'com' UNION SELECT COUNT(*) FROM table
    WHERE user_mobile_number = '1234567890';

    +----------+
    | COUNT(*) |
    +----------+
    | 1 |
    | 0 |
    | 1 |
    +----------+

    Ralph


    "jinxidoru" <net> wrote in message
    news:googlegroups.com... [/ref]
    >[/ref]


    Ralph Guest

Similar Threads

  1. Replies: 1
    Last Post: June 26th, 04:09 PM
  2. for( ).....complicated..
    By UsualSuspect in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: March 5th, 01:20 PM
  3. Requesting help with complicated query
    By Fox in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 7th, 10:46 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139