How to get multiple rows from a database request

Question

This is what I have so far. It calls to the database and receives one comment and all of the values that go with it, but I want to get multiple comments with the same id. How do I set up a array to get the multiple comments?

function get_comment_by_id($lake_id) {

        global $connection;
        $query = "SELECT * ";
        $query .= "FROM comments ";
        $query .= "WHERE lakeId=" . $lake_id ." ";
        //$query .= "LIMIT 1";
        $result_set = mysql_query($query, $connection);
        confirm_query($result_set);

        // REMEMBER:
        // if no rows are returned, fetch_array will return false
        if ($comment = mysql_fetch_array($result_set)) {
            return $comment;
        } else {
            return NULL;
        }
    }

Answer 1

you just need to put it in a while loop. instead of

if ($comment = mysql_fetch_array($result_set)) {
    return $comment;
} else {
    return NULL;
}

do

$rows = array();
while($comment = mysqli_fetch_array($result_set))
    $rows[] = $comment;

return $rows; 

and you also need to use mysqli_* functions since the mysql_* functions are deprecacted.

In the calling function you should check for an empty array instead of a null value