0

So I'm trying to code a PHP script, but we'll just leave it at the SQL part of things since this is where the issue is arising. I've a SELECT * query which should only grab from the rows where the user ID matches, and the badge ID meets their userID followed by an underscore. Although, it's grabbing results that shouldn't be included?

Here's my SQL query:

SELECT * 
FROM  `user_badges` 
WHERE  `user_id` = 1
AND  `badge_id` LIKE  '%1_%'

That should only return badges that start/contain 1_, it is grabbing all the badges that do contain/start with 1_ but it's also grabbing it215. If I search a different user ID, for example my own, it will grab all the badges with 3_ AND it's also grabbing ACH_RoomDecoFurniCount31 which is confusing because it doesn't contain 3_. Maybe there's more to it? Could someone please point me in the right direction.

Liam
  • 41
  • 1
  • 7
  • 3
    Possible duplicate of [Why does "\_" (underscore) match "-" (hyphen)?](http://stackoverflow.com/questions/8236818/why-does-underscore-match-hyphen) – baao Jan 10 '17 at 00:02
  • _"So I'm trying to code a PHP script, but we'll just leave it at the SQL part of things since this is where the issue is arising"_ Thankyou! You'd be amazed how many people don't do this. – Lightness Races in Orbit Jan 10 '17 at 00:07
  • This won't work anyway, if you can find the user ID anywhere, because `%1_%` matches `31_abc` as well as `1_abc`. Unless you fully (and unambiguously) delimit the user ID (e.g. make it "at the start only"?), you won't be able to get an unambiguous match. Reconsider your badge ID format. – Lightness Races in Orbit Jan 10 '17 at 00:09

4 Answers4

1

You need to escape the _ as it's a wildcard character. Your query would should be like this:

SELECT * 
FROM  `user_badges` 
WHERE  `user_id` = 1
AND  `badge_id` LIKE  '%1\_%'
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
0

_ is also a wildcard in SQL - A substitute for a single character

DenisS
  • 1,637
  • 19
  • 15
0

_ is also a wildcard character. It means "any single character" (whereas % is "any sequence of characters").

You could escape/quote that _ or use the LOCATE function instead of a pattern match.

WHERE badge_id LIKE  '%1\_%'

WHERE locate('1_', badge_id) > 0
Thilo
  • 257,207
  • 101
  • 511
  • 656
0

_ is a wildcard "_ matches exactly one character." so what you are saying is: % : starts with anything(or nothing) 1: contains 1 _: has exactly 1 of % (or anything, or nothing)

http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

GavinF
  • 387
  • 2
  • 15