Jump to content

Want to reduce the repeating code on my php filter query

SrilekhaRamesh

By SrilekhaRamesh
in PHP Coding Help

 Share

Recommended Posts

SrilekhaRamesh Newbie

SrilekhaRamesh

Posted
Posted

Dear Team,

I have a datatable in my php page with fetching the data from database using ajax script. In this table i am using filtering the column. my script code is  

<script type="text/javascript" language="javascript">
				$(document).ready(function () {

					load_data();

					function load_data(is_category) {
						var sales = document.getElementById("sales").value;
						var material = document.getElementById("material").value; 
						var salesoffice = document.getElementById("salesoffice").value;
						var dataTable = $('#product_data').DataTable({
							"processing": true,
							"serverSide": true,
							"order": [],
							"ajax": {
								url: "pagination.php",
								type: "POST",
								data: { is_category: is_category, sales: sales, material:material,salesoffice:salesoffice}
							},
							"columnDefs": [
								{
									"targets": [1, 3, 5, 6, 7, 8, 9],
									"orderable": false,
								},
							],

						});
					}

					$(document).on('change', '#sales', function () {
						$('#product_data').DataTable().destroy();
							load_data();
					});

					$(document).on('change', '#material', function () {
						$('#product_data').DataTable().destroy();
							load_data();
					});
					
					$(document).on('change', '#salesoffice', function () {
						$('#product_data').DataTable().destroy();
							load_data();
					});
					
				});
</script>

my pagination php is  

<?php
//fetch.php
include("connection.php");
$column = array("id", "sales_doc_type", "billing_date", "material", "gross_amount", "sales_office", "plant", "distribution_channel_description", "division", "division_header");
$query = "SELECT * FROM billing ";
$query .= " WHERE ";

if (isset($_POST["sales"])) {  // Sales
	if ($_POST["sales"] !== "" && $_POST["material"] == "" && $_POST["salesoffice"] == "") {
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] == ""){
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] == "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}
}

if (isset($_POST["material"])) {  // Material
	if ($_POST["sales"] == "" && $_POST["material"] !== "" && $_POST["salesoffice"] == "") {
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] == ""){
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] == "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}
}

if (isset($_POST["salesoffice"], $_POST['postcode'])) {  // sales office
	if ($_POST["sales"] == "" && $_POST["material"] == "" && $_POST["salesoffice"] !== "") {
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
	}elseif ($_POST["sales"] == "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] == "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}
}

if (isset($_POST["search"]["value"])) {
	$query .= '(id LIKE "%' . $_POST["search"]["value"] . '%" ';
	$query .= 'OR sales_doc_type LIKE "%' . $_POST["search"]["value"] . '%" ';
	$query .= 'OR billing_date LIKE "%' . $_POST["search"]["value"] . '%" ';
	$query .= 'OR material LIKE "%' . $_POST["search"]["value"] . '%") ';
}

if (isset($_POST["order"])) {
	$query .= 'ORDER BY ' . $column[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . ' ';
} else {
	$query .= 'ORDER BY id ASC ';
}

$query1 = '';

if ($_POST["length"] != 1) {
	$query1 .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($conn, $query));

$result = mysqli_query($conn, $query . $query1);

$data = array();

while ($row = mysqli_fetch_array($result)) {
	$sub_array = array();
	$sub_array[] = $row["id"];
	$sub_array[] = $row["sales_doc_type"];
	$sub_array[] = $row["billing_date"];
	$sub_array[] = $row["material"];
	$sub_array[] = $row["gross_amount"];
	$sub_array[] = $row["sales_office"];
	$sub_array[] = $row["plant"];
	$sub_array[] = $row["distribution_channel_description"];
	$sub_array[] = $row["division"];
	$sub_array[] = $row["division_header"];
	$data[] = $sub_array;
}

function get_all_data($conn)
{
	$query = "SELECT * FROM billing";
	$result = mysqli_query($conn, $query);
	return mysqli_num_rows($result);
}

$output = array(
	"draw" => intval($_POST["draw"]),
	"recordsTotal" => get_all_data($conn),
	"recordsFiltered" => $number_filter_row,
	"data" => $data
);

echo json_encode($output);

?>

In my pagination .php i am using if condition for filter the data based on the selection. The filter code if condition is  

if (isset($_POST["sales"])) {  // Sales
	if ($_POST["sales"] !== "" && $_POST["material"] == "" && $_POST["salesoffice"] == "") {
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] == ""){
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] == "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}
}

