vb.org Archive - If an SQL WHERE clause equals a certain value, show image.

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - If an SQL WHERE clause equals a certain value, show image. (https://vborg.vbsupport.ru/showthread.php?t=115929)

Kirk Y

05-17-2006 09:30 PM

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

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

Code:

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

05-18-2006 05:09 AM

Quote:

Originally Posted by acidburn0520

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

Code:

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

Code:

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

05-18-2006 06:29 PM

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

hambil

05-19-2006 04:22 AM

Quote:

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

05-19-2006 05:53 PM

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

All times are GMT. The time now is 07:14 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01819 seconds Memory Usage 1,724KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (1)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (5)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: