PHP - bind_result to array

Question

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, I'm using $var1 and $var2 like "intermediates" - and constantly have feeling there must be a better way.

Thanks.

Andy · Accepted Answer · 2011-07-04 22:54:02Z

3

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);
}

answered Jul 4, 2011 at 22:54

Andy

17.8k9 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CodeVirtuoso Over a year ago

Andy, thanks. I've also been through docs and comments, and tried the above code but it doesn't work for me. Could you illustrate how the above would apply to my concrete example? I'm rally having a bit of a hard time getting my head around this. Thanks.

Andy Over a year ago

What version of PHP are you using?

CodeVirtuoso Over a year ago

PHP 5.3 and have full control over server so I can install whatever necessary.

karanusret · Accepted Answer · 2018-11-11 19:05:23Z

1

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];
}

answered Nov 11, 2018 at 19:05

karanusret

155 bronze badges

Comments

Morg. · Accepted Answer · 2011-09-23 12:21:33Z

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++;
}

Collectives™ on Stack Overflow

PHP - bind_result to array

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related