What is the proper way to return an array from a prepare statement function?

[bbmak]

Hi,

1. I got my code working, but not sure this is the proper way to return an array from a prepare statement. 

2. when I use bind_result($var), are there anyway to include all columns without typing them all?

				
                               if($next24item = $this->mysqli->prepare("SELECT  
					ci.id as item_id,
					ci.item_name,
					ci.item_description,
					ci.item_note,
					ci.item_price,
					ci.item_discount,
					ci.item_url,
					ci.date_created,
					ci.date_updated,
					ci.date_expired,
					cii.item_id as cii_item_id,
					cii.item_image_filename,
					cm.merchant_name,
					cm.merchant_url,
					cm.merchant_description,
					cm.merchant_logo_thumb
					From core_item ci 
					LEFT JOIN core_merchant cm on ci.merchant_id = cm.id
					LEFT JOIN core_item_image cii on cii.item_id = ci.id
					
					WHERE ci.id < ?
					
					ORDER BY ci.id DESC
					
					LIMIT 24"))
					
					{
						$next24item->bind_param("i", $lastItemID);
						$next24item->execute();
						$next24results = $next24item->get_result();
						return $next24results->fetch_all(MYSQLI_BOTH);
						
					}

[Jacques1]

The proper way is to replace mysqli with PDO. If that's not an option, get_result() is OK as a workaround. It does require the MySQL native driver, though (which isn't available on all systems).

 

2. when I use bind_result($var), are there anyway to include all columns without typing them all?

 

It's theoretically possible with call_user_func_array() and an array of references, but this is really ugly.

Edited March 2, 2017 by Jacques1

[Jacques1]

It's theoretically possible with call_user_func_array() and an array of references, but this is really ugly.

 

... or argument unpacking

$stmt = $databaseConnection->prepare('SELECT x, y from test');
$stmt->execute();

$number_of_cols = 2;
$results = array_fill(0, $number_of_cols, null);

$stmt->bind_result(...$results);
$stmt->fetch();

Not much better, especially since the resulting array is numerically indexed.

[bbmak]

Thank you so much for the explanations and suggestions.