searching multiple checkbox

[daniish]

Hello,

I have written a simple html form with checkboxes, which refer to various services on offer. I have created a MySQL database with a single table listing location names and the services available at each ('1' representing the presence of a service - otherwise NULL if the service is not present).

<form name="myform" action="search.php" method="GET">

<div align="left"><br>
<input type="checkbox" name="option" value="Electricity"> Electricity<br>
<input type="checkbox" name="option" value="Shower"> Shower<br>
<input type="checkbox" name="option" value="Toilet"> Toilet<br>
<input type="checkbox" name="option" value="Refuse-bin"> Refuse-bin<br>
<br>
<br>
<input type="submit" type="Submit">


I need a little help with the php script which will be called when the form is submitted, to return the results. Users of this form will select multiple checkboxes and i need to return the names of locations which match the services selected.

Being new to PHP i think that the script will need to create an array and then perhaps use the ISSET function to confirm which services were selected, but the ISSET function will not work directly with a mysql_query WHERE clause...perhaps i need to assign the chosen options to a variable somewhere first but i'm really not sure. I only have the following at the moment:

$query = mysql_query("SELECT name FROM facilities WHERE 'option' ISSET")
        or die ("Couln't execute query.");


// Perform Query
$result = mysql_query($query);
echo "$result";


At the moment of course it does not work, but if someone could give me a few pointers it would be very much appreciated!

[onlyican]

make the names of the options array

<input type="checkbox" name="option[]" value="Refuse-bin"> Refuse-bin

[Destruction]

A little search goes a long long way.  This topic has come up many times and one of the more fully answered threads is this one:

[url=http://www.phpfreaks.com/forums/index.php/topic,107961.0.html]http://www.phpfreaks.com/forums/index.php/topic,107961.0.html[/url]

If you still have any questions afterwards please do still ask :)

Hope this helps,

Dest

[daniish]

wow thanks very much, i will review these posts and get back to you.

Many thanks

[daniish]

Being new to PHP i am often not sure what search criteria to use - out of interest what did you use to find the above thread?

I have modified my form to take advantage of an Array as suggested by Onlyican :

[code]
<form name="myform" action="search.php" method="GET">

<div align="left"><br>

<input type="checkbox" name="option[]" value="1"> Electricity<br>
<input type="checkbox" name="option[]" value="2"> Shower<br>
<input type="checkbox" name="option[]" value="3"> Toilet<br>
<input type="checkbox" name="option[]" value="4"> Refuse-bin<br>

<br>
<input type="submit" type="Submit">
</div>

</form>
[/code]

Having had a look at the following thread : http://www.phpfreaks.com/forums/index.php/topic,107961.0.html  the IF Statement posted by HuggieBear looks the most logical to me.

[code]
<?php
$user="root";
$host="127.0.0.1";
$password="";
$database = "moor_simple";
$connection = mysql_connect($host,$user,$password)
      or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
      or die ("couldn't select the database");


$sql = "SELECT name FROM moor_facility WHERE id";
if (isset($_GET['option[1]']))(
  $sql .= "AND electricity = IS NOT NULL";
}
if (isset($_GET['option[2]']))(
  $sql .= "AND shower = IS NOT NULL";
}
if (isset($_GET['option[3]']))(
  $sql .= "AND toilet = IS NOT NULL";
}
if (isset($_GET['option[4]']))(
  $sql .= "AND bin = IS NOT NULL";
}
$result = mysql_query($sql);

echo $result <br>;

?>
[/code]


As yet it doesn't work, however, i think it might be to do with my use of IS NOT NULL. I have read that checkboxes that are not checked do not get put in the array, which seems to complicate matters. Have i understood the correct syntax ?

Thankyou for your support

[HuggieBear]

My suggestion would be to echo $sql before running the query, that way if it looks wrong you'll know it's the php as opposed to the DB.

In this instance you'd have seen your query isn't as you'd expect it, you're not referencing the variables inside the array correctly...

change:
[code=php:0]$_GET['option[1]'][/code]


to:
[code=php:0]$_GET['option'][1][/code]


Regards
Huggie

[daniish]

Thanks Huggie! Can i just confirm that the following php will work for a mysql table like the following:

