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

Question

尝试转换为 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 版本的理想解决方案。

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

Answer 1

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

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

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

 ...ORDER by countryid, cityname";

丢失第二个查询和内部循环。每次国家/地区 ID 更改时，在输出中插入新的国家/地区 header 。