if (isset($_POST["material"])) {  // Material
	if ($_POST["sales"] == "" && $_POST["material"] !== "" && $_POST["salesoffice"] == "") {
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] == ""){
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] == "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}
}

if (isset($_POST["salesoffice"], $_POST['postcode'])) {  // sales office
	if ($_POST["sales"] == "" && $_POST["material"] == "" && $_POST["salesoffice"] !== "") {
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
	}elseif ($_POST["sales"] == "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] == "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
	}elseif ($_POST["sales"] !== "" && $_POST["material"] !== "" && $_POST["salesoffice"] !== ""){
		$query .= "sales_office = '" . $_POST["salesoffice"] . "' AND ";
		$query .= "sales_doc_type = '" . $_POST["sales"] . "' AND ";
		$query .= "material = '" . $_POST["material"] . "' AND ";
	}
}

The if condition is repeating multiple times and going long. In this code i mentioned only 3 conditions. But i am having 7 filter conditions. If I want to use all the seven condition, my code will go longer.

I want to reduce this repetition code. Can any one help me how to reduce this.

Link to comment
Share on other sites
Strider64 Advanced Member

Strider64

Posted
Posted

Instead of using `if` statements you should be using `functions`, plus PHP has a great way of doing pagination (The following is just an example of what I'm talking about) -> 

    // Display Record(s) by Pagination
    public function page($perPage, $offset, $page = "index", $category = "general"): array
    {
        $sql = 'SELECT * FROM '. $this->table . ' WHERE page =:page AND category =:category ORDER BY id ASC, date_added DESC LIMIT :perPage OFFSET :blogOffset';
        $stmt = $this->pdo->prepare($sql); // Prepare the query:
        $stmt->execute(['page' => $page, 'perPage' => $perPage, 'category' => $category, 'blogOffset' => $offset]); // Execute the query with the supplied data:
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

I would look into pagination tutorials as that will save you a lot headaches.

Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted (edited)

1st of all, all the post data you send in the request will be set (is_category, sales, material, salesoffice), so there's no point in testing if it is set, because it will be. if using a post request, just detect if the REQUEST_METHOD is 'POST'

you should actually be using a get method request when determining what will be displayed on a page.

while I'm pretty sure this has already been covered in a previous thread, you should be using a data-driven design (which is probably what the $column array is part of.)

so what are you actually trying to accomplish? include a column/value in the WHERE clause if there is a non-empty value for that input? to do this, you don't need to write out code for every possible combination. regardless of using a data-driven design or conditional logic (once) for each input, you only need code that includes a column/value if there is a non empty value for that input. 

// using a data-driven design, define permitted WHERE columns and corresponding inputs
$where_columns = ['sales_doc_type'=>'sales', 'material'=>'material', 'sales_office'=>'salesoffice'];

// array to hold where terms
$where_terms = [];
// array to hold prepared query input parameters
$params = [];

// build the where terms
foreach($where_columns as $col=>$input)
{
	if($_POST[$input] ==! '')
	{
		$where_terms[] = "`$col`=?";
		$params[] = "$_POST[$input]";
	}
}

// build the WHERE clause
$where = '';
if(!empty($where_terms))
{
	$where = 'WHERE ' . implode(' AND ',$where_terms);
}

// build the sql query
$sql = "SELECT * FROM billing $where";

// examine the result
echo $sql; echo '<pre>'; print_r($params); echo '</pre>';


 

Edited by mac_gyver
Link to comment
Share on other sites
SrilekhaRamesh Newbie

SrilekhaRamesh

Posted
  • Author
Posted

Dear sir,

         Thanks for support, i modified the code as per your suggestion  .Now its working  all the seven filters option .

The below modify code for your reference. 

$where_columns = ['sales_doc_type'=>'sales', 'material'=>'material', 'sales_office'=>'salesoffice', 'plant'=>'plant', 'distribution_channel_description'=>'category', 'division'=>'division', 'division_header'=>'equipment'];

// array to hold where terms
$where_terms = [];
// array to hold prepared query input parameters

// build the where terms
foreach($where_columns as $col=>$input)
{
	if($_POST[$input] ==! '')
	{
		$where_terms[] = "$col= '$_POST[$input]' ";
	}
}

// build the WHERE clause
$where = '';
if(!empty($where_terms))
{
	$where = 'WHERE ' . implode(' AND ',$where_terms);
}else{
	$where = 'WHERE 1 ';
}


$query = "SELECT * FROM billing $where";

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.