searls03 Posted November 6, 2012 Share Posted November 6, 2012 I would like to run this query: $sql123 = "delete from holiday_cart where cart_id='$cid' && code='$code'" ; $rs = mysql_query($sql123) or die ("Problem with the query: $sql<br>" . mysql_error()); but what I need to know how to do, is if this query matches multiple results, how can I make it so that it only deletes one of the results instead of all of them? Quote Link to comment Share on other sites More sharing options...
Jessica Posted November 6, 2012 Share Posted November 6, 2012 You should put a primary key on the tavle. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 6, 2012 Share Posted November 6, 2012 As Jessica said, you should have a unique identifier set in the table for every cart item. Without more info about the table structure, there isn't anything more we can offer in the way of help. Quote Link to comment Share on other sites More sharing options...
searls03 Posted November 6, 2012 Author Share Posted November 6, 2012 Ok, so I will add a time stamp....how can I make it so that it deletes the most recent time stamp then? Quote Link to comment Share on other sites More sharing options...
searls03 Posted November 6, 2012 Author Share Posted November 6, 2012 I know that may not be a primary key, but it would be a unique identifier Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 6, 2012 Share Posted November 6, 2012 So, you are saying that there are multiple records in that table with the same cart_id and code and you want to delete only one of those records? Â Well, based upon your initial query it is as simple as putting a LIMIT on the query DELETE FROM holiday_cart WHERE cart_id='$cid' AND code='$code' LIMIT 1 Â However, now you are saying you only want to delete the most recent matching record based upon a timestamp field. So, in addition to the ORDER BY you would also want to specify the ORDER DELETE FROM holiday_cart WHERE cart_id='$cid' AND code='$code' ORDER BY date_field DESC LIMIT 1 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 6, 2012 Share Posted November 6, 2012 if you insist on not having a UID field then you could try something along the lines of : WHERE ... AND timestampFieldName = (SELECT MAX(timestampFieldName) from tableName) where ... is your current where condition. No promises though. Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 6, 2012 Share Posted November 6, 2012 if you insist on not having a UID field then you could try something along the lines of : WHERE ... AND timestampFieldName = (SELECT MAX(timestampFieldName) from tableName) where ... is your current where condition. No promises though. Â That's not really a good solution as it is not precise. If the records with the same criteria were created at the same time it's possible that they would have the same timestamp and the above logic would delete multiple records. Besides, a sub-query isn't necessary. Quote Link to comment Share on other sites More sharing options...
searls03 Posted November 6, 2012 Author Share Posted November 6, 2012 Psycho, thank you....I only added the one field because they said I should have something unique. I will try your solution of the limit 1....that is what I want! Quote Link to comment Share on other sites More sharing options...
jcbones Posted November 6, 2012 Share Posted November 6, 2012 What about a simple id column that is auto incremented, that would be truly unique. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 7, 2012 Share Posted November 7, 2012 What about a simple id column that is auto incremented, that would be truly unique. Was kinda what Jessica and I were getting at to start with, but hey ho. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.