Building a Mysql Query With Check Boxes and Imploded Arrays

Question

I'm trying to build a mysql query using search for that utilizes checkboxes, dropdown menus etc. Dropdown menus and text fields work OK because they only address one value that might exist in a column, but checkboxes get tricky as they start duplicating data. For example, this is the code I currently have.

$where = array();
if(isset($_GET['cardname'])){
    if($_GET['cardname']==''){
    $card_name = mysql_real_escape_string($_GET['cardname']);
    } else{
    $card_name = mysql_real_escape_string($_GET['cardname']);
    $where[] = "card_name = '".mysql_real_escape_string($card_name)."'";
    }
}

if (isset($_GET['card_set'])){
    if($_GET['card_set']==''){

    } else {
    $set_name = $_GET['card_set'];
    $where[] = "card_set = '".mysql_real_escape_string($set_name)."'";
    }
}

//Rarity

if (isset($_GET['mythic'])){
    $mythic = $_GET['mythic'];
    $where[] = "rarity IN = '".mysql_real_escape_string($mythic);
    }

if (isset($_GET['rare'])){
    $rare = $_GET['rare'];
    $where[] ="rarity IN = '". mysql_real_escape_string($rare);
}

if(count($where)) {
    $query.= 'SELECT * FROM inventory WHERE '.implode(' AND ', $where);
}

I'm trying to build a query like the following:

SELECT * FROM inventory WHERE card_name = '$card_name' AND '$card_set' = $card_set AND rarity IN = 'r','u';

You'll notice in the sql statement 'rarity in' is checking against two values, R and U. The problems I'm having is that I can't figure out how to implode my array without writing 'rarity IN' to the sql statement for every time a checkbox is checked. When I run my code with two checkboxes checked for example I get this:

SELECT * FROM inventory WHERE card_name = 'Black ' AND card_set = '4E' AND rarity = ('M') AND rarity = ('R')

And What I really need is this

SELECT * FROM inventory WHERE card_name = 'Black ' AND card_set = '4E' AND rarity = ('M','R')

Any help would be appreciated.

Kind Regards Sour Jack

jkavalik · Accepted Answer · 2015-06-07 19:50:00Z

1

You need to store those checkboxes in different array and then use them all at once

IN does not use =, it is written as "column IN (val, val)"

//Rarity

$whereIn = array();
if (isset($_GET['mythic'])){
    $mythic = $_GET['mythic'];
    $whereIn[] = mysql_real_escape_string($mythic);
    }

if (isset($_GET['rare'])){
    $rare = $_GET['rare'];
    $whereIn[] = mysql_real_escape_string($rare);
}
if(!empty($whereIn)) {
    $where[] ="rarity IN ( '". implode("','", $whereIn) . "')'";
}

answered Jun 7, 2015 at 19:50

jkavalik

1,31611 silver badges22 bronze badges

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

1 Comment

Sour Jack Over a year ago

This was exactly what I was going for. The answer actually dawned on me a few minutes before checking this post. So happy you confirmed my line of thinking!

Collectives™ on Stack Overflow

Building a Mysql Query With Check Boxes and Imploded Arrays

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