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' =&gt; 15,
    'food_id' =&gt; 2,
    'quantity' =&gt; 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-&gt;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 =&gt; $v){
                foreach($v as $key =&gt; $val){
                    $data[':i'.$id] = $val;
                    $v[$key] = ':i'.$id;
                    $id++;
                }
                $valArr[] = '('.join(',', $v).')';
            }
        }else{
            foreach($values as $key =&gt; $val){
                $data[':i'.$id] = $val;
                $v[$key] = ':i'.$id;
                $id++;
            }
            $valArr[] = '('.join(',', $v).')';
        }
    }

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

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

    if(count($where) == 3 &amp;&amp; !is_array(reset($where))){
        if(is_array($where[2])){
            foreach($where[2] as $expr){
                $sql[] = ':w'.$iteration.$id;
                $data[':w'.$iteration.$id++] = $expr;
            }
            switch(strtolower($where[1])){
                case 'in': $sql = $where[0] . ' ' . $where[1] . ' ('.join(',', $sql).')'; break;
                case 'between': $sql = $where[0] . ' ' . $where[1] . ' '.join(' AND ', $sql); break;
            }
        }else{
            $sql = $where[0] . ' ' . $where[1] . ' :w'.$iteration.$id;
            $data[':w'.$iteration.$id] = $where[2];
        }
    }else{
        if(!empty($where)){
            $id = 0;

            foreach($where as $c =&gt; $expressions){
                $recur = $this-&gt;parseWhereClause($expressions, ++$iteration, $id++, $c);
                $sql[] = $recur['sql'];
                $data += $recur['data'];
            }

            $sql = '('.join(' '.$sd.' ', $sql).')';
        }else{
            $sql = '1';
        }
    }

    return array(
        'sql' =&gt; $sql,
        'data' =&gt; $data
    );
}


This method will only run one large query.

Option two

$db-&gt;prepare('INSERT INTO users_foods (user_id, food_id) VALUES (?, ?)');

while($input['quantity']){
    $db-&gt;execute(array($input['user_id'], $input['food_id']));
    $input['quantity']--;
}


not quite sure how this prepare-execute thing works under the hood though, but I believe that this will run 15 queries (because quantity is 15 in the example).

Option three

MySQL

CREATE PROCEDURE `buy_food`(IN `user_id` INT UNSIGNED, IN `food_id` INT UNSIGNED, IN `quantity` INT UNSIGNED)
BEGIN

 WHILE quantity &gt; 0 DO
   INSERT INTO users_foods (`user_id`, `food_id`) VALUES (user_id, food_id);
   SET quantity = quantity - 1;
 END WHILE;

END


PHP

$db-&gt;prepare('CALL buy_food(?, ?, ?)')-&gt;execute(array(

    $input['user_id'], 
    $input['food_id'], 
    $input['quantity']

));


Which one do you think is the best way to go and why?
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;