Converting mysql functions to mysqli prepared statment keeping the same output

Question

I'm currently going thorough a site and replacing all the functions which used to return mysql_fectch_array() results, which are put into while loops elsewhere. I'm trying to make them return the same data in the same format but by using mysqli prepared statements output. I have been successful with the code below in producing the same formatted output for single row results.

public function get_email_settings(){
    $stmt = $this->cn->stmt_init();
    $stmt->prepare("SELECT * FROM email_setting WHERE user_id = ? LIMIT 1");
    $stmt->bind_param("i", $this->user);
    $stmt->execute();
    $stmt->bind_result(
        $row['email_id'],
        $row['user_id'],
        $row['news'],
        $row['new_message'],
        $row['new_friend'],
        $row['rule_assent'],
        $row['agreement_ready'],
        $row['agreement_all_assent'],
        $row['time_cap'],
        $row['donations']
        );
    $stmt->store_result();
    $stmt->fetch();
    $stmt->close();
    return $row;
}

But how can I get this code to work when it returns more than one row? I want it to be produce the same result as if I had written:

return mysql_fetch_array($result);

Is it possible?

Don't know why you're using `bind_result()`, try http://php.net/manual/en/mysqli-result.fetch-all.php — AbraCadaver, Aug 09 '15 at 15:32
Why are you adding `LIMIT 1`in query? Would this explain the one row result? — Parfait, Aug 09 '15 at 16:35
Thank you but I've not been able to work out exactly how to get fetch_all working. I used that example code with LIMIT 1 as a working example of how I was able to get single rows working queries working. I just cant get this to work for queries which produce more than one row as a result. — fixdreamer, Aug 09 '15 at 18:25

score 0 · Answer 1 · answered Aug 09 '15 at 16:33

Consider the following adjustment, passing query results into an associative array:

public function get_email_settings(){
    $stmt = $this->cn->stmt_init();
    $stmt->prepare("SELECT  email_id, user_id, news, new_message,
                            new_friend, rule_assent, agreement_ready,
                            agreement_all_assent, time_cap, donations 
                   FROM email_setting 
                   WHERE user_id = ? ");
    $stmt->bind_param("i", $this->user);
    $stmt->execute();

    // CREATE RETURN ARRAY
    $row = [];
    // OBTAIN QUERY RESULTS
    $result = $stmt->get_result();
    // ITERATE THROUGH RESULT ROWS INTO RETURN ARRAY
    while ($data = $stmt->fetch_assoc()) {
           $row[] = $data;
    }

    $stmt->close();
    return $row;

}

You will notice I explicitly select the query's fields to avoid an indeterminate loop through query results.

Thank you very much for your answer, unfortunately I have a plesk server and apparently it doesn't support the mysqlnd extention so I'm getting: Fatal error: Call to undefined method mysqli_stmt::get_result() so I cant use get_result() is it possible to do it without? — fixdreamer, Aug 09 '15 at 18:09

score 0 · Accepted Answer · edited May 23 '17 at 12:29

Ok I have managed to get it to work without using get_result()

This is how I did it with alot of help from Parfait and Example of how to use bind_result vs get_result

function saved_rules($user){
    $stmt = $this->cn->stmt_init();
    $stmt->prepare("SELECT R.rule_id, R.rule_title
        FROM Savedrules S
        LEFT JOIN Rule R
        ON S.saved_rule_id = R.rule_id
        WHERE S.saved_user_id = ?");
    $stmt->bind_param("i", $user);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($id, $rule_title);
    while ($stmt->fetch()) {
        $result[] = Array("rule_id"=>$id,"rule_title"=>$rule_title);
    }
    $stmt->free_result();
    $stmt->close();
    return $result;
}

Its not exactly the same output as using a mysql_fetch_array() so where it is used I have to change the loop to:

foreach($saved_rules AS $row){}

from

    while ($row = mysql_fetch_array($saved_rules){}

Converting mysql functions to mysqli prepared statment keeping the same output

2 Answers2