Custom Button function to return Grid Results for an SQL SELECT * WHERE search

[TechSupport]

Hello Forum,

Full disclosure: I am not a PHP programmer by any stretch of the imagination, but I am able to work my way through most PHP code examples created by the professionals to determine what in their code example requires changing to fit my application.

However, I have spent many hours trying to customise the DadaBik manual examples, and have also tried using the multiple examples that come up on the DadaBik forum when searching “global search”, but I just cannot get the code to work, specifically, the parsing of the SQL result and the webpage generation.

I can amend the URL generated by a Quick Search to simulate the functionality I require, and this works, so I am on the right track (the SQL statement below works), but I need help with the PHP/HTML.

So, and avoiding the specifics of my DadaBik application in an effort to make the responses useable to many others like me who would like to have this functionality, can someone please provide a generic example of a Custom Button PHP_STANDARD function which displays the results of an SQL statement as a Grid Results webpage using the current browser window (as appears when doing a Quick Search). The SQL statement could be something like;

SELECT * FROM table_a WHERE column_1 LIKE "%abcde%" OR column_2 LIKE "%abcde%";

A note for non-coders searching the forum on this topic: the SQL statement above will return all rows in ‘table_a’ where ‘column_1’ contains the text “adcde” OR ‘column_2’ contains the text “abcde” (the percent signs surrounding the text are 'wild-cards' and in this example/use mean anything can be before the text and anything can be after the text - aka. 'contains'). This functionality, a search with logical operators (such as OR, AND, NOT) over multiple columns, is not available in DadaBik without bespoke coding - though something similar is on the DadaBik feature request list, I believe. The feature is useful when you want to search many columns for the same text/data (aka. a ‘Global Search’).

Once I have a working example, I would like to go a step further with my application and have the search text (such as “abcde” in the above SQL Statement) come from a text box that is not associated with any database table/column but is displayed in the Quick Search form/area, and for the Custom Button to be visible only in the Quick Search/Filter menu. But, I feel that this should be a separate question in a separate post. For now, I would just like to see the 'hard-coded' SQL query results appear.

Thanks in advance and regards to all, Tech Support

DaDaBIK Version 11.6-Elba platinum, installed on 29/07/2022.
System info
PHP Version: 7.4.28
mysql version: 5.5.5-10.3.32-MariaDB-log
Web server: Apache/2.4.51 (Unix)
Client: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:109.0) Gecko/20100101 Firefox/110.0

 

[prettem]

Hi, an easy solution is to use a custom filter, I use it in that way:

// CUSTOM FILTERS (ONLY WORK WITH DaDaBIK Enterprise/Platinum)
$custom_filters['rechnungen'] = 'dadabik_custom_filter_aktives_jahr';
$custom_filters['messwerte'] = 'dadabik_custom_filter_aktives_jahr';
$custom_filters['tpl_rechnungen'] = 'dadabik_custom_filter_aktives_jahr';
function dadabik_custom_filter_aktives_jahr()
{
global $conn, $table_name;

$sql = "SELECT jahr FROM mandanten WHERE status = 'aktiv'";
$res_prepare = prepare_db($conn, $sql);
$res = execute_prepared_db($res_prepare,0);
$row = fetch_row_db($res_prepare);
$jahr = $row['jahr'];
if ($jahr !== '' && !is_null($jahr))
{
$filter = "$table_name.jahr = $jahr";
}
else
{
$filter = "1=1";
}
return $filter;
}

regards
M

 

[TechSupport]

Thanks for the response 'M',

Unfortunately, using a CUSTOM_FILTER ($filter) does not permit the filtering of multiple fields with logical operators (AND, OR) set in the $sql statement.

I have now given up on this and will wait until the pending DadaBik feature suggestion: https://dadabik.com/forum/index.php?threads/add-a-search-all-fields.24241/#post-24261 has been looked at.

If anyone is interested in this feature, please 'up vote' the feature suggestion (use the link above and click on the arrow to the right of the top post).

Regards, TechSupport