Geek Answers Handbook

PHP / MYSQL Query by id "duplicate ids"

I try to get the results from 3 tables, but repeat PART_ID and displaying the same identifier again and again. 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

+5
source share
4 answers

You must join the tables together using some condition, for example:

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

, , PART ​​ PART_TYPE ( , PART_TYPE_ID), PART PART PART_TYPE , . , , , .

: SELECT *, , , . - , , . , select, , , , .

: RACK , RACK.LOCATION. , :

INNER JOIN RACK ON RACK.RACK_NUM = PART.RACK_NUM

LOCATION , SELECT.

+4

, " " ( , ). - :

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

- . , , . .

+6

Based on your chart

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.

+2
source

In order not to show the same results again and again, you need to use DISTINCT, so you can try:

SELECT DISTINCT * FROM PART, PART_TYPE, RACK
+1
source

More articles:


All Articles