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]

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]

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:

$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.