CODEWITHSUNDEEP
phpmysqliprepared-statement
mcbeav
mcbeav

Reputation: 12275

Attach data in array in a php mysqli prepared statement

I am tyring to write a simple function here. The idea is to be able to call the function and display the data, but i am wondering if there is a way to stuff it into an array of some sort or something so that i can style the results when the function is called. the function is as follows:

function getDBH() {
    static $DBH = null;
    if (is_null($DBH)) {
        $DBH = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
    }
    return $DBH;
}
function selectInfo($limit, $offset){
    $DBH = getDBH();
    $stmt = $DBH->prepare("SELECT * FROM information LIMIT ?,?");
    $stmt->bind_param("ii", $limit, $offset);
    $stmt->execute();
    $stmt->bind_result($id, $name, $email);
}

Upvotes: 0

Views: 957

Answers (3)

bob-the-destroyer
bob-the-destroyer

Reputation: 3154

This replacement for your selectInfo() function should return a multi-dimensional array containing one associative array per record. It does not rely on you knowing before hand what fields are returned in the statement as that * in the select statement can otherwise easily break things if you alter your table in any way.


function selectInfo($limit, $offset) {
    $DBH = getDBH();
    $limit = (int) $limit;
    $offset= (int) $offset;
    $stmt = $DBH->prepare("SELECT * FROM information LIMIT ?,?");
    $stmt->bind_param("ii", $limit, $offset);
    $stmt->execute();
    // get all field info in returned set
    $result_metadata = $stmt->result_metadata();
    $result_fields = $result_metadata->fetch_fields();  
    $result_metadata->free_result();
    unset($result_metadata);
    $bind_array = array();
    $current_row = array();
    $all_rows = array();
    foreach($result_fields as $val) {
        // use returned field names to populate associative array
        $bind_array[$val->name] = &$current_row[$val->name];
        }
    // bind results to associative array
    call_user_func_array(array($stmt, "bind_result"), $bind_array);
    // continually fetch all records into associative array and add it to final $all_rows array
    while($stmt->fetch()) $all_rows[] = $current_row;
    return $all_rows;
    }

Edit: Changed up your selectInfo() function a bit as my previous answer was just too sloppy. print_r(selectInfo(0,10)); will print out all records with field names intact.

Upvotes: 3

Dan Grossman
Dan Grossman

Reputation: 52372

$stmt->bind_result($id, $name, $email);

while (mysqli_stmt_fetch($stmt)) {
    $row = array('id' => $id, 'name' => $name, 'email' => $email);
    $rows[] = $row;
}

return $rows;

Upvotes: 3

Explosion Pills
Explosion Pills

Reputation: 191749

Use ! instead of ? to attach static data. ? attaches quoted, escaped data. ! is unsafe, but not for limit.

Upvotes: 1

Related Questions