Deleting Row

[searls03]

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?

[Jessica]

You should put a primary key on the tavle.

[Muddy_Funster]

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.

[searls03]

Ok, so I will add a time stamp....how can I make it so that it deletes the most recent time stamp then?

[searls03]

I know that may not be a primary key, but it would be a unique identifier

[Psycho]

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

[Muddy_Funster]

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.

[Psycho]

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.

[searls03]

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!

[jcbones]

What about a simple id column that is auto incremented, that would be truly unique.

[Muddy_Funster]

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.