[table][tr][td]
id name     electricity           toilet                      shower               bin
1     a 1               NULL                      NULL                  1
2     b 1               1                            1                        1
3     c 1               NULL                      1                        NULL
4     d NULL                     NULL                        NULL                  1

[/table]
sorry the columns don't line up very well - i hope you can make sense of it!



[code]
$sql = "SELECT name FROM moor_facility WHERE id";
if (isset($_GET['option'][1]))(
  $sql .= "AND electricity = IS NOT NULL";
}
if (isset($_GET['option'][2]))(
  $sql .= "AND shower = IS NOT NULL";
}
if (isset($_GET['option'][3]))(
  $sql .= "AND toilet = IS NOT NULL";
}
if (isset($_GET['option'][4]))(
  $sql .= "AND bin = IS NOT NULL";
}

echo $sql <br>;

$result = mysql_query($sql);

echo $result <br>;
[/code]

...on another note the "echo $..." doesn't display anything - which i guess indicates that the SQL is wrong?

Cheers

[HuggieBear]

Try this:

[code]
<?php
$sql = "SELECT name FROM moor_facility WHERE id";
if (isset($_GET['option'][1]))(
  $sql .= " AND electricity IS NOT NULL"; // I have added spaces in front of each of these values and removed the equals '='
}
if (isset($_GET['option'][2]))(
  $sql .= " AND shower IS NOT NULL"; // added a space, removed =
}
if (isset($_GET['option'][3]))(
  $sql .= " AND toilet IS NOT NULL"; // added a space, removed =
}
if (isset($_GET['option'][4]))(
  $sql .= " AND bin IS NOT NULL"; // added a space, removed =
}

echo "$sql<br>\n"; // quoted the echo string and added a newline character

$result = mysql_query($sql);

echo "$result<br>\n"; // quoted the echo string and added a newline character
?>
[/code]

You don't use = with IS NOT NULL just the column name

Regards
Huggie

[daniish]

Thanks for your answers Huggie!

Unfortunately it still wont display anything. In fact i put a little text in the HTML before your <?php  ?> and this wont display either. However, if i comment out the php then it is clearly displayed at the top of the screen.

Do you think this is something todo with why my results wont display?


Again many thanks for your time.

[HuggieBear]

In that case it would appear something else before hand is falling over.

Can you provide me with the whole code for the page that you have.

Regards
Huggie

[daniish]

The HTML form:

[code]
<html>
<head>
<title>Mooring Facilities Search Form</title>
</head>
<body>
Please select the facilities you would like at a mooring:
<form name="myform" action="search.php" method="GET">
<div align="left"><br>

<input type="checkbox" name="option[]" value="1"> Electricity<br>
<input type="checkbox" name="option[]" value="2"> Shower<br>
<input type="checkbox" name="option[]" value="3"> Toilet<br>
<input type="checkbox" name="option[]" value="4"> Refuse-bin<br><br><br>
<input type="submit" type="Submit">

</div>
</form>

</body>
</html>
[/code]

and the PHP script:

[code]
<?php
$user="root";
$host="127.0.0.1";
$password="";
$database = "moor_simple";
$connection = mysql_connect($host,$user,$password)
      or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
      or die ("couldn't select the database");
?>

<html>
<head>
<title>search database for matching records</title>
</head>
<body>
The Search Results are:

