Go Back   vb.org Archive > vBulletin 4 Discussion > vB4 Programming Discussions

Reply
 
Thread Tools Display Modes
  #1  
Old 07-26-2014, 08:24 PM
KGodel's Avatar
KGodel KGodel is offline
 
Join Date: May 2011
Location: Indiana
Posts: 332
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default 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!
Reply With Quote
  #2  
Old 07-26-2014, 08:37 PM
Scanu's Avatar
Scanu Scanu is offline
 
Join Date: Nov 2010
Posts: 829
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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($favorites23) !== FALSE) { // check if 23 is in favorites
//your code

Or also this (more complicated)
PHP Code:
If (in_array(23explode(",",$favorites)) {


Reply With Quote
Благодарность от:
KGodel
  #3  
Old 07-26-2014, 08:40 PM
KGodel's Avatar
KGodel KGodel is offline
 
Join Date: May 2011
Location: Indiana
Posts: 332
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #4  
Old 07-26-2014, 08:42 PM
Dave Dave is offline
 
Join Date: May 2010
Posts: 2,583
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #5  
Old 07-26-2014, 08:42 PM
Scanu's Avatar
Scanu Scanu is offline
 
Join Date: Nov 2010
Posts: 829
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Then you need the second one, i couldn't think of an easier way to do it right now
Reply With Quote
  #6  
Old 07-26-2014, 08:43 PM
KGodel's Avatar
KGodel KGodel is offline
 
Join Date: May 2011
Location: Indiana
Posts: 332
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by Dave View Post
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 View Post
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?
Reply With Quote
  #7  
Old 07-26-2014, 08:45 PM
Dave Dave is offline
 
Join Date: May 2010
Posts: 2,583
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by KGodel View Post
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.
Reply With Quote
Благодарность от:
KGodel
  #8  
Old 07-26-2014, 08:47 PM
KGodel's Avatar
KGodel KGodel is offline
 
Join Date: May 2011
Location: Indiana
Posts: 332
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #9  
Old 07-27-2014, 10:28 PM
kh99 kh99 is offline
 
Join Date: Aug 2009
Location: Maine
Posts: 13,185
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #10  
Old 07-28-2014, 02:43 AM
Dead Eddie's Avatar
Dead Eddie Dead Eddie is offline
 
Join Date: Apr 2004
Location: at Home...
Posts: 196
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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?
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:53 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.07606 seconds
  • Memory Usage 2,274KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)bbcode_code
  • (5)bbcode_php
  • (3)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (10)post_thanks_box
  • (2)post_thanks_box_bit
  • (10)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (2)post_thanks_postbit
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • fetch_musername
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_show_thanks_date_end
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete