MYSQL checkbox query

sorry should read

SELECT *
FROM products, details
WHERE products.prod_id = details.prod_id AND (price BETWEEN 'minprice' AND 'maxprice') AND (brand = 'brand1' OR 'brand1' = '0') AND (brand = 'brand2' OR 'brand2' = '0')
ORDER BY name, price ASC

Select allOpen in new window

Can you please explain a bit more.

What programming language are you using to fetch the values from the checkboxes?

Your sql statement is kind of meaningless, I suspect you have put strings like 'minprice' and 'maxprice' where you actually have a variable?

'brand1' = '0' is a string comparison that will allways return false. Did you mean brand1=0 ?

A full description of the tables could be usefull, execute...

SHOW CREATE TABLE products;
SHOW CREATE TABLE details;

...and show us the result.

Hello

PHP is used to fetch the values for the variables, so

$minprice = $_POST['minprice'];
$maxprice = $_POST['maxprice'];
$brand1=$_POST['sony'];
$brand2=$_POST['panasonic'];

The database has three columns of products referred to -

prod_id - smallint (5)
brand - varchar(20)
price - decimal(5,2)

The values are passed on correctly to the results page. It's just the syntax of the query that I'm trying to figure out.

so basically the checkbox has two values ie. 'sony' or '0'. If checbox is checked then value=sony so all products with that brand is returned. If the checkbox is unchecked then value=0 so no products called sony are returned.

Ok, I see.

The code below use the IN operator to check for membership in a set. It will only make $brand_string when either of sony or panasonic is selected. It can easily be expanded with more brands.

This will return only rows where brand='sony'.
  brand in ('sony')

This will return rows where brand is 'sony' OR 'panasonic'
  brand in ('sony','panasonic')

When no brand is selected, no check will be done on the brand column, and all brands will be returned.

$minprice = (float)$_POST['minprice'];
$maxprice = (float)$_POST['maxprice'];
$brand1=$_POST['sony'];
$brand2=$_POST['panasonic'];
 
$brands = array();
if($brand1=='sony') $brands[] = 'sony';
if($brand2=='panasonic') $brands[] = 'panasonic';
$brand_string = (count($brands)>0) ? 
  'AND brand IN ('.implode(',',$brands).')' : '';
 
$sql = "SELECT *
  FROM products, details
  WHERE products.prod_id = details.prod_id AND 
    (price BETWEEN $minprice AND $maxprice) $brand_string
  ORDER BY name, price ASC";

Select allOpen in new window

thanks, will try and get back to you on this!

I spotted a bug with the quoting. Replace these lines:

$brand_string = (count($brands)>0) ? 
  "AND brand IN ('".implode("','",$brands)."')" : '';

Select allOpen in new window

allanch08:

There is a much easier way to do what you're doing, but first a couple of things about PHP and how it handles checkboxes.  First, only the value of checked boxes are returned in the $_POST array.  Second, to get the values of all checkbox arrays to be returned in the $_POST array, they have to be declared as arrays in your form.  By making some simple changes you can eliminate a lot of your code, and be able to add other items to the form in the future and not having to change the code.

1) Change the checkbox names to be the category (brand):

  <input type="checkbox" name="brand[]" ... value="sony" />
  <input type="checkbox" name="brand[]" ... value="panasonic" />

2) You can now eliminate the hard coded brand searches by stepping through the 'brand' array as in the code sample below.  This means you can add brands without adding or changing the code.

ELIMINATE THIS:

$brand1=$_POST['sony'];
$brand2=$_POST['panasonic'];
 
$brands = array();
if($brand1=='sony') $brands[] = 'sony';
if($brand2=='panasonic') $brands[] = 'panasonic';
$brand_string = (count($brands)>0) ?
  'AND brand IN ('.implode(',',$brands).')' : ''

AND ADAPT THIS:

if (isset($_POST['brand']))
{
  $intListItems = count($_POST['brand']);
}
else
{
  $intListItems = 0;
}

for ($intIndex = 0; $intIndex < $intListItems; $intIndex++)
{
  Do some thing with $_POST['brand'][$intIndex] like:

  WhereClauseString = AppendToWhereClause($_POST['brand'][$intIndex]);
}

thanks aielloj. so the sql statement would stay the same just the php would be different?

thanks again. going to do some intense coding and research and get back to you

My prior post was a concept only, not finished code.  Cxr's post about sanitizing the input against SQL injection attacks should ALWYS be used whenever your dealing with data user input.  However, checking input values by hardcoding them is not the way to go.  I'm assuming that was just a concept also.  Checking the values against the database is the way to go.  Keeps the code cleaner, more efficient, and expansion easier.

I've added a placeholder for a function to check the values in the $_POST array.

if (isset($_POST['brand']))
{
  $intListItems = count($_POST['brand']);      // Get count of checked boxes.
}
else
{
  $intListItems = 0;     // No boxes checked.
}

$strWhereClauseIN = '';

for ($intIndex = 0; $intIndex < $intListItems; $intIndex++)
{
  if ($strWhereClauseIN != '')
  {
    $strWhereClauseIN = $strWhereClauseIN . ',';   // Add comma if needed.
  }

  if (BrandExists($_POST['brand'][$intIndex]))
  {
    $strWhereClauseIN = $strWhereClauseIN . "'" . $_POST['brand'][$intIndex] . '"';    // Append box value.
  }
  else
  {
    // Unknown value entered.  THIS SHOULD NEVER HAPPEN.  Suspect cxr's 'evil user' attacking, or
    // a bug in the form or PHP code.  Since there is the possibility of this being an 'evil user' (love the
    // term!!) SQL injection attack, you should probably assume everything about this post is malicious
    // and not process anything from this post.
  }
}

I'm assuming that all the brands you have checkboxes for were populated on your form based on some table in your database.  This limits the user to valid brands, and gives you a list to verify the input against.

JRA

hi guys, the brands would have been populated in the table. The brand chechboxes reflect what is available to search. I'm aware of sql injection so for $minprice which is a text field and should consist of numbers only

$minprice = preg_match('/^\d', $_POST['$minprice']);

Select allOpen in new window

ok thanks!

There should not be a $ in the key:

$minprice = (float) $_POST['minprice'];

Select allOpen in new window

thanks that was a typo on my behalf.

hi aielloj tried the code you suggested but unable to get any matching results, do you think it could be a prob with mysql statement, any help appreciated

$search = "SELECT *
FROM products, details
WHERE products.prod_id = details.prod_id AND (price BETWEEN 'minprice' AND 'maxprice') AND brand IN '$strWhereClause'
INORDER BY name, price ASC";

Select allOpen in new window

allanch08:

There's a SQL syntax error that looks like an editing error in the statement you posted.  The ORDER BY clause is types as INORDERBY.  Try removing the 'IN' from it and see if that works.

This phrase:

price BETWEEN 'minprice' AND 'maxprice'

...will never match any rows. it should be

price BETWEEN $minprice AND $maxprice

Hi guys made the suggested corrections and double checked syntax but still no joy. I'll keep debugging

$search = "SELECT *
FROM products, details
WHERE products.prod_id = details.prod_id AND (price BETWEEN '$minprice' AND '$maxprice') AND brand IN ($strWhereClause)
ORDER BY name, price ASC";

Select allOpen in new window

Hi guys! Got it to work!

Checked everything again and used

brand IN ($strWhereClause)

as the sql statement and it's all cool. Really grateful for the help cxr/AielloJ, much appreciated!