0

I am running a query from a php file, which gets its input from the user.
Each time a user buys a product, I want to insert a new row.
What my query looks like:

INSERT INTO purchases (name, price)
SELECT product.name, product.price 
       WHERE product.name 
       IN (.........)

(and, in place of the dots, a php implode is used, and it could be 

('scarf', 'jeans', 'T-shirt', 'scarf')

Now, this would be great if it did what I wanted:
When a customer buys the same product twice (say, 2 scarfs), then the IN condition only matches it once (obviously, it is an IN statement...) but I would like to match it twice. That is, if a customer buys two scarfs then I want two new rows in purchases.
Would it be possible to achieve it, without messing much with the code?
BKM
  • 6,949
  • 7
  • 30
  • 45
Nico
  • 790
  • 1
  • 8
  • 20
  • Why not just add a `quantity` field to purchases? – Denis de Bernardy Oct 30 '13 at 09:01
  • you might call this for each product separately, not for all of them at once – wxyz Oct 30 '13 at 09:01
  • @Denis No. This is just an example of a more complicated structure, and I need just what I asked... Thank you anyway! wxyz: What if they buy 10000 socks? Then I will do 10000 queries... – Nico Oct 30 '13 at 09:02
  • Try doing it like in the accepted answer for [Create an inline SQL table on the fly (for an excluding left join)](http://stackoverflow.com/questions/10709464/create-an-inline-sql-table-on-the-fly-for-an-excluding-left-join) – Klas Lindbäck Oct 30 '13 at 09:06
  • I agree with @Denis that it can be done easily by quantity field.And i think all shopping carts uses quantity for that purpose. – Mahmood Rehman Oct 30 '13 at 09:08
  • One possible way is doing two queries: first retrieve all the prices for the products you want to insert, and then form the INSERT INTO statement. Also, you might want to locally cache product info, for the sake of better efficiency. Unless your prices change in realtime every minute, of course :-} – Ashalynd Oct 30 '13 at 09:11

1 Answers1

1

What if they buy 10000 socks? Then I will do 10000 queries…

With your current approach, you'll necessarily need to do 10k queries in that case. Or 10k unions, which is pretty much equivalent:

insert into ...
select ... where product_name = ...
union all
select ... where product_name = ...
union all
...

For this reason, and per my comment, I'd highly recommend that you revisit your schema and add an apparently much needed quantity field.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • I think I like this idea, which I figured out just after I answered you on the comment, noticing that even it might be a bit slow, it is a moment when users can wait. Thank you – Nico Oct 30 '13 at 09:14
  • Thank you for the tips. In the end I just did `INSERT ... SELECT ...; INSERT ... SELECT ...; INSERT ... SELECT ...; ... ` and it just does what I wanted, but of course I wanted to know if it was possible to do it in just one query. Cheers and thanks again. PS: `UNION` and `UNION ALL` didn't work for me... MySQL syntax error – Nico Oct 30 '13 at 10:08