CODEWITHSUNDEEP
phpmysql
Sour Jack
Sour Jack

Reputation: 71

Mysql search using multiple and varying combinations of values

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

Upvotes: 0

Views: 199

Answers (1)

Sour Jack
Sour Jack

Reputation: 71

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!

Upvotes: 2

Related Questions