Issues using checkboxes in forms in php forms


I am creating a form which pulls information from my SQL database. Initially my form had two fields, 'language' and 'prefecture' . Thanks to Julian Hansen on this forum, I managed to get the form to work, so that when no values were returned the form processed correctly. My code to do this is below:

  $language = $_POST['language'];
  $prefecture = $_POST['prefecture'];
  $sql = "SELECT * FROM teacher_table WHERE language = '".$language."' AND prefecture = '".$prefecture."' ";
  $result =mysqli_query($db_connection, $sql);
  if (count(mysqli_fetch_array($result)) > 0)  
 ...displays output
      // No Results
      redirect_to ('no_results.php');

Open in new window

However, I am now trying to add a checkbox to the form which has the function of basically   'only show values where photo field entry =1'. So that only entries with photo field = 1 are returned if it is checked and if it is not checked all entries (photo field = 1 or 0) are returned.

 I therefore tried to amend the statement to:
 $sql = "SELECT * FROM teacher_table WHERE language = '".$language."' AND prefecture = '".$prefecture."'  AND   photo = '".$photo."  ";

Open in new window

However, the problem with this is (I think?) relates to this line:

if (count(mysqli_fetch_array($result)) > 0)

Open in new window

 If no results are returned from language and prefecture, a result is always returned from photo (either checked or unchecked -1 or 0) so results is always greater than 0? I think that's what's happening from when I run the code. And also, when this box is not checked, it only returns values which are 'not-checked' i.e. 0. It should returns '1 'and '0' values.  

So I'm kind off stuck. I guess I'm looking for a way to use a checkbox in a form where if it is not checked, all values corresponding to that checkbox are returned, and if it is checked, values corresponding to that checkbox which are 'checked' or '1' are returned only.

And I want to do this in a way which doesn't interfere with my  if (count(mysqli_fetch_array($result)) > 0 statement .

Oh and I'm using the following code for my form:
<input type="hidden" name="photo" value="0" />
 <input type="checkbox" name="photo" value="1" checked>Show Teachers with Photos only<br>

Open in new window

I'm not sure if this is relevant but in my database table, the photo field is of tinyint type and all values are either 1 or 0.

Thanks for reading this post.

AdamTrying to learn phpAsked:
Who is Participating?
Julian HansenCommented:
Part of the problem might be that checkboxes don't submit a value if they are not checked so you need to get the value with a conditional like this

$photo = isset($_POST['photo']) ? $_POST['photo'] : false;

Open in new window

Then in your query you only include the filter on photo if this value is not false
$sql = "SELECT * FROM teacher_table WHERE language = '{$language}' AND prefecture = '{$prefecture}';
if ($photo) {
  $sql .= " AND  photo = '{$photo}'";

Open in new window

Now your query should work as expected.

Note: I changed the string to use embedded variables rather than concatenation (.) - much neater.
AdamTrying to learn phpAuthor Commented:
Works perfectly.

Again, many thanks for your help Julian - the code and the explanation.

Have a great weekend.
Julian HansenCommented:
You are welcome.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.