Put MySQL data in a table using a function

Question

I have a function which should get data from a MySQL database with prepared statements and put it in an array, and I have a piece of code which should get the data from that function and put it in a table. The two pieces of code are below.

The function

function list_students($connect) {

if($stmt = mysqli_prepare($connect, "SELECT id,fullname,gender,dateofbirth,passed FROM students ORDER BY fullname ASC LIMIT 0, 20")) {
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $id, $fullname, $gender, $dateofbirth, $passed);
}

$results = array();
while(mysqli_stmt_fetch($stmt)) {
    $results['id'] = $id;
    $results['fullname'] = $fullname;
    $results['gender'] = $gender;
    $results['dateofbirth'] = $dateofbirth;
    $results['passed'] = $passed;
}

return $results;

}

The code which should get data from the function

<?php                       
$list = list_students($connect);
foreach($list as $variable) {
?>
    <tr>
        <td><? echo $variable["id"]; ?></td>
        <td><? echo $variable["fullname"]; ?></td>
        <td><? echo $variable["gender"]; ?></td>
        <td><? echo $variable["dateofbirth"]; ?></td>
        <td><? echo $variable["passed"]; ?></td>
    </tr>
<?php
}
?>

I can imagine I do something terribly wrong, because the data I get with this code is far from what's in the database. It's important to know that I have three rows in the table student, and the code above adds five rows to the table.

Can someone help me out with this, or put me in the right direction?

I hope I have explained it clear enough. If I didn't, please let me know so I can improve my question.

Cheers

EDIT:

I tried to use this, but mysqli_prepare doesn't support fetch_array as far as I know.

That didn't work, unfortunately. I've read that I need PHP 5.3.0 or higher if I want to use `get_result()`. I've tried using `bind_result` and `fetch` according to [this](http://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result), but that didn't return anything. — Steef, Sep 01 '13 at 13:21
then you have to assemble your array manually. it is not that hard. — Your Common Sense, Sep 01 '13 at 13:54

score 0 · Accepted Answer · answered Sep 01 '13 at 22:27

I got it working! I used this as my function:

function beta_get_students() {
    include("config.php");

    if($stmt = mysqli_prepare($connect, "SELECT id,fullname,gender,dateofbirth,passed FROM students ORDER BY fullname ASC LIMIT 0, 20")) {
        mysqli_stmt_execute($stmt);
        $meta = mysqli_stmt_result_metadata($stmt);
        while ($field = $meta->fetch_field()) {
            $params[] = &$row[$field->name];
        }

        call_user_func_array(array($stmt, "bind_result"), $params);

        // returns a copy of a value
        $copy = create_function('$a', 'return $a;');

        $results = array();
        while (mysqli_stmt_fetch($stmt)) {
            // array_map will preserve keys when done here and this way
            $results[] = array_map($copy, $params);
        }
        return $results;
    }
}

And this to put it in the table:

<?php
$list = beta_get_students();
foreach($list as $val) {
?>
        <tr>
            <td><a href="students.php?action=view&id=<? echo $val[0]; ?>"><? echo $val[0]; ?></a></td>
            <td><? echo $val[1]; ?></td>
            <td><? echo $val[2]; ?></td>
            <td><? echo $val[3]; ?></td>
            <td><? echo $val[4]; ?></td>
        </tr>
<?php
    }
?>

I hope anyone finds this useful and many thanks for spending time reading this.

Cheers

Put MySQL data in a table using a function

1 Answers1