SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed with SQL query on 2 tables

    Hi,
    I have 2 tables in my db and need to get the emails from the first table (list_mike) dependant on the location value in another table (options).

    Table 1 - list_mike:
    id|email|etc

    Table 2 - options:
    fieldid|value|etc

    So, what i'm trying to do is....
    select email from list_mike
    WHERE
    value="south" from options

    Not sure how to do this mutli-table query, and any help much appreciated!
    Mike
    "You know what you know - but that's all you know!"

  2. #2
    SitePoint Enthusiast Vpekulas's Avatar
    Join Date
    Mar 2000
    Location
    Winnipeg, Canada
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Do you reference the first table in the other one ?
    In other words are you trying to get the value based on a foreign key ?
    Vladimir S. Pekulas
    http://www.Europeum.net
    http://wap.Europeum.net

  3. #3
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So is Table 1's "id" related to Table 2's "fieldid"? If so, you would use something like this:

    select email from table1, table2 where table1.id=table2.feildid

    Does that answer it?
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  4. #4
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies so far

    In Table 1 (list_mike) 'id' is the same as 'userid' in Table 2(options)

    Regards,
    Mike
    "You know what you know - but that's all you know!"

  5. #5
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies so far

    In Table 1 (list_mike) 'id' is the same as 'userid' in Table 2(options)

    Regards,
    Mike
    "You know what you know - but that's all you know!"

  6. #6
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, you did not give us enough info the first time you asked. Where did this userid business come from?

    don't read this bonehead business, I made a silly error that is corrected belowselect email from list_mike, options where list_mike.id=options.userid where options.value='south'
    Last edited by samsm; Sep 25, 2002 at 19:12.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  7. #7
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by samsm

    select email from list_mike, options where list_mike.id=options.userid where options.value='south'
    2 Where's?

    Don't you mean...

    SELECT email FROM list_mike, options WHERE list_mike.id=options.userid && options.value='south'

    BTW: I always like to use the mysql things (SELECT, UPDATE, SET, WHERE, FROM, INSERT, DELETE etc.) in capitals, so you can see the true difference a lot easier.

  8. #8
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that's what I meant, thanks!
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  9. #9
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thankyou guys,
    Much appreciated. Big thanks to phpJohn who helped me for nearly an hour on this labrynth of a db. This was the solution.....
    PHP Code:
    $query "SELECT userid FROM options WHERE fieldid = 111 AND value = 'south'"
      
    $result mysql_query($query);
      
    $resultOptions $result;  
    while(
    $fetch mysql_fetch_array($resultOptions)) 

     { 

    $query "SELECT email FROM list_mike WHERE id = ".$fetch["userid"]; 
      
    $result mysql_query($query);
        
    $fetchEmail mysql_fetch_array($result); 
    $emailAddress $fetchEmail["email"]; 
    echo 
    $emailAddress."<br />";

     } 
    There should be some sort of reward system for getting help like this! I should be able to order a pizza online for phpJohn and have it delivered to his/her door as a means of appreciation. "PayPizza" or somethin'
    "You know what you know - but that's all you know!"


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
  •