datatable, Php filter using where clause not working

Question

I use this script for datatable.

$query = "SELECT * FROM tbl_child "; enter code here

will show all the value in the table but i need

$query = "SELECT * FROM tbl_child where child_place='us' ";

if i apply where clause then datatable will not show anything.

is it because the where clause is already used by the isset? how can i make a filter based on child_place.

Thanks

    <?php
$column = array("childpid", "child_name", "child_house", "child_gender", "child_age", "child_place");

$query = "SELECT * FROM tbl_child ";

if(isset($_POST["search"]["value"]))

{

 $query .= '
 WHERE child_name LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_house LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_gender LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_age LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_place LIKE "%'.$_POST["search"]["value"].'%"
 
 ';

}

if(isset($_POST["order"]))

{

 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';

}
else
{
 $query .= 'ORDER BY childpid DESC ';
}
$query1 = '';

if($_POST["length"] != -1)

{

 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];

}


$statement = $pdo->prepare($query);

$statement->execute();


$number_filter_row = $statement->rowCount();


$statement = $pdo->prepare($query . $query1);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

foreach($result as $row)

{

 $sub_array = array();

 $sub_array[] = $row['childpid'];

 $sub_array[] = $row['child_name'];

 $sub_array[] = $row['child_house'];

 $sub_array[] = $row['child_gender'];

 $sub_array[] = $row['child_age'];

 $sub_array[] = $row['child_place'];


 $data[] = $sub_array;

}

function count_all_data($pdo)

{

 $query = "SELECT * FROM tbl_child ";

 $statement = $pdo->prepare($query);

 $statement->execute();

 return $statement->rowCount();

}

$output = array(

 'draw'   => intval($_POST['draw']),

 'recordsTotal' => count_all_data($pdo),

 'recordsFiltered' => $number_filter_row,

 'data'   => $data
);

echo json_encode($output);

?>

**Warning!** You're open to [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection)! Read [how to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) by using prepared statements with bound parameters instead of injecting variables directly into your queries. It's not just about security. If your data contains, for example, a single quote `'`, your query will break. — M. Eriksson, Feb 16 '22 at 23:52

score 0 · Accepted Answer · answered Feb 16 '22 at 23:50

it is very simple. just add your condition to the code like this

<?php
$column = array("childpid", "child_name", "child_house", "child_gender", "child_age", "child_place");

$query = "SELECT * FROM tbl_child ";

if(isset($_POST["search"]["value"]))

{

 $query .= '
 WHERE (child_name LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_house LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_gender LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_age LIKE "%'.$_POST["search"]["value"].'%" 

 OR child_place LIKE "%'.$_POST["search"]["value"].'%")
 
 AND child_place = "us"
 ';

}else{
 $query .= '
  WHERE child_place ="us" ';
}

if(isset($_POST["order"]))

{

 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';

}
else
{
 $query .= 'ORDER BY childpid DESC ';
}
$query1 = '';

if($_POST["length"] != -1)

{

 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];

}


$statement = $pdo->prepare($query);

$statement->execute();


$number_filter_row = $statement->rowCount();


$statement = $pdo->prepare($query . $query1);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

foreach($result as $row)

{

 $sub_array = array();

 $sub_array[] = $row['childpid'];

 $sub_array[] = $row['child_name'];

 $sub_array[] = $row['child_house'];

 $sub_array[] = $row['child_gender'];

 $sub_array[] = $row['child_age'];

 $sub_array[] = $row['child_place'];


 $data[] = $sub_array;

}

function count_all_data($pdo)

{

 $query = "SELECT * FROM tbl_child ";

 $statement = $pdo->prepare($query);

 $statement->execute();

 return $statement->rowCount();

}

$output = array(

 'draw'   => intval($_POST['draw']),

 'recordsTotal' => count_all_data($pdo),

 'recordsFiltered' => $number_filter_row,

 'data'   => $data
);

echo json_encode($output);

?>

follow @M. Eriksson advice on SQL injection – Segun Adeniji Feb 16 '22 at 23:55 — Segun Adeniji, Feb 16 '22 at 23:55

datatable, Php filter using where clause not working

1 Answers1