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, but that didn't return anything. — Steef
– Steef, Commented Sep 1, 2013 at 13:21
then you have to assemble your array manually. it is not that hard. — Your Common Sense
– Your Common Sense, Commented Sep 1, 2013 at 13:54

Steef · Accepted Answer · 2013-09-01 22:27:19Z

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

Collectives™ on Stack Overflow

Put MySQL data in a table using a function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related