Inserting multiple rows in MySQL

Question

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($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 => $expressions){
                $recur = $this->parseWhereClause($expressions, ++$iteration, $id++, $c);
                $sql[] = $recur['sql'];
                $data += $recur['data'];
            }

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

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

This method will only run one large query.

Option two

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

while($input['quantity']){
    $db->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 > 0 DO
   INSERT INTO users_foods (`user_id`, `food_id`) VALUES (user_id, food_id);
   SET quantity = quantity - 1;
 END WHILE;

END

PHP

$db->prepare('CALL buy_food(?, ?, ?)')->execute(array(

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

));

Which one do you think is the best way to go and why?

Answer 1

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;

Answer 2

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.

Who said you had to insert multiple rows?

If I buy 10 apples, that's one row: user_id = 42, food_id = 103, quantity = 10.

Option one

You are creating a method to produce a very long SQL statement. This makes it very very very hard to follow. Avoid such methods! It will make it very confusing for Mr. Maintainer to understand what it actually does. Additionally, you are not using parametrized SQL correctly (which would be even more complicated, although possible, to use there). Just because you prepare a statement doesn't make it safe.

A concatenated prepared SQL statement is just as dangerous as a non-prepared one. It is the parametrization that can make it safe(r). It is hard to tell for me if this version is really vulnerable to SQL, because it is hard to keep track of all the concatenations. Either way, this is the worst in my opinion.

Option one and two

If quantity is negative, these loops will never end.

Option three

Sure, this one would work fine as it is now I guess. However, this is not right as Option four (introduced by 200_success) is much better. And in my opinion, it would be overkill to use a SQL procedure for this (others might disagree).

---

I think currently, option two is the best, but it is doing the wrong thing.

Loop through all the kinds of items I am buying instead of looping over all the 10 apples.

It is much easier (requires less computing power and less work by the database) to loop over an array of (apples, pears, bananas) (3 times) than to loop 10 times for apples, 7 times for pears, and 30 times for bananas. _{(I like bananas, OK? And I do plan to share some...)}