Getting a multi dimensional array from a mysql database

Question

Right now im using Mysqli to retrieve data from a Mysql Database, The code im using is difficult to understand and from my understanding has depreciated functions or itself in entire is depreciated. If i could get some insight and maybe some updated techniques on my Qoal of retrieving data from a mysql DB.

mysqli prepared statements

What i have so far is this:

$param = 1;
$mysqli = new mysqli("127.0.0.1", "user", "password", "databaseName");
$mysqli->query('SELECT reply_id FROM replies WHERE reply_topic = ? ORDER BY reply_date ASC');
$mysqli->bind_param('i',$param)
$mysqli->execute();
$row = bind_result_array($mysqli);

while($mysqli->fetch()){
    $set[$row['']] = getCopy($row);
}
$mysqli->free_result();
return $set;




function bind_result_array($stmt)
{
    $meta = $stmt->result_metadata();
    $result = array();
    while ($field = $meta->fetch_field())
    {
        $result[$field->name] = NULL;
        $params[] = &$result[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $params);
    return $result;
}

function getCopy($row)
{
    return array_map(create_function('$a', 'return $a;'), $row);
}

Answer 1

You need to clean up the call on the database first. $mysqli->query has send the query before you bound parameters and so on.

Replace with the following;

$stmt = $mysqli->prepare('SELECT reply_id FROM replies WHERE reply_topic = ? ORDER BY reply_date ASC');
$stmt->bind_param('i',$param)
$stmt->execute();

The $mysqli->prepare returns a new object that you should bind values to and then execute. It avoids SQL injection!

None of this is depreciated. mysqli_ is exactly what you should use.

As all you want is an array of the results, you can use the following code to do so - no user functions required.

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
        $set[] = $row['reply_id'];
}

For each row in the while, each field is saved into an array with the column name as the key. So $row['reply_id'] contains the value of the reply_id column in the database. Above I have saved this value to an array called $set, and when it loops over the next result row it saves the next value to that array too.

You can manipulate the result before or after saving it to the new array, as you see fit.

Hope this helps.