Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Jul 2007
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question Database Data Display

    Hi,

    I need some help setting this database up correctly. Any help would be appreciated. I currently have 2 tables with the following fields in each:

    ======================
    Table Name: Master |
    ======================
    ID (Primary ID) ----->
    City ------------>
    Section ------------>
    Year ------------>
    Address ------------>
    City ------------>
    State ------------>
    Zip ------------>
    Phone ------------>
    Quota ------------>
    *******************************
    =========================
    Table Name: Orders |
    =========================
    Order_ID (Primary ID)
    ID
    City
    Section
    Year
    Address
    State
    Zip
    Phone
    Quota
    <------------ Qty
    <------------ Goal
    <------------ Last_Order

    I would like to display the fields from the table called "MASTER": ID, City, Section, Year, Address, City, State, Zip, Phone, and Quota in the Table called "ORDERS".Because that information would be coming from the "MASTER" Table.

    The "Orders" table has 3 fields that will display on the "MASTER" table, which are: Qty, Goal, and Last_Order.

    And everything is Set by "ID" which is the primary key in the "MASTER" table. So the fields would have to display according to what "ID" they belong to. The "ORDERS_ID" from the "ORDERS" table is the primary key since the "ID" in the "ORDERS" table will have multiple orders.

    I have a master.php and orders.php file.

    How would I set something like this up to work correctly? Hope all this makes sense.

    __________________________

    Thanks, Sara
    Last edited by user55; 07-19-2007 at 06:46 PM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I was confused by your table definitions. Does the ORDERS table really contain city, section, year, address, etc? Or are you just showing the data that you want to display from the MASTER table?

    Assuming the latter, you need some way of connecting the ORDERS table to the MASTER table. If any given row in the ORDERS table will belong to one and only one row in the MASTER table, then you can store the MASTER_ID in the ORDERS table to establish that connection. (You may or may not have already done this depending on the answer to my question above.) Then it's a simple join query:

    Code:
    SELECT 
        City
        ,Section
        ,Year
        ,Address
        ,City
        ,State
        ,Zip
        ,Phone
        ,Quota
        ,Qty
        ,Goal
        ,Last_order
    FROM MASTER
    JOIN ORDERS
    ON MASTER.ID = ORDERS.MASTER_ID
    WHERE MASTER.ID = $var

  • #3
    New Coder
    Join Date
    Jul 2007
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for the reply. Yes, the "ORDERS" table also contains the fields city, section, year, address, etc.

    Sara

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    In that case you should normalize your tables and store that stuff in only one place, then store the MASTER_ID in the ORDERS table.

  • #5
    New Coder
    Join Date
    Jul 2007
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    This is the PHP code that I have in orders php file. How would this be setup to work like in your example?

    PHP Code:
    $sql .= " Select\n";
        
    $sql .= "     orders.`Order_ID`,\n";
        
    $sql .= "     orders.`ID`,\n";
        
    $sql .= "     orders.`City`,\n";
        
    $sql .= "     orders.`Section`,\n";
        
    $sql .= "     orders.`Year`,\n";
        
    $sql .= "     orders.`Address`,\n";
        
    $sql .= "     orders.`State`,\n";
        
    $sql .= "     orders.`Zip`,\n";
        
    $sql .= "     orders.`Phone`,\n";
        
    $sql .= "     orders.`Quota`,\n";
        
    $sql .= "     orders.`Qty`,\n";
        
    $sql .= "     orders.`Goal`,\n";
        
    $sql .= "     orders.`Last_Order`,\n";
        
    $sql .= " From\n";
        
    $sql .= "     orders   orders\n";

    if (
    strpos(strtoupper($sql), " WHERE ")) {
       
    $sqltemp $sql " AND (1=0) ";
    }else{
       
    $sqltemp $sql " Where (1=0) ";
    }
    $result mysql_query($sqltemp " " $sql_ext " limit 0,1")
              or die(
    "Invalid query"); 
    ______________________________________________________
    Would I need to delete the fields city, section, year, address, etc; that are already in the "MASTER" table?

    Thanks


    Sara

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Actually my thought was keep the "master" data (address, etc) in the MASTER table, and store only the "order" data (qty, etc) in the ORDERS table. Connect the two tables by storing the MASTER_ID in the ORDERS table.

  • #7
    New Coder
    Join Date
    Jul 2007
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for getting back to me. So, would I delete the fields that are the same like this:

    PHP Code:
    $sql .= " Select\n";
        
    $sql .= "     orders.`Order_ID`,\n";
        
    $sql .= "     orders.`Quota`,\n";
        
    $sql .= "     orders.`Qty`,\n";
        
    $sql .= "     orders.`Goal`,\n";
        
    $sql .= "     orders.`Last_Order`,\n";
        
    $sql .= " From\n";
        
    $sql .= "     orders   orders\n";

    if (
    strpos(strtoupper($sql), " WHERE ")) {
       
    $sqltemp $sql " AND (1=0) ";
    }else{
       
    $sqltemp $sql " Where (1=0) ";
    }
    $result mysql_query($sqltemp " " $sql_ext " limit 0,1")
              or die(
    "Invalid query"); 
    How would I get the other fields to appear here in PHP?

    Sara

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You would use a join query, like I posted in #2 of this thread.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •