Original Source
https://pdocrud.com/demo/pages/filters
https://pdocrud.com/demo/pages/set-search-cols
https://pdocrud.com/demo/pages/autosuggestion
https://pdocrud.com/demo/pages/sql
https://pdocrud.com/demo/pages/where-condition
https://pdocrud.com/demo/pages/date-range-report
https://pdocrud.com/demo/pages/search-col-data-type
https://pdocrud.com/demo/pages/subselect-sql
Advance Filter Options
Apart from column based search, you can add the advance filter options in PDOCrud. You can specify different filter and connect to different columns of table. You can also specify type of filter and how it should work. Currently it support three types of controls - dropdown, radio button and textbox. More options will be available in future versions.
$pdocrud = new PDOCrud();
//add Filter ("unique-filter-name", "Filter display name", "column name to be matched","type of filter")
$pdocrud->addFilter("product_cat_filter", "Product Category", "product_cat", "radio");
//set data for filter ("unique-filter-name",array of data or table,key (if source=db),value (if source=db), "source_type")
$pdocrud->setFilterSource("product_cat_filter", array("Electronic" => "Electronic", "Fashion" => "Fashion"), "", "", "array");
$pdocrud->addFilter("ProductLineFilter", "Product Line", "product_line", "dropdown");
$pdocrud->setFilterSource("ProductLineFilter", "products", "product_line", "product_line as pl", "db");
$pdocrud->addFilter("ProductVendorFilter", "Vendor", "ProductVendor", "text");
$pdocrud->setFilterSource("ProductVendorFilter", "", "", "", "");
echo $pdocrud->dbTable("products")->render();
Set Search Columns (Instead of All)
You can set which search columns to be shown instead of showing all columns of table in search box.
$pdocrud = new PDOCrud();
$pdocrud->setSearchCols(array("id","first_name"));
echo $pdocrud->dbTable("employee")->render();
Auto Suggestion (In Search Box)
You can add auto suggestion in search box that allows you to type the text and based on text, records will be shown as suggestion. You can enable/disable auto suggestion in config file. Please note that auto suggestion works with specific column not for "All" option in column dropdown of search box. You need to type atleast 2 character to view auto suggestion search result.
$pdocrud = new PDOCrud();
//enable the auto suggestion and hide the "All" text from the search box
$pdocrud = new PDOCrud(false, "", "", array("autoSuggestion" => true, "showAllSearch" => false));
echo $pdocrud->dbTable("orders")->render();
Where condition
You can add where condition to filter records using where() function. In simplest term, you can pass the column name and value to apply where condition, default "=" (equalto) operator will be used if you don't pass any operator as third parameter
Below example shows how to generate queries like WHERE `order_date` > ? AND `order_amount` >= ? AND ( `order_status` = ? OR `order_status` = ? )
$pdocrud = new PDOCrud();
/**
* Add where condition
* @param string $colName column name for which where condition to be applied
* @param string $val value of column
* @param string $operator any operator like =, !=, default value is "="
* @param string $andOroperator whether to use "and" or "or" operator, if empty, default andOrOperator = "and" will be used
* @param string $bracket whether to use opening "(" or closing bracket ")", leave empty if not required
* return object Object of class
*/
$pdocrud->where("order_date", "2015-09-08", ">");
$pdocrud->where("order_amount", 50, ">=");
$pdocrud->where("order_status", "Completed", "=", "" ,"(");
$pdocrud->where("order_status", "Pending", "=", "OR" ,")");
echo $pdocrud->dbTable("orders")->render();
Date range wise report
You can directly add month wise, day wise, year wise (calendar as well day duration wise) report buttons to generate the table data based on the date range. Please note it work for the CRUD table not for SQL as sql statement can be of anytype.
$pdocrud = new PDOCrud();
/**
* Add date range report buttons (eg daily ,monthly ,yearly report button)
* @param string $text Name/Text of the button
* @param string $type Type of the report to be generated.
* return object Object of class
*/
$pdocrud->addDateRangeReport("This Year", "calendar_year", "order_date");
$pdocrud->addDateRangeReport("This Month", "calendar_month", "order_date");
$pdocrud->addDateRangeReport("Last 365 days", "year", "order_date");
$pdocrud->addDateRangeReport("Last 30 days", "month", "order_date");
$pdocrud->addDateRangeReport("1 Day", "Last 1 day", "order_date");
$pdocrud->addDateRangeReport("Today", "today", "order_date");
echo $pdocrud->dbTable("orders")->render();
Set Search Col Data Type
You can set search col data type to date/datetime/time etc to make search more friendly. Default type is text. For example When you set search column data type to date-range, it will show date range options.
$pdocrud = new PDOCrud();
//set the search column data type
$pdocrud->setSearchColumnDataType("order_date", "date-range");// other options are time-range, datetime-range
echo $pdocrud->dbTable("orders")->render();
SQL Operation
Render sql helps to display the data in grid/table format. You can write sql select statement to display data in the grid format. Please note that since sql statement can be of any type so the default grid function will not work with this sql render. From version 2.4, the pagination, records per page and display of total records is also removed. A better option to use the sql render with the jquery datatable plugin. You can see example here
$pdocrud = new PDOCrud();
$pdocrud->setQuery("select * from orders");
echo $pdocrud->render("SQL");
Subselect SQL
You can use the subselect query to generate columns dynamically. You can get data from other tables using the query. It accepts two parameters, first one is column name/alias and second is query.
$pdocrud = new PDOCrud();
$pdocrud->crudTableCol(array("first_name","last_name"));
/**
* Allows you to add the dynamic column based on sub query
* @param string $columnName Alias column name to be used for the query
* @param string $query Sub Query to be used
* return object Object of class
*/
$pdocrud->subQueryColumn("order_id", "select sum(id) from orders where customer_name = {user_name}");
echo $pdocrud->dbTable("users")->render()
