using bind_param with a subquery

hello all (first time posting for this newbie)

lets say i have a query that looks something like this:

SELECT whatever FROM myColors WHERE color IN ('red', 'green', blue', 'purple')

i would like to be able to use bind_param to access this query, something like:

sql: SELECT whatever FROM myColors WHERE color IN (?)
$my_color_array = ('red', 'green', blue', 'purple');
$my_statement->bind_param('s' , $my_color_array );

but i am unable to determine what "syntax" the bind_param needs to pass an array as a single value into the subquery "IN" statement.

any ideas?

i did get it to work this way, but it seems pretty low-tech since the array size cannot vary:
sql: SELECT whatever FROM myColors WHERE color IN (?,?,?,?)
$my_statement->bind_param('ssss' , $my_color_array[0],$my_color_array[1], $my_color_array[2], $my_color_array[3] );


thanks in advance,
mark

 

Basically what it comes down to is using call_user_func_array along the lines of this. (taken verbatim from recent project)

// $sql is the mysqli object instance
// $c->catgory_descendants is an array of items I want to use within the IN() clause
// $c->max_subs is the number of items in the array

		array_unshift($c->category->descendants, str_repeat('i', $c->max_subs));
		call_user_func_array(array($sql, 'bind_param'), $c->category->descendants);

Code (php):

A recent change in the MySQLi bind_param method complicates things though, the first argument to the method is a string full of data type tokens, but the subsequent arguments need to be references.

I work around this by creating a temporary array specifically for the purpose of passing to call_user_func_array, then loop through my real array and add items by reference to my temp array that are pointing back to the items in my real array. So by the time that unshift happens, the rest of the array it's prepending the string to is already full of references.

$real = array('one', 'two', 'three');
$temp = array();
foreach($real as $key => $val)
{
  $temp[] =& $real[$key];
}

Code (markup):

 

thank you joebert - but will this allow for an array that might be two today but five tomorrow?

also, suppose my query looked like this:
sql: SELECT whatever FROM myColors WHERE color IN (?) AND NAME = ?
$my_color_array = ('red', 'green', blue', 'purple', 'orange');
$my_statement->bind_param('ss' , $my_color_array, "marky" );

in other words, the array was NOT the last parameter?

thanks, mark

 

It supports a variable number of arguments for the IN() clause.

I started to confuse myself when I went to explain it. It's been awhile since I wrote this.
So instead, I'll just post the whole section of the script that does it.

<?php

	$c = new stdClass;
	$c->categories = array();
	$c->max_subs = 0;
	
	$result = $db->query('SELECT category_id, label, description FROM ' . CATEGORIES_TABLE . ' WHERE soi = TRUE', MYSQLI_USE_RESULT);
	while($row = $result->fetch_object())
	{
		$row->descendants	= array();
		foreach($category_lineage[$row->category_id]->descendants as $c->descendant)
		{
			$row->descendants[]	=& $c->descendant;
		}
		$c->categories[$row->category_id]	= $row;
	}
	$result->close();
	
	foreach($c->categories as &$c->category)
	{
		$c->max_subs = max($c->max_subs, count($c->category->descendants));
	}
	$c->max_subs++;
	
	foreach($c->categories as &$c->category)
	{
		$c->category->descendants[]	=& $c->category->category_id;
		for($i = count($c->category->descendants); $i < $c->max_subs; $i++)
		{
			$c->category->descendants[]	=& $c->category->category_id;
		}
	}

	$sql = $db->prepare('
		SELECT wp.label, wp.path, wp.wallpaper_id
			FROM ' . CAT_WP_RELATIONS_TABLE . ' rel
				LEFT JOIN ' . WALLPAPERS_TABLE . ' wp
					ON rel.wallpaper_id = wp.wallpaper_id
			WHERE rel.category_id IN (' . implode(',', array_fill(0, $c->max_subs, '?')) . ")
		ORDER BY rel.insert_order DESC LIMIT {$config->options->index->new_wallpapers->count}"
	);
	
	foreach($c->categories as &$c->category)
	{
		array_unshift($c->category->descendants, str_repeat('i', $c->max_subs));
		call_user_func_array(array($sql, 'bind_param'), $c->category->descendants);

		if($sql->execute())
		{
			
		}
	}
	unset($c);

?>

PHP:

 

well, i had hoped to use the bind_param clause rather than the "prepare" clause.

 

Where are you going to use bind_param other than with a prepared statement ?

 

here is what i am doing as an example (and THANK YOU for all the help!)

<?php

$mysqli = new mysqli( constant('HOST'), constant('DBUSER'), DBPASS, DBNAME);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$colors = array('red', 'blue', 'green', 'orange');

if ($sql_statement = $mysqli->prepare(load_sql()))
{
$colorString = "'" . implode("','" , $colors ) . "'" ;
$sql_statement->bind_param
( str_repeat('s',1) ## parm counter
, $colorString #### NOT WORKING!
)
;

$sql_statement->execute();
$sql_statement->store_result();

$sql_row = array();
stmt_bind_assoc($sql_statement, $sql_row);

while ( $sql_statement->fetch() ) {
echo $sql_row['result'] ;
}

$sql_statement->close();
} else {
error_log (__FILE__ . '--' . __LINE__ . 'bad prepare statement ' . $sqlStatement );
} // end if good prepare statement

/* close connection */
$mysqli->close();

function load_sql() {
return <<<END
SELECT color "result"
FROM color_table
WHERE color IN (?)
END;
}

// http://php.net/manual/en/mysqli-stmt.fetch.php
// function written from suggestion on improvement from php cookbok
function stmt_bind_assoc (&$stmt, &$out) {
$data = mysqli_stmt_result_metadata($stmt);
$fields = array();
$out = array();
$fields[0] = $stmt;
$count = 1;
while($field = mysqli_fetch_field($data)) {
$fields[$count] = &$out[$field->name];
$count++;
}
call_user_func_array('mysqli_stmt_bind_result', $fields);
}
?>

 

I misread your last comment. I was thinking you wanted to use bind_param with something else, excluding prepare all together.

Sadly, you're not going to be able to get multiple items to work with an "IN(?)" style prepared clause. I went through the same problem before and after a lot of looking around I came up with that option from earlier where I determine the largest possible number of items needed for the IN() clause before building an IN() clause that has enough room for the maximum number of items and then pads the actual arrays of values with duplicates when they arrays are not as long as the longest array.

It seems like a lot to do, and really it is, but the alternative, which is not using a prepared statement and forcing the server to reparse the SQL statement for each round of items, is worse.

 

hey joebert - next time i am in clearwater FL you just earned yourself a free meal.

i started looking in the log files and realized the problem is that when i pass the "imploded" value, bind_param keeps wanting to guard against sql injection, so it backslashes all the single quotes.

so i decided just to do CREATE TEMPORARY table and populate it instead, than use it for the subquery. its better than nothing.

i did come across PDO over mysqli - may have some big advantages over mysqli.

thanks, mark

 

I came back to this thread to mention that trying to explain how this worked lead to me snuffing out an intermittent bug I've been trying to track down for months, a bug that only happened once, two weeks ago, in the last month. It was a duplication bug and came down to getting rid of the by-reference assignment in this piece of code from above.

 $result = $db->query('SELECT category_id, label, description FROM ' . CATEGORIES_TABLE . ' WHERE soi = TRUE', MYSQLI_USE_RESULT);
    while($row = $result->fetch_object())
    {
        $row->descendants   = array();
        foreach($category_lineage[$row->category_id]->descendants as $c->descendant)
        {
            $row->descendants[] =& $c->descendant; // dropping the byref assignment here squashed my bug
        }
        $c->categories[$row->category_id]   = $row;
    }
    $result->close();

PHP:

My first thought on PDO VS MySQLi, is that PDO would probably use the same internals as MySQLi but also carry the overhead of support for other database engines. Might be worth looking under the hood to see how they work one of these days.