将MySQL数据库中两个表的数据与PHP PDO进行匹配并提取

Question

How to extract data based on matched values from two tables on MySQL database with PHP loop?

On table_x I have the column ZIPCODE and on table_y I have ZIPCODE and STREETCODE like this:

+-----+---------+  +---------+------------+
| id  | zipcode |  | zipcode | streetcode |
+---------------+  +---------+------------+
| 01  | 1234001 |  | 1111001 |     123    |
+---------------+  +---------+------------+
| 02  | 1234002 |  | 1234001 |     456    |
+---------------+  +---------+------------+
table_x            table_y

I want to extract each line from table_x with the matching streetcode from table_y but i'm not sure which join to use and if I should replace foreach with a while loop to achieve my goal.

$sql = 'SELECT * FROM table_x ORDER BY id';
    foreach ($db->query($sql) as $row) {                
        echo $row['zipcode'] . "<br>";
        // select from other table
    } 

Eventually, output each line as the zipcode with the streetcode next to it..

Answer 1

Use NATURAL JOIN, if the associated tables have the identical column name zipcode and the columns are of same data type.

SELECT *
FROM table_x
NATURAL JOIN table_y

Or use INNER JOIN on zipcode

SELECT *
FROM table_x
INNER JOIN table_y
ON table_x.zipcode=table_y.zipcode