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?

Answer 1

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);