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

enter image description here

You have no conditions on your joins. You're just retrieving every combination of rows from each of those three tables. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented May 29, 2011 at 17:55
Can you give me and example on how to resolve this issue? I'm trying to learn more by expanding on a tutorial I found online but im not very good at this stuff yet any assistance is appreciated. — Michael Quiles
– Michael Quiles, Commented May 29, 2011 at 17:59
You have shown us the output you get. What is the output you want? — Hammerite
– Hammerite, Commented May 29, 2011 at 17:59
I want 1 result per PART_ID if possible. Once I figure that out I can continue trying more examples. — Michael Quiles
– Michael Quiles, Commented May 29, 2011 at 18:00
What columns do you have in each of the tables, and how do they relate to each other? — Gareth
– Gareth, Commented May 29, 2011 at 18:02

IAmTimCorey · Accepted Answer · 2011-05-29 18:00:34Z

6

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.

answered May 29, 2011 at 18:00

IAmTimCorey

16.8k5 gold badges42 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Törnwall · Accepted Answer · 2011-05-29 18:21:27Z

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.

Gareth · Accepted Answer · 2011-05-29 18:11:13Z

2

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.

answered May 29, 2011 at 18:11

Gareth

5,7283 gold badges30 silver badges40 bronze badges

3 Comments

Martin Törnwall Over a year ago

Notice that you don't need to join with RACK as he doesn't actually reference the RACK.LOCATION column in the code.

Michael Quiles Over a year ago

I will eventually but im taking it step by step.

Gareth Over a year ago

@Michael That's because there's a mistake in my answer (PART.PART_ID should read PART.PART_TYPE_ID). @Martin's answer is better

haunted85 · Accepted Answer · 2011-05-29 17:58:19Z

1

In order not to display the same results over and over again you need to use DISTINCT, so you may wanna try:

SELECT DISTINCT * FROM PART, PART_TYPE, RACK

answered May 29, 2011 at 17:58

haunted85

1,7017 gold badges24 silver badges41 bronze badges

1 Comment

Hammerite Over a year ago

This is still only going to filter out rows in the join where each and every column from each table is the same. In particular, it's totally redundant if there are unique keys on the tables (and of course there ought to be a primary key on each table).

Collectives™ on Stack Overflow

PHP/MYSQL Query by id "duplicate ids"

4 Answers 4

Comments

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related