Mysql insert using array

Question

I have an array stored in a variable $contactid. I need to run this query to insert a row for each contact_id in the array. What is the best way to do this? Here is the query I need to run...

$contactid=$_POST['contact_id'];
$eventid=$_POST['event_id'];
$groupid=$_POST['group_id'];

mysql_query($query);
$query="INSERT INTO attendance (event_id,contact_id,group_id) VALUES ('$eventid','$contactid','$groupid')";

Answer 1

Use a foreach loop.

$query = "INSERT INTO attendance (event_id,contact_id,group_id) VALUES ";

foreach($contactid as $value)
{
    $query .= "('{$eventid}','{$value}','{$groupid}'),";
}

mysql_query(substr($query, 0, -1));

The idea here is to concatenate your query string and only make 1 query to the database, each value-set is separated by a comma

Answer 2

Since no one hasn't stated that yet, you actually cannot do this:

$query = '
INSERT INTO [Table] ([Column List])
VALUES ([Value List 1]);
INSERT INTO [Table] ([Column List])
VALUES ([Value List 2]);
';
mysql_query($query);

as this has been prevented to prevent sql injections in the mysql_query code. You cannot have semicolon within the given query param with mysql_query. With the following exception, taken from the manual comments:

The documentation claims that "multiple queries are not supported".

However, multiple queries seem to be supported. You just have to pass flag 65536 as mysql_connect's 5 parameter (client_flags). This value is defined in /usr/include/mysql/mysql_com.h:

#define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt support */

Executed with multiple queries at once, the mysql_query function will return a result only for the first query. The other queries will be executed as well, but you won't have a result for them.

That is undocumented and unsupported behaviour, however, and easily opens your code to SQL injections. What you can do with mysql_query, instead, is

$query = '
INSERT INTO [Table] ([Column List])
VALUES ([Value List 1])
     , ([Value List 2])
       [...]
     , ([Value List N])
';
mysql_query($query);

so you can actually insert multiple rows with a one query, and with one insert statement. In this answer there's a code example for it which doesn't concatenate to a string in a loop, which is better than what's suggested in this thread.

---

However, disregarding all the above, you're probably better of still to use a prepared statement, like

$stmt->prepare("INSERT INTO mytbl (fld1, fld2, fld3, fld4) VALUES(?, ?, ?, ?)");
foreach($myarray as $row)
{
    $stmt->bind_param('idsb', $row['fld1'], $row['fld2'], $row['fld3'], $row['fld4']);
    $stmt->execute();
}
$stmt->close();

Answer 3

Use something like the following. Please note that you shouldn't be using mysql_* functions anymore, and that your code is suseptible to injection.

for ($i = 0; $i < count($contactid); $i++) {
    $query="INSERT INTO attendance (event_id,contact_id,group_id) VALUES ('$eventid','$contactid[$i]','$groupid')";
    mysql_query($query);
}

Answer 4

I'm not sure running multiple queries is the best thing to do, so won't recommend making a for loop for example, that runs for each element of the array. I would rather say, make a recursive loop, that adds the new elements to a string, that then gets passed to the query. In case you can give us a short example of your DB structure and how you'd like it to look like (i.e. how the array should go into the table), I could give you an example loop syntax.

Cheers!

Answer 5

What about:

$contactIds = $_POST['contact_id'];
$eventIds = $_POST['event_id'];
$groupIds = $_POST['group_id'];

foreach($contactIds as $key => $value)
{
    $currentContactId = $value;
    $currentEventId = $eventIds[$key];
    $currentGroupId = $groupIds[$key];

    $query="INSERT INTO attendance (event_id,contact_id,group_id) VALUES ('$currentEventId','$currentContactId','$currentGroupId')";

    mysql_query($query);
}

Well, you could refactor that to insert everything in a single query, but you got the idea.