CODEWITHSUNDEEP
mysqldelete-row
jQuerybeast
jQuerybeast

Reputation: 14500

MySQL: Delete from database when id first letters are numbers

I am trying to delete a row from my database and the aid which is generated from an external API contains number.

For example this are the aid's:

16RYR3w
15LSPf1
10sLK3
5PlsKs

This is what I'm trying to achieve:

$uid = 1;
$aid = '15LSPf1';

DELETE FROM favorites WHERE uid='". $uid ."' AND aid LIKE '". $aid ."'

This doesn't work. What I'm I doing wrong? I dont have a choice of changing the id because the numbers are in a random order.

Upvotes: 0

Views: 1006

Answers (4)

ahPo
ahPo

Reputation: 384

Try this:

$uid = 1;
$aid = 15;

'DELETE FROM favorites WHERE uid="'.$uid.'" AND aid REGEXP "^'.$aid.'"'

Upvotes: 0

ATs
ATs

Reputation: 105

This is what you're looking for:

$aid = stripslashes($_GET['aid']);
$query="DELETE FROM favorites WHERE uid='". $uid ."' AND aid LIKE '%". $aid ."%'";

And note that LIKE is only 10x faster than REGEXP. More on the test can be found here: http://thingsilearn.wordpress.com/2008/02/28/mysql-query-speed-regexp-vs-like/

Upvotes: 1

Kickstart
Kickstart

Reputation: 21533

Do you ever have leading zeros?

If not then possibly something like this:-

DELETE FROM favorites
WHERE CAST( i AS UNSIGNED ) > 0

EDIT - to delete a specific record what you originally have would work if the columns are both character format, but not sure why you are using a LIKE. If uid is numeric then:-

$uid = 1;
$aid = '15LSPf1';

DELETE FROM favorites WHERE uid=". $uid ." AND aid = '". $aid ."'

should workfine

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26353

If you're trying to delete any rows where the first aid character is a numeral, use a regular expression:

DELETE FROM favorites WHERE uid=whatever AND aid RLIKE '^[0-9]'

The regular expression above checks for "beginning of string" (that's the ^) followed by "any number" (that's the [0-9]).

Upvotes: 0

Related Questions