chibineku Posted August 28, 2009 Share Posted August 28, 2009 I want the option of merging rows in a table. The rows might look like this:id....sessionid....userid....product_id....qty 1....abcdef............3..............1...........22......ghijkl............3..............1...........53.....mnopq...........3..............2...........3I want to merge rows with the same product id, the same user id, regardless of session id, and to have the qty column become a sum total of all the ones being merged. If possible, the most recent session id (the one that the user has currently been assigned) should survive. So, this would be the end result:id....sessionid....userid....product_id....qty 2......ghijkl............3..............1...........73.....mnopq...........3..............2...........3I don't know if this is possible, but if it is, some ideas on the sort of query necessary would be great. Thanks! Link to comment Share on other sites More sharing options...
justsomeguy Posted August 28, 2009 Share Posted August 28, 2009 That's not one query, you'll need to get all records, loop through them, and do the updates and deletes yourself. The problem with trying to do it in one query is to make sure the old rows got deleted without deleting the new rows. Link to comment Share on other sites More sharing options...
chibineku Posted August 28, 2009 Author Share Posted August 28, 2009 I've said it before and I'll say it again: egads, that's complicated. Expect more later - if nothing else, I need the occasional bit of moral support. If you see my code and don't laugh I can press on. Link to comment Share on other sites More sharing options...
justsomeguy Posted August 28, 2009 Share Posted August 28, 2009 You can make it easier by sorting on user id and product id, at least that way they'll be in order. Link to comment Share on other sites More sharing options...
chibineku Posted August 28, 2009 Author Share Posted August 28, 2009 I'm not really entirely 100% sure how sorting/ordering helps me. I've not made enough queries to have had any call for that yet. Perhaps a small example? Link to comment Share on other sites More sharing options...
justsomeguy Posted August 31, 2009 Share Posted August 31, 2009 If you're counting all records for a certain user ID/product ID, it helps them to be in order. If they aren't in order, you have to keep a list of each ID combo you've seen and keep updating it when you find a new one. If they're in order then if you find a new ID combo you know that the old one is done, and that you're starting a new total. Link to comment Share on other sites More sharing options...
chibineku Posted August 31, 2009 Author Share Posted August 31, 2009 Oh I see. Well, I have simplified the design. I've decided, with your prompting, that it isn't necessary to retain basket contents, even for users who are signed in, because unless you have such extensive inventory that people are liable to add hundreds of different items, it isn't really that great a loss, even if users suffer crashes and have to come back and re-add items to their basket. We have only got a select product range so far, very specialized. I will consider revising that and tacking on a keep-alive option later if we expand our range sufficiently or if we feel it's impacting sales. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.