vb.org Archive - MySQL Check List and Check String & Forms

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

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

- - MySQL Check List and Check String & Forms (https://vborg.vbsupport.ru/showthread.php?t=313252)

MySQL Check List and Check String & Forms

Hey all! I am trying to figure out if there is an easy way to check if a value is in a (MySQL) table column which saves values as a comma separated list. So for example, if the column name is "favorites", and for some user favorites = "54,23,12", in a query can I select only the rows that have 23 in their "favorites" column?

Another issue I need assistance with is trying to find if a number is within a list of numbers separated by commas. So let's say I have a list of numbers "52,13,2,5". I want to see if 2 is in this list. I cant convert to strings because if I used a string operator and search for 2 it would show as true or give me the the place where it found the 2 in 52. Any help would be great!

Assuming you already know how to get that value from the database and you habe it in a variable for example $favorites you can use this code

PHP Code:


		
			
If (strpos($favorites, 23) !== FALSE) { // check if 23 is in favorites
//your code
}

Or also this (more complicated)

PHP Code:


		
			
If (in_array(23, explode(",",$favorites)) {

}

If in your first example I replaced 23 with 2, wouldn't it give me the the place where it shows 2 in 52 before it check if there is 2 alone?

Also, in my first question I wanted to do this in MySQL without having to run another query on data I already retrieved.

You can use LIKE in a WHERE statement like:

PHP Code:


		
			
WHERE favorites LIKE '23,%' OR favorites LIKE '%,23,%' OR favorites LIKE '%,23'

There might be an easier way to do this, but not aware of that at the moment.

Then you need the second one, i couldn't think of an easier way to do it right now

Quote:

Originally Posted by Dave (Post 2508395)

You can use LIKE in a WHERE statement like:

PHP Code:


		
			
WHERE favorites LIKE '23,%' OR favorites LIKE '%,23,%' OR favorites LIKE '%,23'

There might be an easier way to do this, but not aware of that at the moment.

Can I only use one LIKE and group the ORs in parens just like you would for WHERE?

Quote:

Originally Posted by Scanu (Post 2508396)

Then you need the second one, i couldn't think of an easier way to do it right now

If the list only has 1 number and I use explode with the comma, will it still create an array with 1 value?

Quote:

Originally Posted by KGodel (Post 2508397)

Can I only use one LIKE and group the ORs in parens just like you would for WHERE?

If the list only has 1 number and I use explode with the comma, will it still create an array with 1 value?

You can't use 1 LIKE because you want to check if the number is at the begin, end or middle of the column value.
And second question: yes.

Thanks for your help guys. I'm learning more and more every day! I'm already designing my own plugins and tools. If I figure out how to export them and do the whole process to make them their own thing I might be able to release one! :)

--------------- Added [DATE]1406422797[/DATE] at [TIME]1406422797[/TIME] ---------------

figured I'd keep this one to ask again.

I am creating a form in the ACP to update a table one of my forum systems uses. I successfully get the correct info when I click the edit button, but the form that shows up is not selecting the proper options for my selection items even though I have passed the info one. Code below.

PHP Code:


		
			
    $ugame = mysql_fetch_row($result);
    
    // print_r(array_values($ugame));
    
    print_form_header($this_script, 'update');
    print_table_header('Update Game');
    // print_select_row('Main Game', 'mgame', $currentgames, $ugame[1]);
    echo "<input type='hidden' name='game' value='$egame'>";
    print_input_row('Abbreviation', 'abbrev', $ugame[2]);
    print_input_row('Account Name', 'acctname', $ugame[3]);
    print_select_row('Account Profile Field', 'proffield', $fields, (string)$ugame[4]);
    print_select_row('Game Status', 'status', $gamestatuses, (string)$ugame[5]);
    print_submit_row('Update Game');

Figured it out. Contrary to what vBulletin documentation says it does NOT want a string to determine the selected option, it wants what corresponds to the VALUE of that list item.

There's a mysql function called FIND_IN_SET that might work. You'd have something like

Code:

WHERE FIND_IN_SET('23', favorites) != NULL

but I haven't actually tried it.

Just an observation...don't kill me for it...but storing comma values always kinda sends the alarms off. It's breaking First Normal Form which, if I were just learning, I'd want to do as little as possible.

Are you sure there isn't a better way to store your data?

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (5)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01017 seconds Memory Usage 1,756KB 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 (1)bbcode_code_printable (5)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: