How to avoid empty in like statement in mysql query

Question

In my below code, I have been querying name and email from the database, in this case, if name or email is empty it fetches all the data.

How to avoid this? Please help

Mysql query:

$searchdata = mysqli_query($this->dbh, "SELECT insertdata.name, insertdata.email, state.StateName, district.DistrictName
  FROM insertdata
  INNER JOIN state ON insertdata.state = state.StCode
  INNER JOIN district ON insertdata.district = district.DistCode
  WHERE name LIKE '%$namesearch%' OR email LIKE '%$emailsearch%'");

My Code,

<?php
include_once("function.php");
$searchdata=new DB_con();
if(isset($_POST['submit']))
{
    $namesearch=$_POST['namesearch'];
    $emailsearch=$_POST['emailsearch'];
}
$search=$searchdata->searchdata($namesearch,$emailsearch);
foreach($search as $search)
{
    ?>
    <tr>
    <td><?php echo ($search['name']);?></td>
    <td><?php echo ($search['email']);?></td>
    <td><?php echo ($search['StateName']);?></td>
    <td><?php echo ($search['DistrictName']);?></td>
    </tr>
<?php 
}
?>

Your code is open to [SQL injection](https://stackoverflow.com/q/332365/2469308) related attacks. Please learn to use [Prepared Statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Madhur Bhaiya, Nov 23 '18 at 08:57
Also, check this answer on how to create query dynamically based on the filter value: https://stackoverflow.com/a/53427912/2469308 — Madhur Bhaiya, Nov 23 '18 at 08:57

score 0 · Answer 1 · edited Nov 23 '18 at 09:20

0

just add this if condition to check weather it is empty or not

// check here
if ($namesearch == '' || $emailsearch =='')
{
    echo 'Please enter name or email to search.';
} else {
    $search = $searchdata->searchdata($namesearch,$emailsearch);
    foreach($search as $search)
    {
    ?>
        <tr>
            <td><?php echo ($search['name']); ?></td>
            <td><?php echo ($search['email']); ?></td>
            <td><?php echo ($search['StateName']); ?></td>
            <td><?php echo ($search['DistrictName']); ?></td>
        </tr>
    <?php   
    }
}

edited Nov 23 '18 at 09:20

julianstark999

3,450
1
27
41

answered Nov 23 '18 at 09:13

Akhilesh

927
1
6
21

Thanks for your comments!. I want to check empty also if data is empty it will avoid in MySQL query and fetch the other. Like the name is empty it will fetch data match with email. Now it fetches all data from the name. – Riyaz Ashik Nov 23 '18 at 09:23

score 0 · Answer 2 · answered Nov 23 '18 at 10:54

How about writing different statements for various conditions like

if($namesearch == '' && $emailsearch !=''){
        $searchdata =  mysqli_query($this->dbh, "SELECT insertdata.name, insertdata.email, state.StateName, district.DistrictName
                        FROM insertdata  INNER JOIN state ON insertdata.state = state.StCode  INNER JOIN district ON insertdata.district
                        = district.DistCode WHERE  email LIKE '%$emailsearch%'");
    }
else if($namesearch != '' && $emailsearch ==''){
$searchdata =  mysqli_query($this->dbh, "SELECT insertdata.name, insertdata.email, state.StateName, district.DistrictName
                        FROM insertdata  INNER JOIN state ON insertdata.state = state.StCode  INNER JOIN district ON insertdata.district
                        = district.DistCode WHERE name LIKE '%$namesearch%'");
}
else if{
  //bla bla bla
}

How to avoid empty in like statement in mysql query

2 Answers2