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

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

图片转代码服务由CSDN问答提供 功能建议

如何使用PHP循环基于MySQL数据库上两个表的匹配值提取数据?

在table_x上我有ZIPCODE列,在table_y上我有ZIPCODE和STREETCODE,如下所示:

  + ----- + ------  --- + + --------- + ------------ + 
 |  id | 邮编|  | 邮编|  streetcode | 
 + --------------- + + --------- + ------------ + 
 |  01 |  1234001 |  |  1111001 |  123 | 
 + --------------- + + --------- + ------------ + 
 |  02 |  1234002 |  |  1234001 |  456 | 
 + --------------- + + --------- + ------------ + 
table_x table_y 
 <  / code>  
 
 

我想用table_y中匹配的街道代码从table_x中提取每一行,但我不确定要使用哪个 join ,如果我应该替换 带有 while 循环的 foreach 来实现我的目标。

  $ sql ='SELECT * FROM table_x ORDER BY id'  ; 
 foreach($ db-&gt; query($ sql)as $ row){
 echo $ row ['zipcode']。  “&lt; br&gt;”; 
 //从其他表格中选择
} 
   
 
 

最后,将每一行输出为包含旁边街道代码的邮政编码。 。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

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
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • drd94483 2017-08-13 13:22

    you can use join query,

    $dbh = new PDO('mysql:host=localhost;dbname=database', $user, $pass);
    //replace table_x, y with your table names
    $sql = "SELECT table_x.zipcode, table_y.streetcode
    FROM table_x
    INNER JOIN table_y ON table_x.zipcode=table_y.zipcode";
    $stmt = $dbh->prepare($sql);
    if ($stmt->execute()) {
        while ($row = $stmt->fetch()) {
            print_r($row);
        }
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题