Multiple search criteria return result by either all input of part input

Question

I know I am supposed to apply prepared statement, but at this stage I just want to get clear about the basic for multiple search criteria.

I have 3 criteria for users to query, if users input 3 criteria conditions then my code returns the correct result. But when the user only input 1 criteria then my code return records not found.

I have tried to include (is set) in the criteria but I find conflicting with (is set) in summit button.

if (isset($_POST['submit'])){

$status = $_POST['status'];
$area = $_POST['area'];
$address = $_POST['address'];

if($status != "" OR $area != "" OR $address != ""){
$query = "SELECT * FROM `tbl_name` WHERE address LIKE '%".$address."%' 
AND status LIKE '%".$status."%' AND area = '$area' ";
}

I expect users can get the results either input in the all 3 search criteria, or only any 2 criteria or only any 1 criteria.

But my case only input all 3 only can return correct answers.

I managed to get the solution, I don't know where to post it so I make it below

        <?php
        include("db.php");
        if (isset($_POST['submit'])){

            $status = $_POST['status'];
            $area = $_POST['area'];
            $address = $_POST['address'];

            $input = $_POST;

            $table_name = 'tbl_name';

            $column = '*';

            $query = "SELECT $column FROM `$table_name`";

            $where = null;

            if (!empty($input['address'])) 
            {
               $where = "WHERE";
               $query .= " $where address LIKE '%".$input['address']."%'";
            }

            if (!empty($input['status']))
            {
            if (is_null($where))
            {
            $where = "WHERE";
            }
            else {
            $where = "AND";
            }
            $query .= " $where status='{$input['status']}'";
            }

           if (!empty($input['area']))
            {
           if (is_null($where))
            {
            $where = "WHERE";
            }
            else {
            $where = "AND";
            }
            $query .= " $where area='{$input['area']}'";
            }       

            $data = mysqli_query($conn, $query) or die('error');
            if(mysqli_num_rows($data) > 0){
                    while($row = mysqli_fetch_assoc($data)){
                        $contact_person = $row['contact_person'];
                        $status = $row['status'];
                        $area = $row['area'];
                        $address = $row['address'];
                    ?>

How come the some comments here disappeared ? I really need those to refer to learn — player, Aug 11 '19 at 06:11
I managed to find a solution, i dont know where to post so I make it in the bottom of my question — player, Aug 22 '19 at 09:28
Hope at least I could make some contribution to the community. — player, Aug 22 '19 at 09:29

score 0 · Answer 1 · answered Aug 11 '19 at 04:46

0

WHERE ($address is null OR address LIKE '%".$address."%')
  AND ($status is null OR status LIKE '%".$status."%')
  AND ($area is null OR area = '$area')

answered Aug 11 '19 at 04:46

juergen d

201,996
37
293
362

Thanks. Just appied this but it did not work – player Aug 11 '19 at 04:50
1

Are your input values `null` or `''`? – juergen d Aug 11 '19 at 04:51
it says "error" – player Aug 11 '19 at 04:52
You need to get the complete error message. MySQL has excellent error messages – juergen d Aug 11 '19 at 04:53
sorry, but does any difference null or ' ' ? – player Aug 11 '19 at 04:58
Yes, then it would be `$address = ''` instead of `$address is null` – juergen d Aug 11 '19 at 05:01
Can you tell to code to display full error message ? – player Aug 11 '19 at 05:01
It should be in the PHP logs – juergen d Aug 11 '19 at 05:02
I already tried WHERE ($address =" " OR address LIKE '%".$address."%') AND ($status = " " OR status LIKE '%".$status."%') AND ($area = " " OR area = '$area') still not working – player Aug 11 '19 at 05:05
Undefined index: status in C:\MAMP\htdocs\3f\index5.php on line 109 – player Aug 11 '19 at 05:10
line 109 is $status = $_POST['status']; – player Aug 11 '19 at 05:11
Maybe the html input is wrong. You can try this code to detect the wrong strings: `print_r ($ _ POST);` – ariferol01 Aug 11 '19 at 05:23
Thank you for your patience.. but where to put print_r ($ _ POST); – player Aug 11 '19 at 05:31
NOT `" "`. Use `''` - an empty string without whitespaces – juergen d Aug 11 '19 at 06:15
I use '', error with php logs Undefined index: status in C:\MAMP\htdocs\3f\index5.php on line 109 where line 109 is $status = ($_POST['status']); – player Aug 11 '19 at 06:27

Multiple search criteria return result by either all input of part input

1 Answers1