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...)}

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...)}