Query database with text field and checkboxes.

[animone]

HI all.

 

I've passed entire days searching on internet and trying to find a solution on myself, but with no success.

 

So now i'm here to ask for your help.

 

I have a simple search form with a text field (city)and 4 checkboxes (club, school, shop, tour). THey are all also  fields of a database .

 

A user inserts a city and then he/she checks one or more checkboxes to search for what h/she wants.

 

I cannot figure out how to build a select statement to solve this problem. I've combined several AND / OR statements but nothing.

 

I want to specify that for th checkboxes i have created 4 different fields in the database. Is this ok? Or should i have to create just one text field and use the IMPLODE function to store and EXPLODE to retrieve data?

 

Hope i've been clear.

 

 

Thanks in advance for you help.

 

 

[WebStyles]

this is not the best way to do it, but it will serve nicely as an example. note $started and how it's used to figure out if we need to insert the word 'AND' in the statement.

 

<?php
$keyword = '%'.$_POST['city'].'%';
$query = "select * from `database` where";
if(isset($_POST['club']) && $_POST['club'] != ''){
$query .= " `club` like '$keyword'";
$started = 'yes';
}
if(isset($_POST['school']) && $_POST['school'] != ''){
if(isset($started) && $started == 'yes') $query .= ' and';
$query .= " `school` like '$keyword'";
$started = 'yes';
}
if(isset($_POST['shop']) && $_POST['shop'] != ''){
if(isset($started) && $started == 'yes') $query .= ' and';
$query .= " `shop` like '$keyword'";
$started = 'yes';
}
if(isset($_POST['tour']) && $_POST['tour'] != ''){
if(isset($started) && $started == 'yes') $query .= ' and';
$query .= " `tour` like '$keyword'";
}
?>

 

hope this helps

[animone]

this is not the best way to do it, but it will serve nicely as an example. note $started and how it's used to figure out if we need to insert the word 'AND' in the statement.

 

<?php
$keyword = '%'.$_POST['city'].'%';
$query = "select * from `database` where";
if(isset($_POST['club']) && $_POST['club'] != ''){
$query .= " `club` like '$keyword'";
$started = 'yes';
}
if(isset($_POST['school']) && $_POST['school'] != ''){
if(isset($started) && $started == 'yes') $query .= ' and';
$query .= " `school` like '$keyword'";
$started = 'yes';
}
if(isset($_POST['shop']) && $_POST['shop'] != ''){
if(isset($started) && $started == 'yes') $query .= ' and';
$query .= " `shop` like '$keyword'";
$started = 'yes';
}
if(isset($_POST['tour']) && $_POST['tour'] != ''){
if(isset($started) && $started == 'yes') $query .= ' and';
$query .= " `tour` like '$keyword'";
}
?>

 

hope this helps

 

Than you Webstyles.

 

Unlunckly i don't think this is what i'm searching for.

 

Probably my question wasn't very clear.

 

I try again.

 

Every record in the database, among the other, has a city and club, school, tour, shop fields. Club, school, tour and shop are TINYINT (1). During registration the sailing club (that's the project) can check 1 or all the checkboxes. So for every sailing club  in the database i can have a  1 (if checkbox is checked) and 0 if not.

 

As a common user let's say i want to find all the club and schools in Marseille. So i insert Marseille in the city search field and check club and school checkboxes.

 

What i expect as a result  is obviuos.

 

It seems that in your code the LIKE is used to compare tour, club, school just with the city name.

[animone]

Up

[teynon]

If I understand what you are saying, your database looks like this:

 

| ID      |  TITLE                | CITY                        | SCHOOL | CLUB | TOUR | SHOP |

| 1        | My Club              | MyCity                      | 0            | 1      | 0        | 1        |

 

And you want to search for this with the checkboxes.

So your form variables look like this:

$_POST['school'] = 0;

$_POST['club'] = 1;

$_POST['tour'] = 0;

$_POST['shop'] = 1;

 

Your query should work like this:

if ($_POST['school']==1) {
   $addOn[]="`SCHOOL` = 1";
}
if ($_POST['club']==1) {
   $addOn[]="`CLUB` = 1";
}
if ($_POST['tour']==1) {
   $addOn="`TOUR` = 1";
}
if ($_POST['shop']==1) {
   $addOn="`SHOP` = 1";
}

$addString=implode("AND", $addOn);
$sql="SELECT FROM myTABLE WHERE ".$addString;

I wouldn't do it like this, personally. I would design my database a bit differently.

[animone]

If I understand what you are saying, your database looks like this:

 

| ID      |  TITLE                | CITY                        | SCHOOL | CLUB | TOUR | SHOP |

| 1        | My Club              | MyCity                      | 0            | 1      | 0        | 1        |

 

And you want to search for this with the checkboxes.

So your form variables look like this:

$_POST['school'] = 0;

$_POST['club'] = 1;

$_POST['tour'] = 0;

$_POST['shop'] = 1;

 

Your query should work like this:

if ($_POST['school']==1) {
   $addOn[]="`SCHOOL` = 1";
}
if ($_POST['club']==1) {
   $addOn[]="`CLUB` = 1";
}
if ($_POST['tour']==1) {
   $addOn="`TOUR` = 1";
}
if ($_POST['shop']==1) {
   $addOn="`SHOP` = 1";
}

$addString=implode("AND", $addOn);
$sql="SELECT FROM myTABLE WHERE ".$addString;

I wouldn't do it like this, personally. I would design my database a bit differently.

 

Thanks Teynon.

 

Even in your select statement i don't see the city.

 

The user first insert  a city and then checks the checkboxes.

 

So the final result should be similar to this:

 

SELECT from myTable where city = $city and the checkboxes checked here.

 

But i don't know how to do it.

 

Also. you say you desgin the database a little differently. Do you mean you would create a separate table for the checkboxes and then create a relation with the sailing centers table?

 

Thanks again.