how to insert insert array

[lukep11a]

I am currently going through the process of converting all mysql code to mysqli and having some trouble with inserting an array from a prepared statement. This is how it used to work:

 

$apointsAry = array();
$apointsAry[] = "($id, $aid, $at_points)";

$query = "INSERT INTO team_points (fixture_id, team_id, points)
VALUES ".implode(', ', $apointsAry);
$result = mysql_query($query) or die("Query: {$query}<br>Error: ".mysql_error());

 

And this is what I have got so far after converting it:

 

$stmt = $mysqli->prepare("INSERT INTO team_points (fixture_id, team_id, points)
VALUES (?, ?, ?)");
			
$stmt->bind_param("iii", $id, $aid, $at_points);
$stmt->execute();
$stmt->close();

 

 

Obviously the above code only enters one row. I have searched around but can't find how to incorporate an array with bind_param or is it not possible? Any help would be greatly appreciated.

[teynon]

You could run a foreach loop. (http://php.net/manual/en/mysqli-stmt.execute.php)

 

$query->bindParams("iii", $param1, $param2, $param3);
$query->execute();

foreach ($insertVals as $values) {
    $param1 = $values[0];
    $param2 = $values[1];
    $param3 = $values[2];
    $query->execute();
}

[AyKay47]

Fill in the missing pieces of your logic before an appropriate answer can be formulated. An example array would help.

Storing multiple values in one array element is not how arrays are meant to be implemented. Something like:

 

 

$arr = array();
$arr[] = array($id, $aid, $at_points);

 

would be a better solution.

Edited March 3, 2013 by AyKay47

[lukep11a]

Thanks to you both for your help, got it working with the code below so thanks for pointing me in the right direction:

 

$apointsAry = array();
$apointsAry[] = array($id, $aid, $at_points);

$stmt = $mysqli->prepare("INSERT INTO team_points (fixture_id, team_id, points)
VALUES (?, ?, ?)");
				
foreach ($apointsAry as $values) {
$stmt->bind_param("iii", $id, $aid, $at_points);
$id = $values[0];
$aid = $values[1];
$at_points = $values[2];
$stmt->execute();
}
$stmt->close();

[AyKay47]

Looks good, except the bind_param() call should be outside of the foreach loop.

[lukep11a]

Oh yeah, I couldn't get it to work originally so I was trying it out inside the loop, but you obviously only need to specify that once which I didn't realise at first! Thanks for pointing it out, I've moved it back now.