SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    AZ
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Two Table Output

    I've searched the forum but the answer to my problem is eluding me. Maybe I've been looking at it to long.

    I'm trying display the output of two tables ORDERS and PRODUCTION.

    My current output for one table looks like so:

    <?php
    $db = mysql_connect("localhost","sales","123456") or die("Problem connecting");
    mysql_select_db("sales") or die("Problem selecting database");
    $query = "SELECT * FROM orders WHERE status='Open'";
    $result = mysql_query($query) or die ("Query failed");
    $numofrows = mysql_num_rows($result);
    ?>

    The part that display to the page looks like:
    <?php
    echo "<TABLE class=\"normal\" BORDER=\"0 \" width=\"105%\">\n";
    echo "<TR valign=\"top\" bgcolor=\"lightblue\"><TD nowrap><strong>Date In</strong></TD><TD nowrap ><strong>Order Number</strong></TD><TD nowrap><strong>PO Number</TD><TD nowrap><strong>Name</strong></TD><TD nowrap><strong>Ship To</strong></TD><TD nowrap><strong>Amount</strong></TD><TD nowrap><strong>Product</strong></TD><TD nowrap><strong>Description</strong></TD><TD nowrap><strong>Vendor</strong></TD><TD nowrap><strong>Date Cut</strong></TD><TD nowrap><strong>Shipped</strong></TD><TD nowrap><strong>Tracking</strong><TD nowrap><strong>Notes</strong></TD><TD nowrap><strong>Status</strong></TD></TR> \n";

    for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result);
    if($i % 2) {
    echo "<TR bgcolor=\"yellow\">\n";
    } else {
    echo "<TR bgcolor=\"#C6CEBD\">\n";
    }
    echo "<TD>".$row['date_in']."</TD><TD>".$row['order_number']."</TD><TD>".$row['po_number']."</TD><TD>".$row['name']."</TD><TD>".$row['ship_to']."</TD><TD>".$row['amount']."</TD><TD>".$row['product']."</TD><TD>".$row['description']."</TD><TD>".$row['vendor']."</TD><TD>".$row['date_cut']."</TD><TD>".$row['shipped']."</TD><TD>".$row['tracking']."</TD><TD>".$row['sales_notes']."</TD><TD>".$row['status']."</TD>\n";
    echo "</TR>\n";
    }
    echo "</TABLE>\n";
    ?>

    I have tried all the differnt join methods but always get the query failed error.

    The output will just display the info in the fields for both tables. It works now for each separatly when you do them, but trying to display both of them breaks it.

    Can anyone see what I'm doing wrong?

  2. #2
    SitePoint Zealot soart's Avatar
    Join Date
    Nov 2003
    Location
    Norwich UK
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is an sql guru who's name escapes me for the moment on this forum who may well be answering this post shortly and I'd take his advice on sql everytime.

    My 6 pennyworth

    Do you have 2 fields in each table that are matched and unique?
    IE is the production order number the same as the sales order?

    If so then you can join the 2 tables on that field

    try

    PHP Code:
    $query = ("SELECT * FROM orders LEFT OUTER JOIN production ON orders.order_no = production.po_number WHERE orders.status='Open'");
    $result mysql_query($query) or die ("Query failed"); 
    Good luck!

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might need to make a quesry that looks something like this:
    PHP Code:
    $select "SELECT * FROM orders, production WHERE..."
    Can you post the examples of the JOINS you have tried?

  4. #4
    SitePoint Member
    Join Date
    Dec 2004
    Location
    AZ
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So far, I've tried:

    $query = "SELECT * FROM orders,production WHERE status='open' OR status='closed' ORDER BY date_in DESC";

    Result Query Failed.

    $query = "SELECT * FROM orders JOIN production WHERE status='open' OR status='closed' ORDER BY date_in DESC";

    Result Query Failed.

    I've tried just about all the Join techniques with same result. I did just try soart sample and it did produce a result. Not exactly the result I was looking for but at least it did not fail.

    I'm just trying to get it to display the results of both tables and not really join the data. Perhaps that is why the join function is not working.

  5. #5
    SitePoint Member
    Join Date
    Dec 2004
    Location
    AZ
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I take it back, I've been looking at this to long.

    I would like it to join on the order_number as these will be same in both tables so that way it would only display one result from both tables if the order name was the same.

    I confess I'm pretty good and standard PHP on page stuff, but the Mysql database stuff is fairly new to me.

  6. #6
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't you look at the GROUP BY function here http://dev.mysql.com/doc/mysql/en/GR...Functions.html

  7. #7
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What results do you want

    1) Only those records that have the same order_number in BOTH tables

    2) All the records from the ORDERS table and any matching records from the PRODUCTION table.

    3) All the records from the PRODUCTION table and any matching records from the ORDERS table.

    4) All the records from both tables

  8. #8
    SitePoint Member
    Join Date
    Dec 2004
    Location
    AZ
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would like to see all he records from the ORDERS table and any matching records from the PRODUCTION table.

  9. #9
    SitePoint Zealot soart's Avatar
    Join Date
    Nov 2003
    Location
    Norwich UK
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The select statement sample that I posted before will give you access to all records in orders where orders are open and all records in production where the production order number = sales order number.
    If there is no production order that matches the order.no then there will be no production record available. Does that help?

    You can order the output as well

    PHP Code:
    $query = ("SELECT * FROM orders LEFT OUTER JOIN production ON orders.order_no = production.po_number WHERE orders.status='Open' ORDER BY orders.order_no"); 
    $result mysql_query($query) or die ("Query failed"); 


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
  •