7

I am currently using the follow query: 

SELECT *
FROM `wp_usermeta`
WHERE meta_key='avatar'
AND meta_key NOT LIKE '% '
ORDER BY RAND()
LIMIT 4

In that way, I want to try to get only field values, where no empty spaces re in the file name. Where is the error in my query? It still selects filenames with empty spaces in the filename.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Lars
  • 73
  • 1
  • 1
  • 3

1 Answers1

15

Try 

NOT LIKE '% %'

Your current wildcard match only catches trailing spaces.

Also, you're using meta_key twice. Should the column used in your LIKE clause be meta_value (or whatever it is in Wordpress).

This question is probably worth reading if you're concerned about performance - Which is faster — INSTR or LIKE?

Community
  • 1
  • 1
Phil
  • 157,677
  • 23
  • 242
  • 245
  • yeah..probably the second meta_key was wrong...ist should be meta value. I tested it before with `% %`. Thanks for your help! – Lars Feb 16 '11 at 22:26
  • okay. I read it and decided for this way: 'SELECT * FROM `wp_usermeta` WHERE meta_key='avatar' AND NOT INSTR(meta_value,'% %') ORDER BY RAND() LIMIT 4' works fine! – Lars Feb 16 '11 at 22:31
  • @Lars You don't need wildcards in `INSTR`. Also, I'd steer clear of automatic boolean conversion. Be specific, use `AND INSTR(meta_value, ' ') = 0`. Don't know why you're changing from the `NOT LIKE` query as the performance is the same as `INSTR` and the query would be easier to understand – Phil Feb 16 '11 at 23:23