How to format long MySQL Insert-Statements

Question

this might be a silly question, but I struggle with it a lot.

I usually have very long MySQL Insert statements which take many arguments. The arguments are all represented by question marks. Because of that, my code gets very confusing when I have to add more arguments in the middle. (EDIT: Can't add all of the variables from the start, due to changing customer requirements)

$statement = $pdo->prepare("INSERT INTO table (Field1, Field2, Field3, Field4, Field5, Field6, ...) VALUES (?, ?, ?, 3, ?, ?, ...)");
$statement->execute([$x, $y, $z, $b, $c, ...]);

At the moment, I just count the question marks to find out where to add the variables. However, if you have 25 arguments, it takes a lot of time.

I didnt find good tips on google. Very thankful for any help!

Build the placeholders and array at the same time, or use named placeholders and have the key match the name in the array. — user3783243, Oct 14 '22 at 11:08
If you just want to add more question marks, since they're all identical you can just add one question mark at the end - there's really no need to count the question marks specifically. I can see you might need to count the items in the array you're passing to Execute, of course. A solution to that (other than the sensible one above) might be to use more meaningful variable names, e.g. to match the DB field names!! — ADyson, Oct 14 '22 at 11:12
`Can't add them from the beginning, due to customer wishes`...the order of the fields in your _INSERT_ statement is a) invisible to the customer, b) none of their business, c) nothing to do with the order in which the fields are stored in the database itself, and d) nothing to do with the order in which you could select, output or display them, either. I can't see how a customer would either know or care about this? Unless what you're actually doing is handing over this specific piece of source code to your customer to maintain it themselves afterwards, it should be completely irrelevant. — ADyson, Oct 14 '22 at 11:14
I see your example now contains a hard-coded value within the query. Presumably it's this you need to position? My advice would be...don't...just put it in a variable and pass it in as a parameter. In which case you're then back to what I said above. Or just add it to the end, because, again, as I said, it's unclear how or why any customer would actually know or care. — ADyson, Oct 14 '22 at 11:17
@ADyson I improved my formulation because it was unclear. I meant to answer that I can't create "placeholders and array at the same time", as someone suggested — ostue, Oct 14 '22 at 11:18
@ADyson I wasn't aware, that one should use variables for hard-coded values in statements. I thought it would be bad practice. If you post it as a comment I will mark it as solved. — ostue, Oct 14 '22 at 11:23
I didn't say you _should_, I said you _could_, but only to avoid the specific "counting the question marks" problem you're having here, not for any other reason. But if you notice, we've also suggested other, probably better ways to avoid that issue :-) — ADyson, Oct 14 '22 at 11:29
`I can't create "placeholders and array at the same time"`...why on earth not? It's a much neater solution - see Tim's answer below. — ADyson, Oct 14 '22 at 11:29
You also still didn't explain why or how customer requirements could possibly prevent you adding things to the start or end of the query. You seem to be placing a lot of arbitrary barriers in your own way by saying you can't or won't do things, without any meaningful explanation. — ADyson, Oct 14 '22 at 11:39

score 4 · Accepted Answer · answered Oct 14 '22 at 11:15

Well you could maintain an array of column names, and then auto generate the values clause:

$cols = ["col1", "col2", "col3"];
$values = "?" . str_repeat(", ?", count($cols) - 1);
$sql = "INSERT INTO table (" . implode(", ", $cols) . ") VALUES (" . $values . ")";
echo $sql;  // INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?)

$statement = $pdo->prepare($sql);
$statement->execute([$x, $y, $z]);

How to format long MySQL Insert-Statements

1 Answers1