duandian8251 2017-08-13 13:08
浏览 100
已采纳

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

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..

  • 写回答

2条回答 默认 最新

  • dongxuying7583 2017-08-13 13:13
    关注

    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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?