PHP/MYSQL Query by id "duplicate ids"

Question

I am trying to get results from 3 tables, but its repeating the PART_ID and displaying the same id over and over. How can I fix this?

<?php
    $product_list = "";
    $sql = mysql_query("SELECT * FROM PART, PART_TYPE, RACK");
    $productCount = mysql_num_rows($sql);
    if ($productCount >0){

    while($row= mysql_fetch_array($sql)){
                 $id = $row["PART_ID"];
                 $PART_DESC = $row["PART_DESC"];
                 $SERIAL_NUM = $row["SERIAL_NUM"];
                 $RACK_NUM = $row["RACK_NUM"];
                 $PART_TYPE_ID = $row["PART_TYPE_ID"];
                 $PART_TYPE_DESC = $row["PART_TYPE_DESC"];
                 $product_list .= " <strong>PART_ID:</strong> $id -<strong>$PART_DESC</strong> -<strong>Product Type</strong> $SERIAL_NUM - <em><strong>RACK_NUM</strong> $RACK_NUM  - <em> <strong>PART_TYPE_ID</strong> $PART_TYPE_ID  - <em> <strong>PART_TYPE_DESC </strong> $PART_TYPE_DESC   - <em> &nbsp; &nbsp; &nbsp; <a href='inventory_edit.php?pid=$id'>edit</a> &bull; <a href='inventory_list.php?deleteid=$id'>delete</a><br />";
    }

    }else 
        $product_list = "You have not items in the inventory yet"

?>

RESULTS

PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S1 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S2 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S3 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S4 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S5 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S6 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S1 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S2 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S3 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S4 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S5 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S6 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R3S1 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R3S2 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R3S3 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC -       edit • delete

Answer 1

Your query is not well-formed if you have any one-to-many relationships (which it looks like you do). Link the tables based upon some sort of criteria like so:

SELECT * FROM PART
JOIN PART_TYPE on PART.part_type = PART_TYPE.ID
JOIN RACK ON PART.part_rack = RACK.ID

Or something like that. You want to tell the database how you want these tables linked together. This does this.

Answer 2

You should join the tables together using some condition, such as:

SELECT PART_ID, PART_DESC, SERIAL_NUM, RACK_NUM, PART.PART_TYPE_ID, PART_TYPE_DESC
FROM PART
INNER JOIN PART_TYPE ON PART.PART_TYPE_ID = PART_TYPE.PART_TYPE_ID

Basically, what this will accomplish is to get all the rows from the PART table, and for each of the rows we find, match that row to a row in the PART_TYPE table (the condition being that they have the same PART_TYPE_ID). If no match between the PART and PART_TYPE tables can be found for a given row in the PART table, that row will not be included in the result. In effect, that means that you'll only get parts that have a valid corresponding part type.

Note: selecting all columns from a table using SELECT * is generally frowned upon as it makes maintenance difficult. If you ever were to add, remove or rearrange columns all your code would break. As such, I changed your select statement to explicitly retrieve the columns you referenced, in the same order as they were referenced in the code.

---

Edit: I omitted a join with the RACK table in the code as you didn't reference the RACK.LOCATION column. If this was a mistake, just throw in another join like so:

INNER JOIN RACK ON RACK.RACK_NUM = PART.RACK_NUM

And add the LOCATION column to the list of columns to be retrieved in the SELECT statement.

Answer 3

Based on your diagram

SELECT * FROM PART
INNER JOIN PART_TYPE ON PART.PART_ID=PART_TYPE.PART_TYPE_ID
INNER JOIN RACK ON PART.RACK_NUM=RACK.RACK_NUM

should do what you want.