PHP - bind_result for an array

Question

I use a prepared statement for a query that returns several results that I would like to have in an array. But bind_result will not work with arrays, so here 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 a clearer / better / more elegant way to put the results in the $ item_id and $ item_name arrays?

As you can see from the above, I use $ var1 and $ var2 as “intermediate” - and constantly feel that there should be a better way.

Thanks.

Answer 1

I can’t take advantage of this, but the PHP manual has a nice solution (inserted 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); } 

Answer 2

I really don’t see any reason to put your element names and element names in different arrays, do you split one object in two collections for ??? no reason. Yes.

So, back to this, what you should do:

 $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 that you can immediately start working on it, regardless of the request, here mine work in several digits, but not all of them are relevant 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); } 

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