PHP MYSQL checkbox search

Question

I am working on a checkbox search. I'm new to PHP. I would appreciate it if someone could help me. Basically the user checks the options required, hits submit and results matching the checkbox options is displayed. The checkboxes have a value of M for Male and F for Female which matches the data in the MYSQL table.

So if a user checks checkbox 'Male' (see code below) all data with 'Male' that have a value of 'M' in the MYSQL table are returned. And any checkboxes not checked should simply be ignored as the user is only interested in the option 'Male' being 'M' (true).

To sum up I only need the search to take into account checkboxes that have been checked and echo which users that has been selected(more like filtering) through PHP.Any help appreciated. Thanks

the table looks like this :

id      name     address    gender    race
-------------------------------------------

1. 1    Lee      NY          M        French
2. 2    James    LA          M        Colombian
3. 3    Kashi    JAPAN       F        Japanese

and i have a form with checkboxes like this :

<form action="shortlist.php" method="post">
 <label for="sel1">Gender:</label>
 Male:<input name="keyword[]" type="checkbox" value="M"  />
 Female:<input name="keyword[]" type="checkbox" value="F" />
 <button name = "myBtn" type="submit" class="btn btn-  default">Search</button>
</form>

SQL :

$sql="SELECT name, address,gender,race FROM talent1 WHERE gender = $gender'";

I'm suppose to echo something like this :

echo "<table width='100%'>\n";
//if (mysqli_num_rows($result) > 0){
//$row=mysqli_fetch_assoc($result);
//$muchneededid = $row["talent_id"];
     while ($row=mysqli_fetch_assoc($result))   
        echo'<tr>'."\n";
        echo '<tr>';
        echo '<th>Name</th>';
        echo '<th>Address</th>';
        echo '<th>Gender</th>';
        echo '</tr>';
        echo "<td>{$row["name"]}</td>\n" . "<td>{$row["address"]}</td>\n" . "<td>{$row["gender"]}</td>\n";  
        echo '</tr>'."\n";
        echo "</table>\n";
        }
        else
        {
       echo "0 results";
       }
       mysqli_close($conn);
       }

there are no checkboxes in your html. Do you mean select-options? — Jeff
– Jeff, Commented Jan 1, 2017 at 5:06
but basicly it would be smth like if checkbox 'm' then add to sql "OR GENDER='M' " — Jeff
– Jeff, Commented Jan 1, 2017 at 5:08
now you loop through $_POST['keyword'] - if value=M add "OR GENDER=M" to sql — Jeff
– Jeff, Commented Jan 1, 2017 at 5:12

Jeff · Accepted Answer · 2017-01-01 11:09:51Z

2

given the information we've got it would be something like this:

<?php
//$_POST['keyword'] = array("M", "F");
$sql_addon = ''; // EDIT: this was one line below first, which of course created an 'undefined' error.
if(isset($_POST['keyword'])) {

    foreach($_POST['keyword'] as $k => $val) {
         $sql_addon.= " OR gender='$val'";
    }
}
$sql="SELECT name, address,gender,race FROM talent1 WHERE 1=1 ";
if($sql_addon) {
    $sql .= " AND (1=2 ".$sql_addon.")";
}
echo $sql;
// SELECT name, address,gender,race FROM talent1 WHERE 1=1 AND (1=2 OR gender=M OR gender=F)
?>

Those 1=1 and 1=2 might look silly, but it's (IMHO) the easiest way to generate that sql. 1=2 is there to create a falsy value, so that no records will be shown if none of the options are clicked.

edited Jan 1, 2017 at 11:09

answered Jan 1, 2017 at 5:40

Jeff

6,9611 gold badge19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

EhsanT Over a year ago

Great, I was writing a comment about 1=2 part which you added it. Also I think you have to enclose $val in single quote since it's character...

struppy Over a year ago

Thanks for the reply. I'm kinda lost at troubleshooting this Undefined variable: sql_addon on line 18 SELECT name, address,gender,race FROM talent1 WHERE 1=1

EhsanT Over a year ago

@struppy you can move $sql_addon = ''; out side(before) the if(isset($_POST['keyword'])) { and then change this line: if($sql_addon) { to if(!empty($sql_addon)) {

struppy Over a year ago

@EhsanT Thanks that solved the issue. Can you please enlighten me on how to echo the fetched results?

EhsanT Over a year ago

@struppy first if this answer has solved your problem, please accept it and also give it an up-vote. But about your request, it really depends on how have you written your current code. If you can edit your question and add the related php code on how you are running your current query right now(are you using mysqli or PDO)

|

Collectives™ on Stack Overflow

PHP MYSQL checkbox search

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related