Thread: Join Tables

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0

    Join Tables


    Hi there,

    I'm new to Mysql so please bear with me.

    I am currently in the process of building a system for my business where we can log special orders placed with customers that are emailed straight though to our buying dept, we are trying to cut down on paper in the business!

    I have created the db and the php site which works OK at the moment but on our list of open orders page we get all the data we need from one table (the one holding the order data) within this table there is a supplier id column which the data is pulled from our second table with the suppliers information.

    On the open orders page at present the supplier ID is shown table "orders", I am struggling to find out how we can using the supplier ID from the orders table pull the Suppliers name from the "suppliers" table.

    EG: As is now:
    Orders Table:
    Order ID Date Name Phone SupplierID Code PName PSize PColour ReceiptNo Contacted Closed
    44 20120801 Phil 44***2 3 3154 HH Booots small cobalt R7000000 N N
    49 20120803 Phil 44***2 5 3154 HH Booots small cobalt R7000000 N N

    Suppliers Table
    SupplierID SupplierName
    3 Supplier1
    4 Supplier2
    5 Supplier3

    EG: How it should look
    Order ID Date Name Phone SupplierID Code PName PSize PColour ReceiptNo Contacted Closed
    44 20120801 Phil 44*******2 Supplier1 3154 HH Booots small cobalt R7000000 N N
    49 20120803 Phil 44***2 Supplier3 3154 HH Booots small cobalt R7000000 N N


    OUTPUT

    Any help would be fantastic.

    Thank you in advance,
    Phil
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    the table itself will only show the foreign key

    if you query in this way you will get the correct results in your php
    Code:
    SELECT o.OrderId
    , o.Date
    , o.Name
    , s.SupplierName
    
    FROM Orders AS o
    
    INNER
      JOIN Suppliers AS s
        ON s.SupplierID = o.OrderId
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0
    Hi and thanks,

    I'm now getting this where the result should be:


    Resource id #6

    Resource id #7

    Any ideas what this is?

    Thank you,
    Phil
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by philjm82
    Resource id #6

    Resource id #7

    Any ideas what this is?
    yup

    those be php errors

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    if you post your php code i can help.

    here is a generic chunk of code to get/handle results
    PHP Code:
    $sql "SELECT o.OrderId
    , o.Date
    , o.Name
    , s.SupplierName

    FROM Orders AS o

    INNER
      JOIN Suppliers AS s
        ON s.SupplierID = o.OrderId"
    ;

    $query mysql_query("$sql") or die("error message here");

    while (
    $rows=mysql_fetch_array($query)){
    $date $row['Date'];
    $name $row['Name'];
    $supplier $row['SupplierName'];} 

IMN logo majestic logo threadwatch logo seochat tools logo