Need Help Counting 'Pending' Status Based on Filtered Data in Table View

[snawaz]

I'm facing an issue in my Table View where I want to count the occurrences of 'pending' status based on the currently loaded data and respecting any applied filters. Despite attempting direct SQL queries, I couldn't achieve the desired count.

Essentially, I need to dynamically count the number of 'pending' statuses within the displayed dataset. For instance, if I apply a filter for the last week, I want the count to reflect only the 'pending' statuses within that filtered data. I'm attaching a snapshot to better illustrate the problem. Any guidance or solutions on how to achieve this within the Table View would be greatly appreciated. Thank you.

status.png (60.84 KiB) Viewed 3884 times

[pbottcher]

Hi,

if I get your request correctly you would like to count only those entries that you show on the visible part of your table, correct?
Or do you need the count for the filtered data overall, meaning that your filter returns 100 results, but you only display 10 on the tableview, you would like to count the pending on the 100 (=filtered result)?

[snawaz]

Hello pböttcher,

I require an overall count of the filtered data, which could potentially be substantial and managed through pagination. Initially, the count should be based on the entire dataset in the table. However, upon applying any filters, the count should dynamically adjust to reflect the filtered dataset. As illustrated in the snapshot, there are summary cards displayed on the same page atop the table view.

Looking forward to your help. Thanks

[pbottcher]

Hi,

you can try this:

Add to your hooks/TABLENAME.php file in the _init function:

Code: Select all

$queryWhere = get_sql_filter_query($options);

$filteredcount=sqlvalue("SELECT count(1) from TABLENAME where YOURSELECTION ".$queryWhere);

And add this functions at the end of the hooks/TABLENAME.php file

Code: Select all

