Page 1 of 1

call_user_func_array + bind_result = pass as reference mess

Posted: Tue Nov 13, 2012 10:20 am
by KatChi
I have three types of merchandise on my site, two different kinds of records and merchandise. If a customer is shopping through merchandise and puts an item in his/her cart, the script will realize it is a t-shirt from the merch table and gather variables from the DB including size, color, price, etc. Alternatively, when a customer is looking at records, the needed variables will be title, artist, release date, etc.

Given this need, I set my script up so that it determines what type of merch a user is shopping, and then sets up variable arrays. Then this array is passed to bind_result using call_user_func_array. Since call_user_func_array requires that your args are passed as reference, I am getting stuck with an array full of references that all point to the last set of query results.

Here is the relevant code:

Code: Select all

function prodArr() {
		if (!empty($_POST['cart'])) {
			global $dbType;
			$prodDetail = array();
			$columns = array();
			$data = array();
			$img = '';
			$artist = '';
			$title = '';
			$label = '';
			$year = null;
			$color = '';
			$size = '';
			$sex = '';
			$qty = 0;
			$price = 0.00;

			//check for database type and set up $results (array) and $tbl (string) accordingly
			if ($dbType === 'distro') {
				$columns = array('img' => $img, 'artist' => $artist, 'title' => $title, 'label' => $label, 'year' => $year, 'price' => $price, 'qty' => $qty);
				$tbl = 'products';
			}
			elseif ($dbType === 'releases') {
				$columns = array('img' => $img, 'artist' => $artist, 'title' => $title, 'year' => $year, 'price' => $price, 'qty' => $qty);
				$tbl = 'products';
			}
			elseif ($dbType === 'merch') {
				$columns = array('img' => $img, 'title' => $title,'size' => $size, 'color' => $color, 'sex' => $sex, 'price' => $price, 'qty' => $qty);
				$tbl = 'merch';
			}
			//connect to database
			$mysqli = Database::getInstance();
			//Set up query
			$query = 'SELECT ';
			$query .= '`'.implode('`, `', array_keys($columns)).'`';
			$query .= ' FROM ' . $tbl . ' WHERE (`id` = ?) AND (`qty` > 0) AND (`agedOff` <> 1);';
			//query for each item in post array
			$i = 0;
			foreach ($_POST['cart'] as $key => $reqQty) {
				//$stmt = $mysqli->stmt_init();
				if (!$stmt = $mysqli->prepare($query)) {
					echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
				}
				if (!$stmt->bind_param('s', $key)) {
					echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
				}
				if (!$stmt->execute()) {
					echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
				}
				$results = array();
				foreach ($columns as $col) {
					$results[$col] = &$data[$i][$col]; //This foreach loop was recommended by a member on another forum. His idea was to create a new reference every time, but I still end up with duplicated references, and my array keys don't persist.
				}
				if (!call_user_func_array(array($stmt, 'bind_result'), $results)) {
					echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
				}
				if (!$stmt->fetch()) {
					echo "Fetching results failed: (" . $stmt->errno . ") " . $stmt->error;
	    		}
	    		$prodDetail[$key] = $results;
I have been working on this for days. Is there anyone out there who is willing to talk me through this, as opposed to just writing code?

Re: call_user_func_array + bind_result = pass as reference m

Posted: Tue Nov 13, 2012 10:43 am
by Christopher
I am confused about how you are getting the actual data:

Code: Select all

				$results = array();
				foreach ($columns as $col) {
					$results[$col] = &$data[$i][$col]; //This foreach loop was recommended by a member on another forum. His idea was to create a new reference every time, but I still end up with duplicated references, and my array keys don't persist.
				}
				if (!call_user_func_array(array($stmt, 'bind_result'), $results)) {
					echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
				}
				if (!$stmt->fetch()) {
					echo "Fetching results failed: (" . $stmt->errno . ") " . $stmt->error;
	    		}
	    		$prodDetail[$key] = $results;
Maybe I am missing something. Why not just?

Code: Select all

    $prodDetail[$key] = $result->fetch_assoc();
     $prodDetail[$key]['reqQty'] = $reqQty;

Re: call_user_func_array + bind_result = pass as reference m

Posted: Tue Nov 13, 2012 12:14 pm
by KatChi
Someone else suggested simplifying the process too, starting with my query, and pulling most everything out of the foreach loop. My query now looks like this,

Code: Select all

SELECT `img`, `artist`, `title`, `label`, `year`, `price`, `qty` FROM products WHERE `id` IN ('ccr-01-001', 'ccr-01-002', 'distro001') AND (`qty` > 0) AND (`agedOff` <> 1);
which means I do not have to bind parameters at run time, and that my query will return one result set.

I am having issues trying to figure out how to work that given my current script:

Code: Select all

if (!$stmt = $mysqli->prepare($query)) {
	echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
	echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!call_user_func_array(array($stmt, 'bind_result'), $columns)) {
	echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
}
Now what I am trying to figure out is how to handle bind_result so that I can return a variable number of these:

Code: Select all

$columns = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'label' => &$label, 'year' => &$year, 'price' => &$price, 'qty' => &$qty);
and then drop them into my product array. Any thoughts you have would be helpful.

Re: call_user_func_array + bind_result = pass as reference m

Posted: Tue Nov 13, 2012 12:15 pm
by KatChi
I should add that the $columns array above is dynamically built, so the array/value pairs they contain are variable.

Re: call_user_func_array + bind_result = pass as reference m

Posted: Tue Nov 13, 2012 4:03 pm
by KatChi
This video: http://www.youtube.com/watch?feature=pl ... 0NUexfSw#! brought it all together for me. Thank you for your help!

Re: call_user_func_array + bind_result = pass as reference m

Posted: Tue Nov 13, 2012 4:08 pm
by Christopher
KatChi wrote:Now what I am trying to figure out is how to handle bind_result so that I can return a variable number of these:

Code: Select all

$columns = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'label' => &$label, 'year' => &$year, 'price' => &$price, 'qty' => &$qty);
and then drop them into my product array. Any thoughts you have would be helpful.
If you use fetch_assoc() it will return that array to you. That is assuming that you specified those fields in your SELECT query.