0

I've a database who contain some datas in that form:

icon(name, size, tag)
(myicon.png, 16, 'twitter')
(myicon.png, 32, 'twitter')
(myicon.png, 128, 'twitter')
(myicon.png, 256, 'twitter')
(anothericon.png, 32, 'facebook')
(anothericon.png, 128, 'facebook')
(anothericon.png, 256, 'facebook')

So as you see it, the name field is not uniq I can have multiple icons with the same name and they are separated with the size field. Now in PHP I have a query that get ONE icon set, for example :

mysql_query("SELECT * FROM icon WHERE tag='".$tag."' ORDER BY size LIMIT 0, 10");

With this example if $tag contain 'twitter' it will show ONLY the first SQL data entry with the tag 'twitter', so it will be : 

(myicon.png, 16, 'twitter')

This is what I want, but I would prefer the '128' size by default. Is this possible to tell SQL to send me only the 128 size when existing and if not another size ?

Thanks !

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Jensen
  • 121
  • 1
  • 1
  • 6

2 Answers2

1

You need an additional column to order by that puts the preferred value at the top. For example, you could do something like this:

SELECT
  * 
FROM
  icon 
WHERE 
  tag='whatever' 
ORDER BY
  case size when 128 then 1 else 0 end desc,
  size desc
LIMIT 0, 10

The new order by clause puts your preferred size first (since it's the only one it assigns a 1 to), and then orders the rest of them by their actual size, biggest first.

Donnie
  • 45,732
  • 10
  • 64
  • 86
  • Woow, I'm not sure to understand all your code. I will do some test. – Jensen May 26 '10 at 02:01
  • Hmm that didn't run. Could you explain me more pls ? – Jensen May 26 '10 at 02:03
  • In your example what's the additional column ? And what she contains ? – Jensen May 26 '10 at 02:06
  • @Jensen : I edited in the full query. Note that I didn't include any of the php code, so you'll have to wrap that in yourself. – Donnie May 26 '10 at 02:11
  • When I test these lines I have an SQL error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case desc, size desc LIMIT 0, 10' at line 8 Do I need to add a column or another modification in my SQL database ? – Jensen May 26 '10 at 02:18
  • Thanks, that's run ! Now I've a second query (the goal is the same) : $dbQueryIcons = mysql_query("SELECT * FROM pl_icon WHERE tag LIKE '%".$SEARCH_QUERY."%' ".$formSQLColor."&& size<='$formSizeMax' && size>='$formSizeMin' GROUP BY name ORDER BY id DESC LIMIT ".$firstEntry.", ".$CONFIG['icon_per_page']."") or die(mysql_error()); I want the same thing, only get the 128 (if existing) of each entry. The initial query is a little bit different than the other so how can I adapt your code with this query . Thanks – Jensen May 26 '10 at 02:35
0

ORDER BY size DESC?

brian_d
  • 11,190
  • 5
  • 47
  • 72
  • No because the 128 size is not necessarily the last data. I could have 16, 32, 128, 256 for example; so if I add DESC it would show me 256. – Jensen May 26 '10 at 02:01