Query a database with text field and checkboxes

First 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.

The website is about sailing centers.

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 .

Table1 - sailingcenters

------+----------+----------+--------+--------+---------+-------

 id    name     city     club     school    shop     tour

------+----------+----------+--------+--------+---------+--------

   1      Sail1       City1     1         0        0        1

   2      Sail2       City2     1         1        0        0

   3      Sail3       City3     0         1        1        0 

Let's say i want to show all the clubs and schools in Marseille.

So i type Marseille in the city field and check CLUB and SCHOOL checkboxes. (I think it's clear that all those information have been inserted by the sailing centers during registration)

I want to show all the sailing centers that are both CLUB and Schools, but also just schools or clubs.

This is the key point. I sailing center could be both, or just club or school.

I cannot figure 开发者_运维百科out how to build a select statement to solve this problem.

The point is that i have to combine the city and the checkboxes in the select statement. That 's what i'm not able to do.

I want to specify that for the 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.

---

The OR should be parenthesized, I suppose.

SELECT
  name
FROM 
  sailingcenters
WHERE 
  city = 'Marseille'
  AND
  (club = 1 OR school = 1)

---

SELECT
  name
FROM 
  sailingcenters
WHERE 
  (club = 1 OR school = 1)
  AND city = 'Marseille'

EDIT: Build your query like this:

$query = "SELECT * FROM sailingcenters WHERE city = 'Marseille' AND (0=1";
if ($_POST['club']=='ON')   $query .= ' OR club   = 1 ';
if ($_POST['school']=='ON') $query .= ' OR school = 1 ';
if ($_POST['shop']=='ON')   $query .= ' OR shop   = 1 ';
if ($_POST['tour']=='ON')   $query .= ' OR tour   = 1 ';
$query .= ')';

---

Trivial html -> PHP -> SQL statement example

<form method=post>
<input type=checkbox name=club />

sends to a postback form handler which includes:

$and_clause = "AND ";

// for each of your checkboxes do:
if(isset($_POST['club'])){
$and_clause .= "CLUB = 1 OR ";
}

//then last of all do
$and_clause .= rtrim(" OR ", $and_clause);

Then add $and_clause to your sql statement

$sql = "SELECT
  name
FROM 
  sailingcenters
WHERE 
  city = 'Marseille' $and_clause";

The building of the $and_clause can be automated somewhat, checking values off against a white list then UPPERCASE them to concatenate the string, but the above should give you the idea - IF your question really was "how do I build the sql statement?"