CODEWITHSUNDEEP
mysqldatabase
longlost10
longlost10

Reputation: 57

MySQL: Exclude column based on value

I have a database that has a row called shapes and contains entries with either rectangles, squares and circles. I currently have the following code to get only get the shape that the user requests. (Currently it's either rectangles and circles, squares and circles or only circles). For some reason, it's not working and throwing a database error. Can anyone help me? "MEMBER_FROM" is the column they're under

$where = "";
    if($context['no_sq'] == 1 && $context['no_rec'] == 0) {
        $where .= " WHERE MEMBER_FROM != 'Square'";
    } else if($context['no_sq'] == 1 && $context['no_rec'] == 0) {
        $where .= " WHERE MEMBER_FROM != 'Rectangle'";
    } else if($context['no_sq'] == 1 && $context['no_rec'] == 1) {
        $where .= " WHERE MEMBER_FROM != 'Square' AND MEMBER_FROM != 'Rectangle'";
    }
    $request = mysql_query("SELECT ID_SHAPES FROM {$db_prefix}shapes WHERE ID_MEMBER = {$memID}{$where}", __FILE__, __LINE__);

Upvotes: 0

Views: 149

Answers (2)

Kai Qing
Kai Qing

Reputation: 18833

You can't say WHERE twice. Use AND...

   $where = "";
        if($context['no_squ'] == 1 && $context['no_rec'] == 0) {
            $where .= " AND MEMBER_FROM != 'Square'";
        } else if($context['no_sq'] == 1 && $context['no_rec'] == 0) {
            $where .= " AND MEMBER_FROM != 'Rectangle'";
        } else if($context['no_sq'] == 1 && $context['no_rec'] == 1) {
            $where .= " AND MEMBER_FROM != 'Square' AND MEMBER_FROM != 'Rectangle'";
        } 

     $request = mysql_query("SELECT ID_SHAPES FROM {$db_prefix}shapes WHERE ID_MEMBER = {$memID}{$where}", __FILE__, __LINE__);

UPDATE: Based on your statement that your shapes are exclusion checkboxes it would make more sense to name those boxes in an array then build your query based on a loop. Otherwise this code could be very hard to maintain...

<input type="checkbox" name="exclude[]" value="Square"> Square
<input type="checkbox" name="exclude[]" value="Rectangle"> Rectangle
<input type="checkbox" name="exclude[]" value="Circle"> Circle

then in PHP

foreach($_POST['exclude'] AS $exclude)
{
    $where .= " AND MEMBER_FROM != '".$exclude."'";
}

... obviously escaping that $exclude var, but for simplicity let's assume you are escaping user input.

Upvotes: 3

Chris Trahey
Chris Trahey

Reputation: 18290

You are adding multiple WHERE clauses to your query, which is illegal. Additionally, you may like the syntax of NOT IN better in this case. Note that I also took the liberty of cleaning up your logical structure a bit.

$sql = "SELECT ID_SHAPES FROM {$db_prefix}shapes WHERE ID_MEMBER = {$memID}";
$disallowed = array();
if($context['no_squ']) $disallowed[] = 'Square';
if($context['no_rec']) $disallowed[] = 'Rectangle';
if(count($disallowed)) {
    $sql .= 'AND MEMBER_FROM NOT IN ("' . implode('", "', $disallowed) . '")';
}
$request = mysql_query($sql);

Upvotes: 0

Related Questions