function get_sql_filter_query($a) {

	$a->QueryFieldsIndexed = reIndex($a->QueryFieldsFilters);
	$f=$a->validate_filters($_REQUEST,4);
	array_walk($_REQUEST['FilterAnd'],'makeSafe');
	$a->FilterAnd=$_REQUEST['FilterAnd'];
	array_walk($_REQUEST['FilterField'],'makeSafe');
	$a->FilterField=$_REQUEST['FilterField'];
	array_walk($_REQUEST['FilterOperator'],'makeSafe');
	$a->FilterOperator=$_REQUEST['FilterOperator'];
	array_walk($_REQUEST['FilterValue'],'makeSafe');
	$a->FilterValue=$_REQUEST['FilterValue'];

	$SearchString = Request::val('SearchString');
	if(strlen($SearchString)) {
		if($Search_x != '') $FirstRecord = 1;

		foreach($a->QueryFieldsQS as $fName => $fCaption)
			if(stripos($fName, '<img') === false)
				$a->QuerySearchableFields[$fName] = $fCaption;

		$sss = makeSafe($SearchString); // safe search string

		if(count($a->QuerySearchableFields)) 
			$a->QueryWhere .= ' AND (' .
				implode(
					" LIKE '%{$sss}%' OR ", 
					array_keys($a->QuerySearchableFields)
				) . " LIKE '%{$sss}%'" .
			')';
	}
	$FiltersPerGroup=4;

	$filterGroups[] = [
		'join' => '', 
		'filters' => [ /* one or more strings, each describing a filter */ ]
	];
	$currentGroup =& $filterGroups[count($filterGroups) - 1];

	for($j = 0; $j < $FiltersPerGroup; $j++) {
		$ij = $i + $j;

		// not a valid filter?
		if(!(($a->FilterAnd[$ij] != '' || $ij == 1)
			&& $a->FilterField[$ij] != ''
			&& $a->FilterOperator[$ij] != ''
			&& (
				strlen($a->FilterValue[$ij])
				|| strpos($a->FilterOperator[$ij], 'empty') !== false
			)
		)) continue;

		if($a->FilterAnd[$ij] == '') $a->FilterAnd[$ij] = 'and';
		$currentGroup['filters'][] = '';
		$currentFilter =& $currentGroup['filters'][count($currentGroup['filters']) - 1];

		// always use the 1st FilterAnd of the group as the group's join
		if(empty($currentGroup['join'])) $currentGroup['join'] = thisOr($a->FilterAnd[$i], 'and');

		// if this is NOT the first filter in the group, add its FilterAnd, else ignore
		if(count($currentGroup['filters']) > 1)
			$currentFilter = $a->FilterAnd[$ij] . ' ';

		list($isDate, $isDateTime) = fieldIsDateTime($a, $a->FilterField[$ij]);

		if($a->FilterOperator[$ij] == 'is-empty' && !$isDateTime)
			$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "='' OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';

		elseif($a->FilterOperator[$ij] == 'is-not-empty' && !$isDateTime)
			$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=''";

		elseif($a->FilterOperator[$ij] == 'is-empty' && $isDateTime)
			$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "=0 OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';

		elseif($a->FilterOperator[$ij] == 'is-not-empty' && $isDateTime)
			$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=0";

		elseif($a->FilterOperator[$ij] == 'like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
			$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";

		elseif($a->FilterOperator[$ij] == 'not-like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
			$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " NOT LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";

		elseif($isDate) {
			$dateValue = mysql_datetime($a->FilterValue[$ij]);
			$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '$dateValue'";

		} else
			$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '" . makeSafe($a->FilterValue[$ij]) . "'";

	}


	// construct filters from $filterGroups
	$filtersWhere = '';
	foreach($filterGroups as $fg) {
		if(empty($fg['filters'])) continue;

		// ignore 1st join (i.e. use it only if filtersWhere already populated)
		if($filtersWhere) $filtersWhere .= " {$fg['join']} ";

		$filtersWhere .= '(' . implode(' ', $fg['filters']) . ')';
	}

	if($filtersWhere) $a->QueryWhere .= " AND ($filtersWhere)";
	
	return $a->QueryWhere;
	
}

function fieldIsDateTime($a, $fieldIndex) {
	static $cache = [];

	if(empty($a->QueryFieldsIndexed[$fieldIndex])) return [false, false];
	if(isset($cache[$fieldIndex])) return $cache[$fieldIndex];

	$tries = 0; $isDateTime = $isDate = false;
	$fieldName = str_replace('`', '', $a->QueryFieldsIndexed[$fieldIndex]);
	list($tn, $fn) = array_map('makeSafe', explode('.', $fieldName));

	while(!($res = sql("SHOW COLUMNS FROM `{$tn}` LIKE '{$fn}'", $eo)) && $tries < 2) {
		$tn = substr($tn, 0, -1); // this is to strip # from table alias: 'table1' becomes 'table'
		$tries++;
	}

	if($res !== false && $row = @db_fetch_array($res)) {
		$isDateTime = in_array($row['Type'], ['date', 'time', 'datetime']);
		$isDate = in_array($row['Type'], ['date', 'datetime']);
	}

	$cache[$fieldIndex] = [$isDate, $isDateTime];
	return $cache[$fieldIndex];
}

So you should get in the $filteredcount always the correct count on the filtered data.

[snawaz]

Hello pböttcher,

Thank you so much for your effort.
I have tried to test your code but the code is not executing due to some errors mentioned below.

if ($Search_x != '') $FirstRecord = 1;
$ij = $i + $j;
if (empty($currentGroup['join'])) $currentGroup['join'] = thisOr($a->FilterAnd[$i], 'and');

Undefined variables are highlighted in red above

Regarding the below query, how i can use the variable $filteredcount to display the count in my summary card? Will it be globally available outside this _init function?

$filteredcount = sqlvalue('SELECT count(1) from incident_report where "Pending" ' . $queryWhere);

Surprisingly, it is very complex code. I thought it would be easy, but it seems very difficult. I wish there were any other simple way.

[pbottcher]

Hi,

sorry, please try this.

Code: Select all

function get_sql_filter_query($a) {
	// $a= $option;
	if (isset($_REQUEST['FilterAnd'])) {
		$a->QueryFieldsIndexed = reIndex($a->QueryFieldsFilters);
		array_walk($_REQUEST['FilterAnd'],'makeSafe');
		$a->FilterAnd=$_REQUEST['FilterAnd'];
		array_walk($_REQUEST['FilterField'],'makeSafe');
		$a->FilterField=$_REQUEST['FilterField'];
		array_walk($_REQUEST['FilterOperator'],'makeSafe');
		$a->FilterOperator=$_REQUEST['FilterOperator'];
		array_walk($_REQUEST['FilterValue'],'makeSafe');
		$a->FilterValue=$_REQUEST['FilterValue'];
	}
	$Search_x = Request::val('Search_x');

	$SearchString = Request::val('SearchString');
	if(strlen($SearchString)) {
		if($Search_x != '') $FirstRecord = 1;

		foreach($a->QueryFieldsQS as $fName => $fCaption)
			if(stripos($fName, '<img') === false)
				$a->QuerySearchableFields[$fName] = $fCaption;

		$sss = makeSafe($SearchString); // safe search string

		if(count($a->QuerySearchableFields)) 
			$a->QueryWhere .= ' AND (' .
				implode(
					" LIKE '%{$sss}%' OR ", 
					array_keys($a->QuerySearchableFields)
				) . " LIKE '%{$sss}%'" .
			')';
	}
	$FiltersPerGroup=4;

	$filterGroups[] = [
		'join' => '', 
		'filters' => [ /* one or more strings, each describing a filter */ ]
	];
	$currentGroup =& $filterGroups[count($filterGroups) - 1];
	for($i = 1; $i <= (datalist_filters_count * $FiltersPerGroup); $i += $FiltersPerGroup) {
		for($j = 0; $j < $FiltersPerGroup; $j++) {
			$ij = $i + $j;

			// not a valid filter?
			if(!(($a->FilterAnd[$ij] != '' || $ij == 1)
				&& $a->FilterField[$ij] != ''
				&& $a->FilterOperator[$ij] != ''
				&& (
					strlen($a->FilterValue[$ij])
					|| strpos($a->FilterOperator[$ij], 'empty') !== false
				)
			)) continue;

			if($a->FilterAnd[$ij] == '') $a->FilterAnd[$ij] = 'and';
			$currentGroup['filters'][] = '';
			$currentFilter =& $currentGroup['filters'][count($currentGroup['filters']) - 1];

			// always use the 1st FilterAnd of the group as the group's join
			if(empty($currentGroup['join'])) $currentGroup['join'] = thisOr($a->FilterAnd[$i], 'and');

			// if this is NOT the first filter in the group, add its FilterAnd, else ignore
			if(count($currentGroup['filters']) > 1)
				$currentFilter = $a->FilterAnd[$ij] . ' ';

			list($isDate, $isDateTime) = fieldIsDateTime($a, $a->FilterField[$ij]);

			if($a->FilterOperator[$ij] == 'is-empty' && !$isDateTime)
				$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "='' OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';

			elseif($a->FilterOperator[$ij] == 'is-not-empty' && !$isDateTime)
				$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=''";

			elseif($a->FilterOperator[$ij] == 'is-empty' && $isDateTime)
				$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "=0 OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';

			elseif($a->FilterOperator[$ij] == 'is-not-empty' && $isDateTime)
				$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=0";

			elseif($a->FilterOperator[$ij] == 'like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
				$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";

			elseif($a->FilterOperator[$ij] == 'not-like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
				$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " NOT LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";

			elseif($isDate) {
				$dateValue = mysql_datetime($a->FilterValue[$ij]);
				$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '$dateValue'";

			} else
				$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '" . makeSafe($a->FilterValue[$ij]) . "'";

		}
	}

	// construct filters from $filterGroups
	$filtersWhere = '';
	foreach($filterGroups as $fg) {
		if(empty($fg['filters'])) continue;

		// ignore 1st join (i.e. use it only if filtersWhere already populated)
		if($filtersWhere) $filtersWhere .= " {$fg['join']} ";

		$filtersWhere .= '(' . implode(' ', $fg['filters']) . ')';
	}

	if($filtersWhere) $a->QueryWhere .= " AND ($filtersWhere)";
	
	return $a->QueryWhere;
	
}

function fieldIsDateTime($a, $fieldIndex) {
		static $cache = [];

		if(empty($a->QueryFieldsIndexed[$fieldIndex])) return [false, false];
		if(isset($cache[$fieldIndex])) return $cache[$fieldIndex];

		$tries = 0; $isDateTime = $isDate = false;
		$fieldName = str_replace('`', '', $a->QueryFieldsIndexed[$fieldIndex]);
		list($tn, $fn) = array_map('makeSafe', explode('.', $fieldName));

		while(!($res = sql("SHOW COLUMNS FROM `{$tn}` LIKE '{$fn}'", $eo)) && $tries < 2) {
			$tn = substr($tn, 0, -1); // this is to strip # from table alias: 'table1' becomes 'table'
			$tries++;
		}

		if($res !== false && $row = @db_fetch_array($res)) {
			$isDateTime = in_array($row['Type'], ['date', 'time', 'datetime']);
			$isDate = in_array($row['Type'], ['date', 'datetime']);
		}

		$cache[$fieldIndex] = [$isDate, $isDateTime];
		return $cache[$fieldIndex];
	}

in your query

$filteredcount = sqlvalue('SELECT count(1) from incident_report where "Pending" ' . $queryWhere);

you are missing the field to compate the "Pending" status.

Other than that I do not know how you want to display you top list with the number of tickets in the boxes. So it is hard to tell.
But the variable will only be available within the scope of the function. If you need it outside, you could use e.g. $_SESSION

[snawaz]

Hello pböttcher,

It is working perfectly as I wanted. Thank you so much. I heartily appreciate your help.

Regarding my query, I have updated it as below.

Code: Select all

$_SESSION["filteredcount"] = sqlvalue('SELECT count(1) from incident_report where incident_report.status = "Pending" ' . $queryWhere);

As you suggested, I used the session variable to display it. below is how I used this variable.

Code: Select all

<script>
  $j(document).ready(function () {    

    $j("#pendingCountID").text('<?php echo $_SESSION["filteredcount"] ?>');

  });
</script>

Is this the only way to use the session variable, or is there a better way to display the count variable?

[snawaz]

Hi pböttcher,

I wanted to extend my appreciation for the excellent function you've provided. It performs admirably by honoring all applied filters in calculating the count/occurrence of various statuses in the table view.

I'm curious if it's feasible to enhance the function to accommodate the quick search in the same way as you included filters. With the current function you have provided, I can get the count of the required status concerning the applied filters if any. Is it possible if I can get the count of the required status from the search results after using a quick search on the table?

Your expertise in addressing this would be immensely valuable. Thank you for your support in this matter. I look forward to any insights or modifications you might provide.

[pbottcher]

Hi,

did you test if it works? I thought that feature is already included. At least it works on my test system with both filter and search.

[snawaz]

Hello pböttcher,

I've conducted tests, and the filters seem to be functioning correctly. However, when using the search feature, all counts become zero. Please see to the snapshots below for better understandings.

All counts are updated based on the applied filters.

filters.png (62.18 KiB) Viewed 3483 times

All counts become zero while using quick search feature.

[pbottcher]

Hi,

can you post some more information. What do you get if you add a seach in the $queryWhere?

[snawaz]

Hello,

Code: Select all

$_SESSION["filteredcount"] = sqlvalue('SELECT count(1) from incident_report where incident_report.status = "Pending" ' . $queryWhere);

My requirement was to get the count of the 'Pending' value from the currently loaded data in the table. Initially, I managed to retrieve the count from all the data in the database table. However, I specifically need the count from the limited data currently loaded in the table. For instance, if I filter the data to only display the last two weeks' entries, I want the count of 'Pending' from that filtered subset, not from the entire database.

You provided a function, 'get_sql_filter_query,' which initially worked perfectly, displaying the count from the whole dataset upon opening the table view. When I applied filters, it correctly provided the count from the filtered data. However, when I use the quick search field in the table view, it filters the data based on my search term but doesn't return any results from the same query I used to get the count. It seems that the query might not be executed or might need adjustments to accommodate the quick search functionality.

I'm wondering if the function needs an update to consider the quick search feature or if there's an issue with how the query is executed during the search process. I hope you got my point. Thank

[pbottcher]

Hi,

I get you point, but need some help from your side to understand why it might not be working.

What do you get if you add a seach in the $queryWhere?

Can you show the data of the $queryWhere when you apply your filter and the searchstring you applied + the filters you applied.
Is the data being displayed that has a status pending if you apply that filter?

[snawaz]

Hi pböttcher,

I get nothing when I add a search in the $queryWhere. Means all my custom quires returned no count values.

Below is what being returned from $queryWhere = get_sql_filter_query($options); while adding search in the search field.

Code: Select all

string(1956) " AND (`incident_report`.`id` LIKE '%INC000001512194%' OR `incident_report`.`ref_id` LIKE '%INC000001512194%' OR `incident_report`.`network` LIKE '%INC000001512194%' OR `incident_report`.`nature_of_fault` LIKE '%INC000001512194%' OR if(`incident_report`.`start_time`,date_format(`incident_report`.`start_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR if(`incident_report`.`end_time`,date_format(`incident_report`.`end_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`status` LIKE '%INC000001512194%' OR `incident_report`.`root_cause` LIKE '%INC000001512194%' OR `incident_report`.`service_outage` LIKE '%INC000001512194%' OR `incident_report`.`outage_duration` LIKE '%INC000001512194%' OR `incident_report`.`action_to_restore` LIKE '%INC000001512194%' OR `incident_report`.`severity` LIKE '%INC000001512194%' OR `incident_report`.`hub_cascaded` LIKE '%INC000001512194%' OR `incident_report`.`wo` LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`salam_id`), CONCAT_WS('',   `site_db1`.`salam_id`), '') /* Salam Node */ LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`tawal_id`), CONCAT_WS('',   `site_db1`.`tawal_id`), '') /* Tawal ID */ LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`region`), CONCAT_WS('',   `site_db1`.`region`), '') /* Region */ LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`city`), CONCAT_WS('',   `site_db1`.`city`), '') /* City */ LIKE '%INC000001512194%' OR `incident_report`.`service_impact` LIKE '%INC000001512194%' OR `incident_report`.`responsibility` LIKE '%INC000001512194%' OR if(`incident_report`.`tt_creation_time`,date_format(`incident_report`.`tt_creation_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`tt_respond_time` LIKE '%INC000001512194%' OR `incident_report`.`month` LIKE '%INC000001512194%' OR `incident_report`.`tawal_ntt` LIKE '%INC000001512194%' OR `incident_report`.`remarks` LIKE '%INC000001512194%')"

Below is what being returned from $queryWhere = get_sql_filter_query($options); while adding filter.

Code: Select all

string(49) " AND ((`incident_report`.`status` <=> 'Pending'))"

Below are my custom quires for your reference.

Code: Select all

$_SESSION["pendingcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.status = "Pending" ' . $queryWhere);
$_SESSION["closedcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.status = "Closed" ' . $queryWhere);
$_SESSION["criticalcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.severity = "Critical" ' . $queryWhere);
$_SESSION["majorcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.severity = "Major" ' . $queryWhere);
$_SESSION["minorcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.severity = "Minor" ' . $queryWhere);
$_SESSION["allcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE 1=1 ' . $queryWhere);

Please check below line, where $FirstRecord variable is not being used anywhere. FYI

Code: Select all

if($Search_x != '') $FirstRecord = 1;

[snawaz]

Certainly, here's a revised version of your message:

Hello pböttcher,

I've identified an issue, but I'm unsure how to resolve it. When reviewing the query generated by $queryWhere = get_sql_filter_query($options);, it becomes apparent that during searches in the field below, some lookup fields from another table, named "site_db", are involved. When conducting searches, it seems to encompass all searchable fields. I believe this might be the point requiring attention.

Code: Select all

string(1956) " AND (`incident_report`.`id` LIKE '%INC000001512194%' OR `incident_report`.`ref_id` LIKE '%INC000001512194%' OR `incident_report`.`network` LIKE '%INC000001512194%' OR `incident_report`.`nature_of_fault` LIKE '%INC000001512194%' OR if(`incident_report`.`start_time`,date_format(`incident_report`.`start_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR if(`incident_report`.`end_time`,date_format(`incident_report`.`end_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`status` LIKE '%INC000001512194%' OR `incident_report`.`root_cause` LIKE '%INC000001512194%' OR `incident_report`.`service_outage` LIKE '%INC000001512194%' OR `incident_report`.`outage_duration` LIKE '%INC000001512194%' OR `incident_report`.`action_to_restore` LIKE '%INC000001512194%' OR `incident_report`.`severity` LIKE '%INC000001512194%' OR `incident_report`.`hub_cascaded` LIKE '%INC000001512194%' OR `incident_report`.`wo` LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`salam_id`), CONCAT_WS('',   `site_db1`.`salam_id`), '') /* Salam Node */ LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`tawal_id`), CONCAT_WS('',   `site_db1`.`tawal_id`), '') /* Tawal ID */ LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`region`), CONCAT_WS('',   `site_db1`.`region`), '') /* Region */ LIKE '%INC000001512194%' OR IF(    CHAR_LENGTH(`site_db1`.`city`), CONCAT_WS('',   `site_db1`.`city`), '') /* City */ LIKE '%INC000001512194%' OR `incident_report`.`service_impact` LIKE '%INC000001512194%' OR `incident_report`.`responsibility` LIKE '%INC000001512194%' OR if(`incident_report`.`tt_creation_time`,date_format(`incident_report`.`tt_creation_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`tt_respond_time` LIKE '%INC000001512194%' OR `incident_report`.`month` LIKE '%INC000001512194%' OR `incident_report`.`tawal_ntt` LIKE '%INC000001512194%' OR `incident_report`.`remarks` LIKE '%INC000001512194%')"

I attempted to apply filters specifically to those columns, but encountered the same problem—no results were returned when using the search function.

Could you please assist in addressing this issue?

[snawaz]

Hello pböttcher,

I wanted to update you that I have resolved my issue. The problem was in the SQL query itself; there was a missing JOIN clause. After adding the JOIN, both Filters and Search results are now reflecting in all my counts.

I have not made any changes to the function ($queryWhere = get_sql_filter_query($options);) you provided to me. Thank you so much for your support.

Below is my updated query, and with regards to the variable used to store the count result, I initially used the $_SESSION variable, and now I am utilizing the $GLOBALS variable.

Code: Select all

$GLOBALS["pendingcount"] = sqlvalue('SELECT count(1) FROM incident_report JOIN site_db site_db1 ON incident_report.salam_node = site_db1.id WHERE incident_report.status = "Pending" ' . $queryWhere);