Trying to use checkbox in a form only to display results which are '1'

[Jool]

Hello,

I am creating a form which pulls information from my SQL database. Initially my form had two fields, ‘language’ and ‘prefecture’ . Thanks to daveismyname 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:

[php]<?php

$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

else{
// No Results
redirect_to (‘no_results.php’);
db_disconnect($db_connection);
exit;
}

[/php]

However, I am now trying to add a checkbox to the form which has the function of basically 'only show values where photo field =1.

I tried to append my sql statement to

[php]$sql = “SELECT * FROM teacher_table WHERE language = '”.$language."’ AND prefecture = ‘".$prefecture."’ AND photo = '".$photo." ";[/php]

However, the problem with this is (I think?) if no results are returned from language and prefecture, a result is generally always returned from photo (either checked or unchecked -1 or 0) so if there are no results my code never gets to the else statement as

if (count(mysqli_fetch_array($result)) > 0) will always return a result greater than 0. I think?

Also - and making things a bit more complicated - if the box is unchecked, I’d like the database to return values where photo is 0 and/or 1 (true and / or false). My form code is:

[php]

Teaching Language

All Teachers English French German Italian Other

Prefecture

Everywhere Aichi Kyoto Osaka Tokyo

Show Teachers with Photos only
[/php]

Many thanks for reading. I hope that makes sense.

[Antonio]

HI,

For this form you have to use some type of sql-builder:
[php]<?php

$language = isset($_REQUEST[‘language’ ]) ? $_REQUEST[‘language’ ] : null;
$prefecture = isset($_REQUEST[‘prefecture’]) ? $_REQUEST[‘prefecture’] : null;
$photo = isset($_REQUEST[‘photo’ ]) ? $_REQUEST[‘photo’ ] : null;

$whereClause = [];

if ($language) {
$whereClause[] = ‘language = "’. mysqli_real_escape_string($db_connection, $language) .’"’;
}

if ($prefecture) {
$whereClause[] = ‘prefecture = "’. mysqli_real_escape_string($db_connection, $prefecture) .’"’;
}

if ($photo) {
$whereClause[] = ‘photo != “”’;
}

if (empty($whereClause)) {
$whereClause[] = ‘1’;
}

$sql = "SELECT * FROM teacher_table WHERE “. join(’ AND ', $whereClause) .” ";
// var_dump($sql);die; // Uncomment this for debug
$result = mysqli_query($db_connection, $sql);

if (count(mysqli_num_rows($result)) > 0) {
$list = mysqli_fetch_all($result);
var_dump($result);
}[/php]

[Jool]

Hi,

Many thanks Antonio for getting back to me with this.

I pasted the code in my page and only added a closing ‘?>’ and got the following error:

Parse error: syntax error, unexpected '<’ at what would be line 33 in the post below. All brackets seems to be closed and semi-columns are there so I can’t see what the problem is.

With $whereClause = [ ];, are you creating an array and adding values for language, prefecture and photo?

And the following code:

