Inserting multiple rows in MySQL

I am building a store. When a user decides to make a purchase I need to store it in the database, but since the purchase might be of more than 1 item I'm wondering which way is the best to insert multiple rows, here are my 3 options.

I use a database object that combines PDO with PDOStatement, which is why I don't define $stmt anywhere.

The input is:

$input = array(
    'user_id' => 15,
    'food_id' => 2,
    'quantity' => 15
);

The table I'm inserting into:

CREATE TABLE `users_foods` (
 `user_id` int(10) unsigned NOT NULL,
 `food_id` int(10) unsigned NOT NULL,
 KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Option one

$insert_data = array();

while($input['quantity']){
    $insert_data[] = array($input['user_id'], $input['food_id']);
    $input['quantity']--;
}

$db->insert('users_foods', array('user_id', 'food_id'), $insert_data);

Respectively used functions are

```
public function insert($table, array $columns, array $values){
$valArr = array();
$data = array();

$id = 0;
if(!empty($values)){
if(is_array($values[0])){
foreach($values as $k => $v){
foreach($v as $key => $val){
$data[':i'.$id] = $val;
$v[$key] = ':i'.$id;
$id++;
}
$valArr[] = '('.join(',', $v).')';
}
}else{
foreach($values as $key => $val){
$data[':i'.$id] = $val;
$v[$key] = ':i'.$id;
$id++;
}
$valArr[] = '('.join(',', $v).')';
}
}

return $this->prepare('INSERT INTO '.$table.' ('.join(',', $columns).') VALUES '.join(',', $valArr))->execute($data);
}

private function parseWhereClause(array $where, $iteration = 0, $id = 0, $sd = null){
$sql = array();
$data = array();

if(count($where) == 3 && !is_array(reset($where))){
if(is_array($where[2])){
foreach($whe

It is a maxim in computer science that there are really "only three numbers": zero, one, and many. You have a case of many, and you should treat it as such. Otherwise, someday a customer might want to buy a hundred of some food item, and performance will be poor.

Therefore, I recommend…

Option four

Instead of having the table represent current supply levels, keep a transaction log, where the quantity column stores purchases as positive values and consumption as negative values.

INSERT INTO user_food_purchase (user_id, food_id, quantity) VALUES (?, ?, ?);

Then, adjust the rest of your application to accommodate the schema change. For compatibility, you could define a view:

CREATE VIEW user_food AS
SELECT user_id, food_id, SUM(quantity) AS quantity
    FROM user_food_purchase
    GROUP BY user_id, food_id;

StackExchange Code Review Q#63607, answer score: 12