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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.