php filtering function for jquery table

I use jquery datatables in server side processing and I try to implement in php a function for my Global Filter:

Function should display all rows only if and only if:

-- both words are in the same column AND in the same row
-- both words are in different columns AND in the same row

So..If I type floor free or free floor on Global Filter it should returns always the SAME query.

You can test Global Filter here but this table don't load data from MySQL and I want replicate this client side filtering in php: DEMO

Instead, in my server side table relevant php code which controls Global Filter is:

static function filter ( $request, $columns, &$bindings ) { $globalSearch = array(); $columnSearch = array(); $dtColumns = self::pluck( $columns, 'dt' ); if ( isset($request['search']) && $request['search']['value'] != '' ) { $str = $request['search']['value']; for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) { $requestColumn = $request['columns'][$i]; $columnIdx = array_search( $requestColumn['data'], $dtColumns ); $column = $columns[ $columnIdx ]; if ( $requestColumn['searchable'] == 'true' ) { $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR ); //$globalSearch[] = "match(".$column['db'].") against (".$binding.")"; $globalSearch[] = "`".$column['db']."` LIKE ".$binding; } } }

What should i do?
I believe that I do explode the search string into separate words:

$words = explode(' ', $str); $wordcount = count($words);

and then my condition should look like:

WHERE IF(Column1 LIKE '%{$words[0]}%' OR Column2 LIKE '%{$words[0]}%' OR Column4 LIKE '%{$words[0]}%', 1, 0) + IF(Column1 LIKE '%{$words[1]}%' OR Column2 LIKE '%{$words[1]}%' OR Column4 LIKE '%{$words[1]}%', 1, 0) + IF(Column1 LIKE '%{$words[2]}%' OR Column2 LIKE '%{$words[2]}%' OR Column4 LIKE '%{$words[2]}%', 1, 0) + ... >= $wordcount

Each of the IF expressions should be 1 if that word is found in any of the columns. Adding them together will get the total number of words found in any column. Then test if this is at least the number of words (it could be more if the same word is in multiple columns).

But I don't understand how to use nested foreach loops to build the query from your input. Also I don't understand If I must use Table API

-------------Problems Reply------------

Hey I think I have an idea. But be careful it's untested. The function should var_dump an SQL statement like this:

CONCAT(column1, column2, column3) LIKE '%free%' AND
CONCAT(column1, column2, column3) LIKE '%floor%'

Concat joins the 3 given columns to one (you can concat as many columns as you want)

function filter ( $request, $columns, &$bindings )
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::pluck( $columns, 'dt' );

if ( isset($request['search']) && $request['search']['value'] != '' ) {
$string = $request['search']['value'];
$string = split(" ", $string);
$query = "SELECT * FROM table WHERE ";
$j = 0;
foreach($string as $str){
$concat = "CONCAT(";
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];

if ( $requestColumn['searchable'] == 'true' ) {
//$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
//$globalSearch[] = "match(".$column['db'].") against (".$binding.")";
$globalSearch[] = $column['db'];
$concat .= implode(",", $globalSearch) . ") LIKE '%" . $str . "%'";
if($j > 0 ){
$query .= " AND " . $concat;
$query .= $concat;
$globalSeach = array();

I hope my code can help you a little bit.

Category:php Views:1 Time:2018-02-11
Tags: php

Related post

  • How to add sort function for the table via ajax in ASP.NET MVC?What is the best practice 2010-06-10

    How to add sort function for the table via ajax in ASP.NET MVC?What is the best practice. If not use Ajax, it maybe much easier, just return View, but if use AJAX, what data structure should return?I just use Json to return the data, but i found each

  • small problem using PHP mail function with jQuery 2010-12-11

    I'm getting the following error when submitting an email contact form using PHP mail function and jQuery: SMTP server response: 554 The message was rejected because it contains prohibited virus or spam content in mail.php on line 26 Here is the code

  • codeigniter 2 uses pdo or php mysql function for it's active record? 2011-04-16

    Does CodeIgniter 2 use PDO or PHP MySQL functions for its active record class? --------------Solutions------------- CI does not use PDO. PDO in Codeigniter - Protect vs SQL Injection CI uses mysql_real_escape_string() to help guard against injection

  • list of available functions for jQuery(window) 2011-09-02

    where can i find a list of available standard functions for jQuery(window). I know about load and focus. --------------Solutions------------- Just open your console, type: console.log(jQuery(window)); Hit Enter (Ctrl-Enter in Firebug), and you'll see

  • Is there an "exists" function for jQuery? 2008-08-27

    How can I check the existence of an element in jQuery? The current code that I have is this: if ($(selector).length>0) { // Do something } Is there is a more elegant way to approach this? Perhaps a plugin or a function? --------------Solutions----

  • call php class functions through jquery 2010-11-12

    I have a dropdown which i want to fill through jquery. Problem that i am facing is that i want to call a class function directly from jquery. eg My class_function PHP class contains get_locations function. how can i call this get_locations function u

  • Wrapping around php filters function 2011-02-11

    I'm trying to make a function to wrap around php filters: Something like this: function validate($data, $args, $errorMsg) { } So you can validate data like this: $data = array( 'product_id' => 'libgd<script>', 'component' => '10', 'versio

  • Php Custom Function For Mysql Query Not Working 2012-01-16

    I have created two functions one for connecting to MySQL database and one for running a specific query. I enter the database name as parameter for first function to connect to the database, this works fine, but my problem is with the second one. 2nd

  • which PHP Framework use for jquery +HTML5 mobile website? 2012-04-27

    I have a made website in Zend Framework. Now I am making the same site with less functionality for mobile phones in jquery + HTML5. But which framework do I need to use for this ?? Because ZF slows my website very much, about 92% performance on Y-Slo

  • What are PHP nested functions for? 2009-01-06

    In JavaScript nested functions are very useful: closures, private methods and what have you.. What are nested PHP functions for? Does anyone use them and what for? Here's a small investigation I did <?php function outer( $msg ) { function inner( $

  • Writing a fast filtering function for a char ** 2011-03-31

    I'm trying to write a fast function for filtering a char ** and putting the results into another char ** This is what I have written: /* -filterable is what is to be filtered -filter is the filter -filtered is the result applying the filter to filter

  • MySQL, PHP filtering results within displayed table? 2011-07-04

    Thank you for your patience folks, Im a total novice & Ive searched through what "I think I should be searching for" - but not really understanding or getting anywhere, please could someone point me in the right direction, Id like to be able to f

  • Substitute for the .delay() function for jQuery 1.3 and making a jQuery loop? 2011-08-16

    Is there a substitute for the .delay() function in jQuery 1.3? I know in jQuery 1.4 you can simply use the .delay() function to delay a function from taking place for X amount of seconds but is there someway I can do the same thing but in 1.3? Also I

  • Write a namespaced function for jQuery that operates on parameters and not selectors 2011-11-08

    I've been tying to start a library of functions in jQuery to use transversally in my code I thought that a good way of doing this would be to build a jQuery namespace and add my methods under that (and provide the namespace for all who would like to

  • events or functions for jquery in backbone 2011-12-23

    I'm new to learning backbone, and in a lot of examples and answers to questions here on SO, I see a lot of people just adding regular jQuery or javascript into the render functions rather than following the backbone way of events: { 'draggable #drag_

  • Default error function for jQuery promise? 2013-07-11

    Is there a way to set up a default error function for a jQuery promise? I have a chain of functions to execute asynchronously, and if any of them fails, I want to report the error. Here's what I have to do now: function myErrorFunc() { // report erro

  • PHP $_POST function with a table 2011-01-22

    I have a table, in a grid of 4x4. Assume they're all black boxes. I now have it so that when I hover, they turn grey. However, how would I make it so that if you click on a table cell, it stays grey, and I can somehow pass that cell's coordinates to

  • PHP/RegEx - Logic for prepending table names 2009-01-17

    So I'm trying to build a script that automagically prepends valid column names with its appropriate table prefix (e.g. "t." or "r.") $t_columns = array('id', 'name', 'label'); $r_columns = array('related_value'); INPUT: id > 1 AND (name = 'Hello'

  • Php Javascript alternative for jquery grid 2009-06-24

    I want an alternative to a jquery grid for crud operations in php. I want to use a simple table with facilities such as searching, sorting an pagination. Is there any library for this? Can i use prototype.js for achieving this? Please guide me. -----

  • MySQL / PHP: Date functions for page view statistics and popularity 2009-07-30

    I have a table with number of page views per day. Something like this: +------+------------+------+ | id | date | hits | +------+------------+------+ | 4876 | 2009-07-14 | 4362 | +------+------------+------+ | 4876 | 2009-07-15 | 1324 | +------+-----

  • Getting data from URL (like php's $_GET) for jQuery/Ajax? 2010-11-03

    What I'm wanting to do is take a URL like and get each piece of data and use it in a jQuery function. Is there any built in way to do that? To clarify, when I navigate to (say, pasting the url into t

  • How do I combine functions for jQuery 2010-12-19

    On my input id='currency' First I want maskMoney for currency format $(function () { $("#currency").maskMoney(); }) Then auto submit $('#currency').change(function () { $(this).closest("form").submit(); }); How to make it work together? Update : Code

  • PHP Mail Function with Multiple Table Query 2011-10-01

    I'm working on a script that allows visitors to enter their email address to receive a notification upon a photo upload by other users. For testing purposes, I used this code to query just my "email" table, and it works perfectly when a new photo is

  • PHP Filtering Function 2011-10-14

    I've built a blog similar to wordpress. On my home page, I take the entire blog post, throw it through a function, and only display an excerpt of it. I want to go through and shrink my videos to a specific width/height. The code in the post could loo

Copyright (C), All Rights Reserved.

processed in 0.183 (s). 11 q(s)