View Full Version : MySQL Check List and Check String & Forms
KGodel
07-26-2014, 08:24 PM
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!
Scanu
07-26-2014, 08:37 PM
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
If (strpos($favorites, 23) !== FALSE) { // check if 23 is in favorites
//your code
}
Or also this (more complicated)
If (in_array(23, explode(",",$favorites)) {
}
KGodel
07-26-2014, 08:40 PM
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:
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.
Scanu
07-26-2014, 08:42 PM
Then you need the second one, i couldn't think of an easier way to do it right now
KGodel
07-26-2014, 08:43 PM
You can use LIKE in a WHERE statement like:
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?
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?
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.
KGodel
07-26-2014, 08:47 PM
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 1406422797 at 1406422797 ---------------
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.
$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
WHERE FIND_IN_SET('23', favorites) != NULL
but I haven't actually tried it.
Dead Eddie
07-28-2014, 02:43 AM
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?
KGodel
07-28-2014, 04:15 AM
This is how the data is stored in vBulletin for secondary usergroups, which is what I am checking.
vBulletin® v3.8.12 by vBS, Copyright ©2000-2024, vBulletin Solutions Inc.