SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: JOIN Question

  1. #1
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    JOIN Question

    I am trying to print a list of companies and each company's username in a list on a page. There are two tables, menu and customer. Can somebody tell me what I'm doing wrong? Here's my database schema:

    table = customer
    row = id
    row = usrname

    table = menu
    row = company
    row = customer_id

    Here's my code. I'm not getting any errors but it's also not printing the data on my page so something must be wrong.

    PHP Code:
    $result mysql_query('SELECT customer.id, customer.usrname FROM customer LEFT JOIN menu ON menu.company WHERE menu.customer_id = customer.id') or die (mysql_error());
       while (
    $row mysql_fetch_assoc($result)) {
           
    $usrname $row['usrname'];
           
    $company $row['company'];
       
           
    $listing "$usrname\n";
           
    $listing .= "$company\n";
       } 
    Any help would be greatly appreciated.
    John Saunders

  2. #2
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Sydney
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What happens if you do a echo $listing; under your while loop?

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    PA, USA
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you most likely want to say something like this:

    Code:
    select m.company as company,  c.usrname as usrname from customer c, menu m where c.id = m.customer_id order by m.company
    At least, interpreting your SQL and your statements, thats what I think you want.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would use a left outer join to include customers without companies
    Code:
    select c.usrname 
         , m.company 
      from customer c
    left outer
      join menu m 
        on c.id 
         = m.customer_id 
    order 
        by c.usrname 
         , m.company
    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
  •