如何在PHP函数中返回while循环变量中的mysqli_fetch_assoc？

Question

I have this PHP function :

function userParent($Username)
{
global $con;
$Result = mysqli_query($con, "SELECT Username FROM Family WHERE Parent = '$Username' LIMIT 10");
$row = mysqli_fetch_assoc($Result);
return $row; 
}

that function should give me 10 rows in array, but why I just have a value in array? I tried to test that codes outside function bracket and try to add WHILE loop like this :

while ($row = mysqli_fetch_assoc($Result)){
    print_r($row);
}

and it works. I got my 10 rows in array format. but it prints the result to the screen. how to make it as variable so it can be returned in function?

thanks.

UPDATE : according to Phil's answer, now here's my complete code :

<?php
function userParent(mysqli $con, $username) {
    $stmt = $con->prepare('SELECT Username FROM Family WHERE Parent = ? LIMIT 10');
    $stmt->bind_param('s', $username);
    $stmt->execute();
    $res = $stmt->get_result();
    return $res->fetch_all(MYSQLI_ASSOC);
}

$DbServer = 'localhost';
$DbUser = 'username';
$DbPassword = 'password';
$DbName = 'dbname';
$mysqli = new mysqli($DbServer, $DbUser, $DbPassword, $DbName);

$arrayParent = userParent($mysqli, 'root');
print_r($arrayParent);

?>

but I got this error message :

Fatal error: Call to undefined method mysqli_stmt::get_result() in /home/myhome/public_html/test.php on line 6

Answer 1

Try mysqli_result::fetch_all instead

function userParent(mysqli $con, $username) {
    $stmt = $con->prepare('SELECT Username FROM Family WHERE Parent = ? LIMIT 10');
    if ($stmt === false) {
        throw new Exception($con->error, $con->errno);
    }
    $stmt->bind_param('s', $username);
    $stmt->execute();
    $res = $stmt->get_result();
    return $res->fetch_all(MYSQLI_ASSOC);
}

Then call it like this

$parents = userParent($mysqli, 'some username');

Read these in case you're not aware of prepared statements and parameter binding

• http://www.php.net/manual/en/mysqli.prepare.php
• http://www.php.net/manual/en/mysqli-stmt.bind-param.php

Update

Apparently (undocumented), the mysqli_stmt::get_result() method is only available when using the mysqlnd driver. If you cannot use this driver, try this alternative

function userParent(mysqli $con, $username) {
    $stmt = $con->prepare('SELECT Username FROM Family WHERE Parent = ? LIMIT 10');
    if ($stmt === false) {
        throw new Exception($con->error, $con->errno);
    }

    $stmt->bind_param('s', $username);
    $stmt->execute();

    $parent = null;
    $parents = array();
    $stmt->bind_result($parent);
    while($stmt->fetch()) {
        $parents[] = $parent;
    }
    return $parents;
}