If an SQL WHERE clause equals a certain value, show image.

[Kirk Y]

Okay, so the SQL select query I'm executing is:

SELECT * FROM stats_new WHERE is_pot = '1' OR is_sr = '1' ORDER BY RAND() LIMIT 1

What I'm trying to accomplish here is a spot on my forum for a random clan member to be selected -- from a selection of members from both clans, SR and POT. Now, that's all working out fine and dandy -- but what I'm trying to do is place an image next to the username the sql query selects, a sort of clan tag. If the user is a POT member, it needs to show a POT image, and the same for the SR clan. I'm not sure how to go about this, other than placing the image path into an additional column, and then use another row variable to get it... but I'd think there would be an easier way. Any ideas?

[hambil]

Originally Posted by acidburn0520

Okay, so the SQL select query I'm executing is:

SELECT * FROM stats_new WHERE is_pot = '1' OR is_sr = '1' ORDER BY RAND() LIMIT 1

What I'm trying to accomplish here is a spot on my forum for a random clan member to be selected -- from a selection of members from both clans, SR and POT. Now, that's all working out fine and dandy -- but what I'm trying to do is place an image next to the username the sql query selects, a sort of clan tag. If the user is a POT member, it needs to show a POT image, and the same for the SR clan. I'm not sure how to go about this, other than placing the image path into an additional column, and then use another row variable to get it... but I'd think there would be an easier way. Any ideas?

SELECT *, CASE WHEN is_pot = '1' THEN 'image_pot' ELSE 'image_sr' END AS clan_image FROM stats_new WHERE is_pot = '1' OR is_sr = '1' ORDER BY RAND() LIMIT 1

[Kirk Y]

Thanks Hambil, would you mind explaining the code for me? I've never used CASE statements.

[hambil]

Originally Posted by acidburn0520

Thanks Hambil, would you mind explaining the code for me? I've never used CASE statements.

Well, it's pretty straight foward. All I did was add the case statement to your original query:

SELECT * FROM stats_new WHERE is_pot = '1' OR is_sr = '1' ORDER BY RAND() LIMIT 1

SELECT *, CASE WHEN is_pot = '1' THEN 'image_pot' ELSE 'image_sr' END AS clan_image FROM stats_new WHERE is_pot = '1' OR is_sr = '1' ORDER BY RAND() LIMIT 1

If I fill this in with actual values and turn it into php code it might look like this:

PHP Code:

$clan_result = $db->query_first("
    SELECT *, 
    CASE WHEN is_pot = '1' THEN '" . DIR . "/images/clan/pot.jpg
    ELSE '" . DIR . "/images/clan/sr.jpg' AS clan_image 
    FROM " . TABLE_PREFIX . "stats_new
    WHERE is_pot = '1' OR is_sr = '1'
    ORDER BY RAND() LIMIT 1
");

echo "<img src='" . $clan_result['clan_image'] . "' border = '0'/>"; 


[Kirk Y]

Ah-ha! I wasn't sure where the image variable came in -- much appreciated Hambil, thanks again.