How to Bind an Arbitrary Number of Values to a Prepared Statement in MySQLi

How to bind an arbitrary number of values to a prepared statement in mysqli?

This is exactly the scenario where mysqli is really awkward. To bind multiple params, you have to pass them all as a variable-length argument list to mysql->bind_param(), but the tricky part is that you have to bind them by reference. References in PHP can be pretty confusing.

Here's an rough example (though I have not tested this exact code):

$stmt = $mysqli->prepare("INSERT IGNORE INTO test_news 
(article, link, text_cont) VALUES (?,?,?)");
foreach ($reverse as &$value) {
$params[] = &$value;
}
array_unshift(str_repeat('s', count($params)));
call_user_func_array(array($stmt, 'bind_param'), $params);

I find it much easier to use PDO when I want to write a general-purpose function to bind parameters to SQL. No binding is necessary, just pass an array of values to the PDOStatement::execute() method.

$stmt = $pdo->prepare("INSERT IGNORE INTO test_news 
(article, link, text_cont) VALUES (?,?,?)");
$stmt->execute($reverse);

Update: if you need $items to contain multiple rows of data, I'd do it this way:

First, when building $items, make it an array of arrays, instead of concatenating the values together:

foreach ($main->find('a') as $m){
$items[] = array($m->plaintext, $m->href, $text_content);
}

Then prepare an INSERT statement that inserts one row, and loop over $items executing the prepared statement once for each tuple:

$stmt = $pdo->prepare("INSERT INTO test_news 
(article, link, text_cont) VALUES (?,?,?)");
foreach ($items as $tuple) {
$stmt->execute($tuple);
}

I don't know why you were using array_reverse() at all, and I don't know why you were using INSERT IGNORE, so I left those out.

How to bind N number of parameters using mysqli prepared statements?

Take a look at this SO Post that talks about the use of call_user_func_array with bind_param().

From the PHP Docs on mysqli_stmt_bind_param it says the following...

Note:

Care must be taken when using mysqli_stmt_bind_param() in conjunction
with call_user_func_array(). Note that mysqli_stmt_bind_param()
requires parameters to be passed by reference, whereas
call_user_func_array() can accept as a parameter a list of variables
that can represent references or values.

You'll want to use something like this

call_user_func_array(array($stmt, 'bind_param'), $terms);

and it's up to you to ensure that the correct number of ? characters appear in your SQL string $stmt.

[EDIT]

Here's a working example

// user entered search strings
$user_terms = array("a", "b", "c");

// append your wildcard "%" to all elements. you must use "&" reference on &$value
foreach ($user_terms as &$value) {
$value = '%'.$value.'%';
}

$types = "";
for($i = 0; $i<sizeof($user_terms); $i++) {
$types .= "s";
}

$terms = array_merge( array($types), $user_terms);

// the array $terms now contains: { "sss", "%a%", "%b%", "%c%" }

$sql = "SELECT ... ?,?,?" // edit your sql here

$stmt = $mysqli->prepare($sql)

call_user_func_array(array($stmt, 'bind_param'), $terms);

Binding an array in MySQLi prepared Insert statement PHP

You seem to be binding a single string as a second argument in your bind_param(). This method takes a number of variables by reference and binds them to the placeholders in the query and since you bound a single string the number of bound parameters does not match.

You need to store the values in an array and then unpack them using the splat operator.

if (count($fields) == count($values)) {
$fielddata = implode(", ", $fields);
$questions = rtrim(str_repeat("?, ", count($values)), ", ");

$statement = "INSERT INTO ".$table." (".$fielddata.") VALUES (".$questions.")";
$stmt = $db->prepare($statement);
$stmt->bind_param(str_repeat("s", count($values)), ...$values);
$stmt->execute();
}

Also, the type should be a list of letters denoting the type of each variable being bound. The best case is to bind them all as strings, so just repeat s for each bound variable.

Take care of SQL injection. You need to make sure that the field names are properly whitelisted. If these can be arbitrary values you could be vulnerable to SQL injection.

How to bind multiple parameters to MySQLi prepared statement

You can only call bind_param once, so you'll have to add all the params you want into an array, then call it via call_user_func_array.

Try this:

$params = array('');
foreach( $_POST as $name => $value ) {
$params[0] .= 'sss';
array_push($params, $id, $name, $value);
}

call_user_func_array(array($stmt, 'bind_param'), $params);

if( $stmt->execute()) {
echo '<h1>OK</h1>';
}

Build SELECT query with dynamic number of LIKE conditions as a mysqli prepared statement

The % wrapping goes around the parameters, not the placeholders.

My snippet will be using object-oriented mysqli syntax instead of the procedural syntax that your code demonstrates.

First you need to set up the necessary ingredients:

  1. the WHERE clause expressions -- to be separated by ORs
  2. the data types of your values -- your values are strings, so use "s"
  3. the parameters to be bound to the prepared statement

I am going to combine #2 and #3 into one variable for simpler "unpacking" with the splat operator (...). The data type string must be the first element, then one or more elements will represent the bound values.

As a logical inclusion, if you have no conditions in your WHERE clause, there is no benefit to using a prepared statement; just directly query the table.

Code: (100% Tested / Successful Code)

$string = "my name";

$conditions = [];
$parameters = [''];
foreach (array_unique(explode(' ', $string)) as $value) {
$conditions[] = "name LIKE ?";
$parameters[0] .= 's';
$parameters[] = "%{$value}%";
}
// $parameters now holds ['ss', '%my%', '%name%']

$query = "SELECT * FROM info";
if ($conditions) {
$stmt = $conn->prepare($query . ' WHERE ' . implode(' OR ', $conditions));
$stmt->bind_param(...$parameters);
$stmt->execute();
$result = $stmt->get_result();
} else {
$result = $conn->query($query);
}
foreach ($result as $row) {
echo "<div>{$row['name']} and whatever other columns you want</div>";
}

For anyone looking for similar dynamic querying techniques:

  • SELECT with dynamic number of values in IN()
  • INSERT dynamic number of rows with one execute() call

php mysqli repeated fields in prepared statement

Does it even work like that, typical you wont't do this '?-01-01' in a query. I haven't used Mysqli, in about 4 years, as all I use now a days is PDO. But as far as I know when you send that to prepare it's gonna puke on the ? being in a string.

I would split it, there actually is no real need to do the select because the only thing being selected is the studentID which you already have. Simply

$insert = $mysqli->prepare("Insert into studentclass (`StudentID`, `ClassID`, `Year`, `Level`, `SNo`, `TermList`, `DateStart`, `DateEnd`)VALUES(?, ?, ?, ?, ?, ?, ?, ?)");

foreach( $ids AS $id ){
$stmt->bind_param("issssiss", $id, $prev_cl,$prev_yr,$prev_lvl,'', '123456789', $prev_yr.'-01-01',$prev_yr.'-12-31');
$stmt->execute();
}

I can't test it so hopefully I got everything in the right place.

As I said I don't think you can bind to the Fields part of the query and certainly not inside a partial string, besides it's making a select that is un-needed. Just make sure to prepare the insert before the loop.

Just to clearly the only thing that select actually gets from the DB is this

 select StudentID ... from student Where StudentID in (?) 

The rest are added in as "fake" columns, I don't know the term for it. It's difficult to read the original query..

Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query

you could do it this way:

$ids = array(1,5,18,25);

// creates a string containing ?,?,?
$clause = implode(',', array_fill(0, count($ids), '?'));

$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $clause . ') ORDER BY `name`;');

call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();

// loop through results

Using this you're calling bind_param for each id and you have sorting done by mysql.



Related Topics



Leave a reply



Submit