Problem with Filter (Search).

All ApPHP DataGrid script bugs discussion. Questions and requests here.
Please, use a $debug_mode = true; before you say "Why Nothing Works ?!" (solves 90% of all issues)

Moderator: alexandrleonenko

Alon_A
Junior
Junior
Posts: 21
Joined: Feb 28th, '12, 15:56
Contact:

Problem with Filter (Search).

Postby Alon_A » Nov 15th, '12, 08:43

When I try to search my grid, I get error. I'm using the latest 7.7.7 version.

My SQL is:

Code: Select all

$sql=" SELECT x.eventID, x.producerID, z.fanPageID, REPLACE(x.eventName,'\"','')as eventName, (SELECT SUM(availableSeatsLeft) from e4s_stand_map WHERE eventID =  x.eventID GROUP BY x.eventID)as Seats, x.eventDescription, x.venueType, x.event_type, y.teamID, y.teamName ,x.eventDateTime, x.utcOffset ,x.isActive, x.eventImage, x.awayTeamName, '$t1' as fb
FROM e4s_event_details x LEFT JOIN e4s_team y ON x.teamID = y.teamID INNER JOIN e4s_event_producer z ON x.producerID = z.producerID
WHERE x.producerID= $producerID AND x.eventDateTime >= '$date2'";


The filter setting are:

Code: Select all

$filtering_option = true;
$dgrid->AllowFiltering($filtering_option);
 
$filtering_fields = array(
$text['eventName'] =>array("table"=>"x", "field"=>"eventName", "source"=>"self", "operator"=>true, "default_operator"=>"like", "type"=>"textbox",   
"case_sensitive"=>false,  "comparison_type"=>"string"),
$text['eventDateTime'] =>array('table'=>'x', 'type'=>'calendar', 'field'=>'eventDateTime',"operator"=>true, 'show_operator'=>'true', 'default_operator'=>'>',
'case_sensitive'=>'false', 'comparison_type'=>'string', 'width'=>'', 'on_js_event'=>'', 'default'=>'', 'calendar_type'=>'floating', 'date_format'=>'datedmy', 'field_type'=>''),
 );

$dgrid->SetFieldsFiltering($filtering_fields);


When searching by one of the fields or by both of them, I'm getting this error:

Image

Image

Image


Errors:

1) SELECT x.eventID, x.producerID, z.fanPageID, REPLACE(x.eventName,'"','')as eventName, (SELECT SUM(availableSeatsLeft) from e4s_stand_map WHERE eventID = x.eventID WHERE 1=1 AND ( LCASE(x.eventDateTime) > '2012-11-15' )GROUP BY x.eventID)as Seats, x.eventDescription, x.venueType, x.event_type, y.teamID, y.teamName ,x.eventDateTime, x.utcOffset ,x.isActive, x.eventImage, x.awayTeamName, 'Post' as fb FROM e4s_event_details x LEFT JOIN e4s_team y ON x.teamID = y.teamID INNER JOIN e4s_event_producer z ON x.producerID = z.producerID WHERE x.producerID= 8 AND x.eventDateTime >= '2012-11-15 10:33:31' ORDER BY eventDateTime DESC LIMIT 0, 5
[nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 AND ( LCASE(x.eventDateTime) > '2012-11-15' )GROUP BY x.eventID)as Sea' at line 1]

2) SELECT x.eventID, x.producerID, z.fanPageID, REPLACE(x.eventName,'"','')as eventName, (SELECT SUM(availableSeatsLeft) from e4s_stand_map WHERE eventID = x.eventID WHERE 1=1 AND ( LCASE(x.eventDateTime) > '2012-11-15' )GROUP BY x.eventID)as Seats, x.eventDescription, x.venueType, x.event_type, y.teamID, y.teamName ,x.eventDateTime, x.utcOffset ,x.isActive, x.eventImage, x.awayTeamName, 'Post' as fb FROM e4s_event_details x LEFT JOIN e4s_team y ON x.teamID = y.teamID INNER JOIN e4s_event_producer z ON x.producerID = z.producerID WHERE x.producerID= 8 AND x.eventDateTime >= '2012-11-15 10:33:31' ORDER BY eventDateTime DESC LIMIT 0, 5
[nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 AND ( LCASE(x.eventDateTime) > '2012-11-15' )GROUP BY x.eventID)as Sea' at line 1]


And also this warning (I dont have this warning at any other mode or action I preform with the grid, only when searching. the "awayteamname" is the last field in the VIEW MODE settings, if it helps somehow in understanding the problem):

Warnings:

1) Field awayteamname, used in the list of fields in View Mode was not found in SELECT SQL! Please, check carefully your code syntax and field name, it may be case sensitive!


We don't have other problems with the grid, only the search function.
I remember the search function worked just fine on previous version we've used (7.5.9).

If you need more information & code I will gladly update...
Will be glad for help, thanks a lot !

administrator
Site Admin
Site Admin
Posts: 6010
Joined: Jan 7th, '09, 23:18
Contact:

Re: Problem with Filter (Search).

Postby administrator » Nov 18th, '12, 07:14

The problems comes from the wrong insertion of

Code: Select all

WHERE 1=1 AND ( LCASE(x.eventDateTime) > '2012-11-15' )


Can you please send us SQL dump + your php file, so we could test this issue on our server?
You may send it via PM

reggiepr
Junior
Junior
Posts: 22
Joined: Apr 2nd, '11, 20:46

Re: Problem with Filter (Search).

Postby reggiepr » Nov 27th, '15, 00:04

Was this solved? I'm having the exact same issue and it seems to be related to the use of GROUP BY in a sub query.


Return to “ApPHP DataGrid {bugs}”