MySQL Check List and Check String & Forms

[KGodel]

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]

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)) {

} 


[KGodel]

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.

[Dave]

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.

[Scanu]

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

[KGodel]

Originally Posted by Dave

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?

Originally Posted by Scanu

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?

[Dave]

Originally Posted by KGodel

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]

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.

[kh99]

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]

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?