How To Return mysqli_fetch_assoc in While Loop As Variable in PHP Function?

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

@CORRUPT : then should I put this row : $con = mysqli_connect($DbServer, $DbUser, $DbPassword, $DbName); on each function I have? — Saint Robson
– Saint Robson, Commented Apr 19, 2013 at 4:47

BlitZ · Accepted Answer · 2013-04-19 05:02:18Z

3

Use return:

function userParent(mysqli &$dbms, $username){
    // You need to "escape" strings, which you would use in direct queries.
    // OR BETTER: use mysqli prepared statements with parameter binding.
    $username = mysqli_real_escape_string($dbms, $username);

    $result = mysqli_query($dbms, "SELECT Username FROM Family WHERE Parent = '$username' LIMIT 10");

    // Create temporary array for resultset:
    $buffer = array();

    // Fetch data to temporary buffer:
    while ($row = mysqli_fetch_assoc($result)){
        $buffer[] = $row;
    }

    // Free result set:
    $result->free();

    // Return buffer to global scope:
    return $buffer;
}

$users = userParent($con, 'John');

var_dump($users);

edited Apr 19, 2013 at 5:02

answered Apr 19, 2013 at 4:41

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Phil Over a year ago

You don't need to pass the mysqli instance by reference. Objects are implicitly passed by reference

BlitZ Over a year ago

@Phil, it might be, but I'm don't trust it. Just prefer a C++ way. Still, I know, that PHP-references are not C++ pointers. Thank you for suggestion.

Phil Over a year ago

Might want to watch out for parameters with apostrophes in them, eg O'Neil

Phil · Accepted Answer · 2013-04-19 05:34:29Z

2

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

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

edited Apr 19, 2013 at 5:34

answered Apr 19, 2013 at 4:43

Phil

166k25 gold badges265 silver badges269 bronze badges

14 Comments

Mr. Alien Over a year ago

Provide him a procedural way, as he is using mysqli_()

Phil Over a year ago

@Mr.Alien Why? It makes no difference.

Phil Over a year ago

You don't use type hints when calling a function, they are only for the function signature. I've provided a usage example

Phil Over a year ago

@RobertHanson I've updated my answer. This is why I always recommend PDO over MySQLi

Phil Over a year ago

@RobertHanson Sounds like the prepare() failed. I've updated my answer with some error checking

|

Collectives™ on Stack Overflow

How To Return mysqli_fetch_assoc in While Loop As Variable in PHP Function?

2 Answers 2

3 Comments

Update

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Update

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related