PHP - bind_result to array

I'm using a prepared statement for a query that returns multiple results, which I'd want to have in an array. But bind_result won't work with arrays, so this is how I do it:

$read_items = $db->stmt_init();
$read_items->prepare("SELECT item_id, item_name FROM items");
$read_items->execute();
$read_items->bind_result($var1, $var2);
while ($read_items->fetch()) {
  $item_id[] = $var1; 
  $item_name[] = $var2;
}

Is there clearer/better/more elegant way to put results into $item_id and $item_name arrays?

As it's visible from above, 开发者_Python百科I'm using $var1 and $var2 like "intermediates" - and constantly have feeling there must be a better way.

Thanks.

---

I can't take credit for it, but the PHP manual has a nice solution (pasted verbatim):

<?php

// blah blah...
call_user_func_array(array($mysqli_stmt_object, "bind_result"), $byref_array_for_fields);

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

$results = array();
while ($mysqli_stmt_object->fetch()) {
    // array_map will preserve keys when done here and this way
    $results[] = array_map($copy, $byref_array_for_fields);
}

---

You can use the Argument unpacking(...) operator.

$results = array(null, null);
$read_items->bind_result(...$results);
while ($read_items->fetch()) {
  $item_id[] = $result[0]; 
  $item_name[] = $result[1];
}

---

I really don't see the point of putting your item id's and item names in different arrays, you're breaking up a single object in two collections for ??? no reason . yes.

So, going back to this, what you should be doing is :

$items[$i]['var1']=$var1;

$items[$i]['var2']=$var2;

But this of course is still not very good.

The correct solution is to have a function that maps your mysqli result to an array so you can directly start working on that, whatever the query, here's mine - works in a few db's but not all are up to date at the moment :

function connectmysqldb($database,$host,$port,$user,$password){
    global $conn;
    $conn=mysqli_connect($host, $user, $password, $database,$port);
    if (!$conn){ 
        die('Error: Could not connect: ' . mysqli_connect_error()); 
    } 
    return $conn;
}

function connectpgdb($database,$host,$port,$user,$password){
    $connectString = 'host=' . $host . ' port=' . $port . ' dbname=' . $database . ' user=' . $user . ' password=' . $password; 
    $conn = pg_connect ($connectString); 
        if (!$conn) 
        { 
        die('Error: Could not connect: ' . pg_last_error()); 
        } 
    return $conn;
}

function connectmssqldb($dbname,$host,$port,$uid,$pw){
    $connectionOptions = array("UID"=>$uid,"PWD"=>$pw);
    $conn = sqlsrv_connect( $host, $connectionOptions);
    if (!$conn) 
        { 
        die('Error: Could not connect: ' . print_r( sqlsrv_errors(), true)); 
        } 
    return $conn;
}
function sqlec($query,$dbtype,$dbname,$host,$port,$uid,$pw){
    switch($dbtype){
            default:
                $res="Database Type not Recognized : $dbtype";
                break;

            case "mysql":
                global $conn;
                if(!isset($conn)){
                    $conn=connectmysqldb($dbname,$host,$port,$uid,$pw);
                }
                $clone=mysqli_multi_query($conn,$query); 
                if(!$clone){
                    die('Error:  ' . mysqli_error($conn)); 
                }else{
                    $clonearray=array();
                    $i=0;
                    if ($clone = mysqli_store_result($conn)) {
                        while ($row = mysqli_fetch_assoc($clone)){
                            $clonearray[$i]=$row;
                            $i++;
                        }
                        mysqli_free_result($clone);
                    }
                    $res=$clonearray;
                }
                break;

            case "postgres":
                $conn=connectpgdb($dbname,$host,$port,$uid,$pw);
                $clone=pg_query($conn,$query); 
                if (!$clone) 
                { 
                    die('Error:  ' . pg_last_error()); 
                }else{
                    $clonearray=array();
                    $i=0;
                    while ($row = pg_fetch_row($clone)) 
                        {
                        $count = count($row);
                        $y = 0;
                        while ($y < $count)
                            {
                            $c_row = current($row);
                            $clonearray[$i][pg_field_name($clone,$y)] = $c_row;
                            next($row);
                            $y = $y + 1;
                            }
                        $i = $i + 1;
                        }
                    pg_free_result($clone);
                    pg_close($conn);
                    $res=$clonearray;
                }
                break;

            case "mssql":
                $conn=connectmssqldb($dbname,$host,$port,$uid,$pw);
                $res=sqlsrv_query($conn,$query);
                if (!$res)
                {
                     die( "Error in statement execution.\n".print_r( sqlsrv_errors(), true));
                }else{
                    $i=0;
                    $sqlsarray=array();
                    while( $row = sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC))
                    {
                        if($i==0){
                        $arrk=array_keys($row);
                        }
                        $j=0;
                        while($j<count($arrk)){
                            $sqlsarray[$i][$arrk[$j]]=$row[$arrk[$j]];
                            $j++;
                        }
                        $i++;
                    }
                    $res=$sqlsarray;
                }
                break;
    }
    return $res;
}


function local_sqlec($query){
    global $db_server;
    global $db_db;
    global $db_port;
    global $db_username;
    global $db_password;
    return sqlec($query,"mysql",$db_db,$db_server,$db_port,$db_username,$db_password);
}           

Which I use like this :

$r=local_sqlec("SELECT test1,test2 FROM test");
$i=0;
echo "last id =".($r[0]['test1'])."<br>";
echo "Row count =".($r[0]['test2'])."<br>";

while($i<$r[0]['test2']){
    echo "inserted id =".($r[0]['test1']+$i)."<br>";
    $i++;
}