MySQLi not returning results from a JOIN prepared statement

Question

function fullJoinTest()
{
    $con = ModelBase::getConnection();
    $sql = "SELECT airline, airport
            FROM airlines
            LEFT JOIN airports on airlines.icao_code = airports.icao_code";
    $query = $con->prepare($sql) or die("Error preparing sql in Search (test) ");

    $query->execute() or die("Error executing query in Search (test) ");

    error_log($query->num_rows);
}

Now it keeps returning 0 for the number of rows it's returning. When I plug that same query into phpMyAdmin it returns the expected result set.

Does MySQLi play nice with JOINs? Is there another way to go around this? Eventually it will have a lot more joins and some WHERE clauses. I would like to keep using prepared statements.

Answer 1

You have not yet read the rows, so it doesn't know how many were produced. call $query->store_result(); to buffer the rows into memory.

http://www.php.net/manual/en/mysqli-stmt.num-rows.php

http://www.php.net/manual/en/mysqli-stmt.store-result.php