Mysql search using multiple and varying combinations of values

Question

I'm further along in my quest to build a program to help me organize my Magic The Gathering collection. At this point I'm working on the advanced search. I've made a long way today, however my newest challenge is before me. Previously I was struggling with integrating check boxes in my advanced search. Thanks to a few hours of work and some generous help from all of you here at Stack Exchange (thank you, I'm truly learning a lot here) I was able to overcome that challenge. The new challenge is even, ummm challengier :) Here is what I'm up against.

In Magic the gathering, cards are categorized in multiple colors. For example, a card can be:

blue, white, red, green, black or colorless.

It can also be any combination of the above colors. So a card can can also be:

Red/Blue, Blue/Red, Blue/Red/Green, White/Black/Green, Black/White etc.

I'm using checkboxes in my search parameters to allow the user to input the color combination they will be searching for. I've Achieved queries such as this:

SELECT * FROM inventory WHERE color IN ( "R","G","B")

With this code:

//Color Array

$whereColor = array();
if (isset($_GET['white'])){
    $white = $_GET['white'];
    $whereColor[] = mysql_real_escape_string($white);
    }

if (isset($_GET['blue'])){
    $blue = $_GET['blue'];
    $whereColor[] = mysql_real_escape_string($blue);
}

if (isset($_GET['red'])){
    $red = $_GET['red'];
    $whereColor[] = mysql_real_escape_string($red);
}

if (isset($_GET['green'])){
    $green = $_GET['green'];
    $whereColor[] = mysql_real_escape_string($green);
}

if (isset($_GET['black'])){
    $black = $_GET['black'];
    $whereColor[] = mysql_real_escape_string($black);
}

if (isset($_GET['colorless'])){
    $colorless = $_GET['colorless'];
    $whereColor[] = mysql_real_escape_string($colorless);
}

//Build The Query

if(!empty($whereColor)) {
    $where[] ='color IN ( "'. implode('","', $whereColor) . '")';
}

This solution works wonderful for finding cards that are only one of any color. For example only white cards, or only blue cards, but does not work for combinations of colors. I'm trying to find a way to modify this so that if a person has selected say, blue, red for example, the search will display every card that has a combination of those colors. Card variations using those colors would be (for the sake of example):

red/blue

blue/red

red

blue

My database stores the colors in one column using letters to define the colors, and I can't change this. So for instance, the value for a Red/Green card in the database would be: RG

And the value for a Green/Red card would be: GR

And the value for a Red card would be: R

etc.

Any thoughts on this one guys? Seems pretty tough to me and as always the help is HIGHLY appreciated. I know you guys probably get tired of doing a lot of hand-holding for guys like me but I promise I put a lot of thought, research and tries into this before I reach out.

Kind Regards as always Sour Jack

Can you post your table structure/data? It would give us a clearer view. — kross
– kross, Commented Jun 8, 2015 at 1:34
@hzq.I think I figured it out and I posted my answer. Please feel free to expand upon the answer if you know a more elegant way to do it :) — Sour Jack
– Sour Jack, Commented Jun 8, 2015 at 1:41

Sour Jack · Accepted Answer · 2015-06-08 01:40:16Z

2

YAY I answered it own my own :) Here is the solution I came up with, though feel free to expand upon this if there is a better way. Basically only one piece of code needs modified. Modify the last line of code to this:

if(!empty($whereColor)) {
    $where[] ='color REGEXP "'. implode('|', $whereColor) . '"';
}

For anyone else having a similar problem, that will do it!

answered Jun 8, 2015 at 1:40

Sour Jack

718 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kross Over a year ago

You can tick your own answer to show this question is resolved. :)

Collectives™ on Stack Overflow

Mysql search using multiple and varying combinations of values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related