Vfrdtyky

Trying to convert to MySQLi. I have received great help these last few days, thanks. My next problem is how to convert when LEFT JOIN is used.

Working MYSQL code

$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++;

}

Output is as expected. (Note, this website deletes the row breaks in this but they are there).

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

Broken MYSQLI conversion (followed the same logic, I thought)

$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;

        }

    }

}

Output

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

It is only picking up the LEFT JOIN values from the second table for the first value of the first table. What am I missing? I gather I might not have had an ideal solution to the working MYSQL version.

Any help greatly appreciated. Thanks.

mysql_result makes the result set available as an indexed array. OTOH mysqli_fetch_assoc retrieves a single row from the result. While you could solve the problem by moving the cursor to the start of the recordset before the inner loop:

  mysqli_result_data_seek ($mytable2, 0);

  while($row2 = mysqli_fetch_assoc($mytable2)){

this merely compounds the silliness of running a second query to retrieve data you already know. Change the first query to

 ...ORDER by countryid, cityname";

lose the second query and the inner loop. Inject a new country header in the output each time the countryid changes.