SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict ibeblunt's Avatar
    Join Date
    Jan 2001
    Location
    Jersey City, NJ
    Posts
    312
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Similar Queries But WAY Different Results

    When I do:

    Code:
    SELECT * 
    FROM `tbl_alert_subscriber` 
    WHERE `subscriber_email` LIKE '7136140922@mycingular.com'
    LIMIT 0 , 30
    I get no results.

    However when I do:

    Code:
    SELECT * 
    FROM `tbl_alert_subscriber` 
    WHERE `subscriber_email` LIKE '%7136140922%'
    LIMIT 0 , 30
    I get 133 results. They are the results I'm looking for but what is the difference between the two queries? Currently, it's supposed to allow people to unsubscribe from my mailing list, but it appears that using the first query doesn't work properly.

    Any help is appreciated.
    <///////~

    www.sohh.com

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does this return any results:
    Code:
    SELECT * FROM `tbl_alert_subscriber` WHERE `subscriber_email` LIKE '7136140922@mycingular.com%' LIMIT 0 , 30
    What about this one:
    Code:
    SELECT * FROM `tbl_alert_subscriber` WHERE `subscriber_email` LIKE '%7136140922@mycingular.com' LIMIT 0 , 30
    I'm guessing perhaps you have spaces before or after your email addresses.

  3. #3
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try using EXPLAIN in front of the queries and looking at the results. http://dev.mysql.com/doc/mysql/en/EXPLAIN.html for more information.
    It might help.
    From what I can see, your first query is doing an exact match, as you haven't given the WHERE condition any wildcards.

  4. #4
    SitePoint Addict ibeblunt's Avatar
    Join Date
    Jan 2001
    Location
    Jersey City, NJ
    Posts
    312
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both queries worked.

    I'm looking in the db, and I don't have spaces in the e-mails addresses. Also before I save the addresses to the db, I use trim().

    When I run both the original queries, I get the same result using the EXPLAIN (using: phpMyAdmin).

    table type possible_keys key key_len ref rows Extra
    tbl_alert_subscriber ALL NULL NULL NULL NULL 217864 Using where
    Last edited by ibeblunt; Dec 16, 2004 at 16:07. Reason: MAJOR CORRECTION
    <///////~

    www.sohh.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    um, what was the question again?

    LIKE '7136140922@mycingular.com' will return only that exact value because there's no wildcard characters, so you might as well use an equals sign

    LIKE '%7136140922%' will return any value which contains 7136140922 anywhere inside it, including values such as 'AAAAA7136140922@example.com', '7136140922BBBBB@example.com', 'foo@7136140922.com', and so on

    when you say "the first query doesn't work properly" what in your opinion is it doing incorrectly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •