How to use PHP prepare with zillions of fields of different types

Question

I am inserting data that has VARCHAR, TIMESTAMP and DECIMAL kinds using prepare.

The data is already in the format needed by mySQL.

My problem is this. Suppose I had only 2 items to insert. I would do like this:

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);

My problem is the bind part. How do I do the bind when I have to insert 40 columns at once?

I can deal with the prepare part by doing this:

$sql = "INSERT INTO customers ($columns) VALUES ($values)";
$stmt = $mysqli->prepare($sql);

But the next line will result in a ridiculous long line, impossible to understand and very easy to go wrong.

 $stmt->bind_param("ssssiidisisssiidiisssidiisidi", ....);

I don't see how I could build that in a loop for example.

How do I do that?

I really don't see the issue but you could create an array with the diffrent data types and then implode the data in the bind-statement? — bestprogrammerintheworld, Apr 18 '20 at 16:26
Does this answer your question? [Mysqli - Bind results to an Array](https://stackoverflow.com/questions/24363755/mysqli-bind-results-to-an-array) — bestprogrammerintheworld, Apr 18 '20 at 16:33
@bestprogrammerintheworld nope, this answer is how to bind the result, not param. Also, binding has nothing to do with driver or performance and not using strings in the end — Your Common Sense, Apr 18 '20 at 16:42
@Space you have to be careful with ($columns) and make sure it is proof against SQL injection. Here is how ti can be done: https://phpdelusions.net/mysqli_examples/insert#array — Your Common Sense, Apr 18 '20 at 16:46

Bill Karwin · Accepted Answer · 2020-04-18T17:22:28.590

3

You can pass an array to the mysqli_stmt::bind_param() function as variable arguments with the ... syntax, introduced in PHP 5.6.

$params = ['name', 42];

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();

You don't really need to set the data type individually for each column. You can treat them all as 's'.

I know you're asking about mysqli, but I'll just point out that this is easier with PDO:

$params = ['name', 42];

$stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->execute($params);

edited Apr 18 '20 at 17:22

answered Apr 18 '20 at 16:30

Bill Karwin

538,548
86
673
828

1

It's true you don't need to set the data type invidually for it to work. As I understand it is only for optimizing performance for the driver but in the end it is using strings anyway. – bestprogrammerintheworld Apr 18 '20 at 16:32
1

If you cared about optimizing performance enough that the type of bind variables mattered, you wouldn't be using PHP. ;-) – Bill Karwin Apr 18 '20 at 16:33

How to use PHP prepare with zillions of fields of different types

1 Answers1