时间戳

首页 > 解决方案 > 使用左连接时的mysqli转换

php - 使用左连接时的mysqli转换

问题描述

正在尝试转换为 MySQLi。这几天我得到了很大的帮助，谢谢。我的下一个问题是如何在使用 LEFT JOIN 时进行转换。

工作MYSQL代码

$q1 = 'SELECT * FROM earth LEFT JOIN cities ON (cities.countryid4city = earth.countryid) GROUP BY countryid ORDER by countryid';
$q2 = 'SELECT * FROM cities ORDER BY cityname';
$mytable1 = mysql_query($q1);
$mytable2 = mysql_query($q2);
echo mysql_error();
mysql_close();
$numrows_table1 = mysql_numrows($mytable1); 
$numrows_table2 = mysql_numrows($mytable2);
$i = 0;
while ($i < $numrows_table1){
    $countryid = mysql_result($mytable1,$i,'countryid');
    $countryname = mysql_result($mytable1,$i,'countryname');
    print "<br><br>Country is " . $countryname . ", and these cities are in it";
    $j = 0;
    while ($j < $numrows_table2){
        $countryid4city = mysql_result($mytable2,$j,'countryid4city');
        $cityname = mysql_result($mytable2,$j,'cityname');
        if ($countryid4city == $countryid){
            print "<br><br>" . $cityname;
        }
        $j++;
    }
    $i++;
}

输出符合预期。（注意，本网站删除了其中的换行符，但它们在那里）。

Country is USA, and these cities are in it

New York

San Francisco

Country is England, and these cities are in it

Chelsea

Clapham

London


Country is Sweden, and these cities are in it

Lidingö
Stockholm

损坏的 MYSQLI 转换（遵循相同的逻辑，我想）

$q1 = 'SELECT * FROM earth LEFT JOIN cities ON (cities.countryid4city = earth.countryid) GROUP BY countryid ORDER by countryid';
$q2 = 'SELECT * FROM cities ORDER BY cityname';
$mytable1 = mysqli_query($conned, $q1);
$mytable2 = mysqli_query($conned, $q2);
mysqli_close($conned);
while($row1 = mysqli_fetch_assoc($mytable1)){
    $countryid = $row1['countryid'];
    $countryname = $row1['countryname'];
    print "<br><br>Country is " . $countryname . ", and these cities are in it";
    while($row2 = mysqli_fetch_assoc($mytable2)){
        $countryid4city = $row2['countryid4city'];
        $cityname = $row2['cityname'];
        if ($countryid4city == $countryid){
            print "<br><br>" . $cityname;
        }
    }
}

输出

Country is USA, and these cities are in it

New York

San Francisco

Country is England, and these cities are in it

Country is Sweden, and these cities are in it

它只为第一个表的第一个值从第二个表中提取 LEFT JOIN 值。我错过了什么？我想我可能对工作的 MYSQL 版本没有一个理想的解决方案。

非常感谢任何帮助。谢谢。

标签： phpmysqlmysqli

解决方案

mysql_result使结果集可用作索引数组。OTOHmysqli_fetch_assoc从结果中检索单行。虽然您可以通过将光标移动到内部循环之前的记录集的开头来解决问题：

  mysqli_result_data_seek ($mytable2, 0);
  while($row2 = mysqli_fetch_assoc($mytable2)){

这只会增加运行第二个查询以检索您已经知道的数据的愚蠢。将第一个查询更改为

 ...ORDER by countryid, cityname";

丢失第二个查询和内部循环。每次 countryid 更改时，在输出中注入一个新的国家/地区标头。

推荐阅读