[php]if (empty($whereClause)) {
$whereClause[] = ‘1’;[/php]

Does that sort of say - if there is no value associated with $whereClause then assign the $whereClause array a value of 1?

Many thanks Antonio.

[Antonio]

With $whereClause = [ ];, are you creating an array and adding values for language, prefecture and photo?

Yes, if they have value. You send empty value if want to find "all" something. For example:

All Teachers

if there is no value associated with $whereClause then assign the $whereClause array a value of 1?

Yes, if try to find all records then the query will be [code]SELECT * FROM teacher_table WHERE 1[/code]

If you try to find teachers by language and photo, query will be SELECT * FROM teacher_table WHERE language = "English" AND photo != "" etc…

Parse error: syntax error, unexpected '<' at what would be line 33 in the post below. All brackets seems to be closed and semi-columns are there so I can't see what the problem is.

Try to close php-code: replace '<' on line 33 to '?><'. Or attach that file by zip-archive and i'll check it.

[Jool]

Thanks for the explanation Antonio, I’m starting to understand things better

Yes - I had my closing ?> after my html - (sorry, basic mistakes always for me).

The search form then ran with the [embed=425,349]var_dump($result);[/embed] command displaying the number of rows returned: public ‘num_rows’ => int 9

This is correct as I would have expected to see 9 entries returned. Fantastic!

As a last check I added an else statement, for when no rows should be returned. I amended your code as follows:
[php]
$sql = "SELECT * FROM teacher_table WHERE “. join(’ AND ', $whereClause) .” ";
// var_dump($sql);die; // Uncomment this for debug
$result = mysqli_query($db_connection, $sql);

if (count(mysqli_num_rows($result)) > 0) {
$list = mysqli_fetch_all($result);
var_dump($result);
echo “yes”;
}

else{
var_dump($result);
echo “no”;
}
?> [/php]

However, when the search should return no result, I’m getting this response:

[tt]C:\wamp64\www\sites\BootStrap_Sites\OrangoJapan.com\public\website\results_alpha.php:50:
object(mysqli_result)[2]
public ‘current_field’ => int 0
public ‘field_count’ => int 85
public ‘lengths’ => null
public ‘num_rows’ => int 0
public ‘type’ => int 0
yes[/tt]

As the number of rows is 0, should it not have echoed no back, as per the else statement?

I tried changing my else to an elseif (count(mysqli_num_rows($result)) = 0) but that didn’t work either (returned an error).

Many thanks.

[Antonio]

Oh… I mistaken too. Sorry

This is an invalid condition!! It is always true
[php]if (count(mysqli_num_rows($result)) > 0) {[/php]

Must be
[php]if (mysqli_num_rows($result) > 0) {[/php]

[Jool]

Great - that worked!

Many thanks Antonio for the solution and the explanation.

Have a good day and weekend!

[Antonio]

Good luck

[Jool]

Hello again,

Apologies to ask yet another question relating to this post - this will be the last question in this thread - honestly!

I tried to expand upon your code and wanted to add another checkbox to only display rows with videos (name is ‘vid’). I simply tried:

[php]<?php

$language = isset($_POST[‘language’ ]) ? $_POST[‘language’ ] : null;
$prefecture = isset($_POST[‘prefecture’]) ? $_POST[‘prefecture’] : null;
$photo = isset($_POST[‘photo’ ]) ? $_POST[‘photo’ ] : null;
$vid = isset($_POST[‘vid’ ]) ? $_POST[‘vid’ ] : null;

$whereClause = [];

if ($language) {
$whereClause[] = ‘language = "’. mysqli_real_escape_string($db_connection, $language) .’"’;
}

if ($prefecture) {
$whereClause[] = ‘prefecture = "’. mysqli_real_escape_string($db_connection, $prefecture) .’"’;
}

if ($photo) {
$whereClause[] = ‘photo != “”’;
}

if ($vid) {
$whereClause[] = ‘vid != “”’;
}

if (empty($whereClause)) {
$whereClause[] = ‘1’;
}

$sql = " SELECT * FROM teacher_table WHERE “. join(’ AND ', $whereClause) .”";
// var_dump($sql);die; // Uncomment this for debug
$result = mysqli_query($db_connection, $sql);
[/php]

Do I need to alway amend [tt]$sql = " SELECT * FROM teacher_table WHERE “. join(’ AND ', $whereClause) .”";[/tt] when I am adding more fields to this form?

Ok - no more questions from me on this. Really appreciate your help Antonio.

[Antonio]

Hi Jool,

when I am adding more fields to this form?

It depends only on the complexity of the query. If you select data only from one table - no, if you join more tables - yes.

For example you want to search by teacher’s video name. So you need to join teacher_video:
[php]$language = isset($_REQUEST[‘language’ ]) ? $_REQUEST[‘language’ ] : ‘English’;
$prefecture = isset($_REQUEST[‘prefecture’]) ? $_REQUEST[‘prefecture’] : ‘’;
$photo = isset($_REQUEST[‘photo’ ]) ? $_REQUEST[‘photo’ ] : 1;
$videoName = isset($_REQUEST[‘video_name’]) ? $_REQUEST[‘video_name’] : ‘hello’;

$whereClause = [];
$tables = [
‘teachers’ => ‘teacher_table tch’,
];

if ($language) {
$whereClause[] = ‘tch.language = "’. mysqli_real_escape_string($conn, $language) .’"’;
}

if ($prefecture) {
$whereClause[] = ‘tch.prefecture = "’. mysqli_real_escape_string($conn, $prefecture) .’"’;
}

if ($photo) {
$tables[‘photo’] = ‘teacher_photo pht ON pht.teacher_id = tch.id’;
}

if ($videoName) {
$whereClause[] = ‘vid.name LIKE "%’. mysqli_real_escape_string($conn, $videoName) .’%"’;
$tables[‘video’] = ‘teacher_video vid ON vid.teacher_id = tch.id’;
}

if (empty($whereClause)) {
$whereClause[] = ‘1’;
}

$sql = “SELECT tch.* FROM “. join(’ JOIN ', $tables) .” WHERE “. join(’ AND ', $whereClause) .” GROUP BY tch.id ORDER BY tch.name”;
var_dump($sql);die; // Uncomment this for debug[/php]

Output

SELECT tch.* FROM teacher_table tch JOIN teacher_photo pht ON pht.teacher_id = tch.id JOIN teacher_video vid ON vid.teacher_id = tch.id WHERE tch.language = "English" AND vid.name LIKE "%hello%" GROUP BY tch.id ORDER BY tch.name

The logic is simple - more search fields, join more tables. No search fields, no joins. The query without search fields:

SELECT tch.* FROM teacher_table tch WHERE 1 GROUP BY tch.id ORDER BY tch.name

GROUP BY is used if some table contains more than one record for one teacher (for example teacher_video may contain two records).
ORDER BY is used because GROUP BY changes the order.

https://dev.mysql.com/doc/refman/5.7/en/join.html

[Jool]

Good Morning Antonio.

Many thanks for your reply and help. I’ll get busy learning.

Have a great weekend