SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Query for checking an email in 2 different tables

    I'm using Dreamweaver now to build a site. A lot of things are written by itself and some things really don't.
    Now I'm trying to build a page where the user is adding a name and an e-mail.

    I want my page to check that the e-mail isn't already in my table and that is pretty simple.
    But, now I want this one to check if the e-mail is in either table_users OR table_members

    So, if someone is filling in the form with their name, address and e-mail I want the query to check both tables if the e-mail isn't already in the system.
    What would a query like that look like?

    I thought this would work, but it doesn't.
    PHP Code:
      $loginUsername $_POST['email'];
      
    $LoginRS__query sprintf("SELECT * FROM table_users, table_members WHERE member_email=%s OR user_email=%s"GetSQLValueString($loginUsername"text")); 

  2. #2
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There appears to be a similar problem (resolved) at stackoverflow

    Hope that helps
    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  3. #3
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,220
    Mentioned
    237 Post(s)
    Tagged
    1 Thread(s)
    You can't do that query... either both tables are very similar in structure and you want to show the results as if they were only one table, or those tables have some kind of relationship using primary and foreign keys

    I think that you're in the first case and that means using a UNION

    I suggest that you don't use the wild card (*) and take a bit of more time to write the name of the fields you need. It improves speed.

    I guess that both table have a field named "email". Because the name of the field is not exactly the same in both tables (a pratice that I follow, I never use the exact same name in two tables) you'll need an alias.

    Code:
    SELECT users_email AS email FROM table_users
    UNION [ALL]
    SELECT members_email AS email FROM table_members
    WHERE
    email LIKE '%s'
    I wrote [ALL] beause this is optional. The default behaviour of a UNION is to select all the records in the table, anyway.

    Because you're using patterns, you need to use LIKE and not = when you write your WHERE clause

    This should work

  4. #4
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,220
    Mentioned
    237 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by 2ndmouse View Post
    There appears to be a similar problem (resolved) at stackoverflow

    Hope that helps
    He doesn't necessarily need to do a DISTINCT and it looks that there's no place for a JOIN (outer or not) because it seems that both tables don't have a relationship (at least, not directly)

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I solved it in my own little strange way. I did pickup the first hits and then, IF it found one my script did a new search in the next table, otherwise continue another way.
    So, I solved it with two queries and IF ELSE. It works fine, but I guess, not the best way.

  6. #6
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,220
    Mentioned
    237 Post(s)
    Tagged
    1 Thread(s)
    You solved it and that's good. Well done. You can try to use the UNION and see if there's any advantage


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
  •