<?php
$sql = "SELECT name FROM moor_facility WHERE id";
if (isset($_GET['option[1]'])(
  $sql .= " AND electricity IS NOT NULL"; // I have added spaces in front of each of these values and removed the equals '='
}
if (isset($_GET['option'][2]))(
  $sql .= " AND toilet IS NOT NULL"; // added a space, removed =
}
if (isset($_GET['option'][3]))(
  $sql .= " AND shower IS NOT NULL"; // added a space, removed =
}
if (isset($_GET['option'][4]))(
  $sql .= " AND bin IS NOT NULL"; // added a space, removed =
}

echo "$sql<br>\n"; // quoted the echo string and added a newline character

$result = mysql_query($sql);

echo "$result<br>\n"; // quoted the echo string and added a newline character
?>

</body>
</html>
[/code]

Cheers Huggie!

[HuggieBear]

OK, I've changed the both the HTML in the form and the PHP/SQL in the results page, so give the following a try.  I'd suggest copying and pasting exactly as is, then putting your database password in, it should work for the setup you have as I've kept your table/column names the same:

[size=8pt][b]Form:[/b][/size]
[code]
<html>
<head>
  <title>Mooring Facilities Search Form</title>
</head>
<body>
  Please select the facilities you would like at a mooring:
  <form name="myform" action="search.php" method="GET">
  <div align="left"><br>
    <input type="checkbox" name="option[]" value="electricity"> Electricity<br>
    <input type="checkbox" name="option[]" value="shower"> Shower<br>
    <input type="checkbox" name="option[]" value="toilet"> Toilet<br>
    <input type="checkbox" name="option[]" value="bin"> Refuse-bin<br><br><br>
    <input type="submit" type="Submit">
  </div>
  </form>
</body>
</html>
[/code]

[size=8pt][b]Results Page:[/b][/size]
[code]<?php

// Your db connection options
$user="root";
$host="127.0.0.1";
$password="";
$database = "moor_simple";
$connection = mysql_connect($host,$user,$password) or die ("couldn't connect to server");
$db = mysql_select_db($database, $connection) or die ("couldn't select the database");

// Base SQL statement
$sql = "SELECT name FROM moor_facility WHERE id";

// If they've selected options, which ones
if (isset($_GET['option'])){
  $options = $_GET['option'];
  foreach ($options as $opt){
      if ($opt == "electricity"){
        $sql .= " AND electricity IS NOT NULL";
      }
      else if ($opt == "shower"){
        $sql .= " AND shower IS NOT NULL";
      }
      else if ($opt == "toilet"){
        $sql .= " AND toilet IS NOT NULL";
      }
      else if ($opt == "bin"){
        $sql .= " AND bin IS NOT NULL";
      }
  }
}

// Run the query
$result = mysql_query($sql) or die ("couldn't run the query $sql" . mysql_error());

// Echo the top html
echo <<<HTML
<html>
<head>
  <title>search database for matching records</title>
</head>
<body>
  <table width="300" cellpadding="2" cellspacing="0" border="1">
  <tr>
    <td>
    The Search Results are:
    </td>
  </tr>
HTML;

// While there's still rows, loop through them
while($row = mysql_fetch_array($result, MYSQL_ASSOC){
echo <<<HTML
<tr>
<td>
  {$row['name']}
</td>
</tr>
HTML;
}

// Echo your footer
echo <<<HTML
  </table>
</body>
</html>
HTML;
?>
[/code]

Here's a few changes I made and the reasons for them, read these to better help yourself understand what I did and why...

[size=8pt][color=blue][b]Change:[/b] You had the if statement incorrect, you had normal parenthesis, instead of curly braces, changed to curly's.
[b]Reason:[/b] Incorrect syntax.

[b]Change:[/b] You had $option[1], $option[2], $option[3] and $option[4], changed these to start at $option[0].
[b]Reason:[/b] Array indices start at 0, not 1.

[b]Change:[/b] Did away with the whole of the above options and replaced with a foreach loop.
[b]Reason:[/b] If someone only submitted refuse-bin on the form, and none of the others, it would actually become $option[0], not the expected $option[4], which meant your SQL statement would be incorrect.

[b]Change:[/b] The values on the form itself.
[b]Reason:[/b] To help tidy up code and assist with the above foreach loop.

[b]Change:[/b] Moved all the HTML into [url=http://uk.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc]heredoc[/url] syntax.
[b]Reason:[/b] I believe that it makes it look tidier and easier to read.

[b]Change:[/b] Rather than echo $result which would just print [b]Array()[/b] it now echos the results from the db.
[b]Reason:[/b] You had the syntax not quite right.[/color][/size]

Well that's it, I hope it helps, if it doesn't work (as I've coded it on the fly and haven't tested it) then please post back the error you get and I'll correct.

Regards
Huggie

[daniish]

Huggie you're an absolute LEGEND! Thankyou so much


...just one lil error:
Parse error: parse error, unexpected '{' in C:\wamp\www\moorings\search.php on line 52

[HuggieBear]

Not bad for untested :)

The end of line 52 looks like this:
[code=php:0]...MYSQL_ASSOC){[/code]


Change it to this:
[code=php:0]...MYSQL_ASSOC)[/code][color=red][b])[/b][/color][color=green]{[/color]

Notice the extra closing parenthesis.

Regards
Huggie