0

I have a PHP/MySQL set up with an associative array which I use to store values from a database, allow the user to manipulate those values and then commit any changes back to the database.

I can read and store the values in quite a neat way, but I hit a challenge when attempting to write back to the database.

$tags = array (
  array ( "tag" => "tag1", "name" => "Tag 1", "value" = > "0.1"),
  array ( "tag" => "tag2", "name" => "Tag 2", "value" = > "0.2"),
  array ( "tag" => "tag3", "name" => "Tag 3", "value" = > "0.3")
)

When I come to build the mysql insert statement, I'm successful write up until the bind_param statement.

$tagNames = implode(", ", array_column($tags, "tag"));
  // Creates a comma separated list of column names for the INSERT statement,
    e.g. "tag1, tag2, tag3"
$tagTypes = str_repeat('d', count($tags));
  // Creates a string of 'd's for the bind_param statement (I'm inserting floating point / doubles),
    e.g. "ddd" 
$tagParams = str_repeat('?,', count($tags) - 1) . '?';
  // Creates a comma separated string of '?'s for the VALUES part of the INSERT statement, ensuring the last parameter isn't followed by a comma,
    e.g. "?, ?, ?"
$tagValues = implode(", ", array_column($tags, "value"));
  // Creates a comma separated string containing the values,
    e.g. "0.1, 0.2, 0.3"

The INSERT statement looks like this:

$sql = $this->mysqli->prepare("
  INSERT INTO tableName (
    id, " . $tagNames . ")
  VALUES (?, " . $tagParams . ")");
$sql->bind_param('i' . $tagTypes, $this->gameId, $tagValues);

What I want the SQL statement to read is:

INSERT INTO tableName (
  id, tag1, tag2, tag3)
VALUES (1, 0.2, 0.2, 0.3)

What the code is actually doing is generating a string containing the values, and therefore the number of values being passed into the prepare statement doesn't match the number of parameters:

INSERT INTO tableName (
  id, tag1, tag2, tag3)
VALUES (1, "0.2, 0.2, 0.3")

How do I get a list of values into the bind_param statement rather than a string containing the list of values?

I've explored extract() and am familiar with array_column(), but can't quite figure this one out!

Dharman
  • 30,962
  • 25
  • 85
  • 135
MuppetDance
  • 144
  • 10
  • Don't you realize that all this mess is for naught, because you are adding column names directly from the user input and thus making your SQL wide open to any injection? – Your Common Sense Mar 07 '22 at 18:56
  • Thanks - I didn't find that question/answer when searching. The unpacking operator is exactly what I needed: ```...array_column($tags, "value")``` works! As for the adding of column names dynamically, this function is not directly available to user input, so is less insecure. That wasn't clear from my question, as I didn't feel that this was relevant to the question. – MuppetDance Mar 07 '22 at 19:29
  • I don't care for whatever "function". It is **column names** I am talking about which directly, according to your own words, are coming to SQL: " the user to manipulate those values and then commit any changes back to the database" – Your Common Sense Mar 07 '22 at 19:37

0 Answers0