filtering with more fields

hi folks,

today i wanted to add a third field to my filter, which already consists of an input field "device_name" and a select "site". the new filter field shoud be a select "device_type". my problem is, that i always get the "An error occured while communicating to the server." message when selecting something in my new select field.

here is the javascript-function:

// Re-load records with filtering $('#LoadRecordsButton').click(function (e) { e.preventDefault(); $('#devicesContainer').jtable('load', { device_name: $('#device_name').val(), site: $('#site').val(), device_type: $('#device_type').val() }); });

here is the php-code from the actions.php:

if($_GET["action"] == "list") {

    if(isset($_POST['device_type'])) {
        $device_types = "AND d.device_type = ".$_POST['device_type']."";
    }
    if($_POST['site'] > -1) $sites = "AND d.site = ".$_POST['site']."";

    if(empty($_POST['device_name'])) {
        $result = mysql_query("SELECT *, (SELECT SUM(a.time_lasting) AS sum_time_lasting 
                                                                        FROM addons_2_devices AS a2d 
                                                                            JOIN addons AS a ON a2d.addon_id = a.id 
                                                                        WHERE a2d.device_id = d.id) AS sum_addons 
                                                 FROM devices AS d 
                                                 WHERE d.project_id = ".$project_id." ".$sites." ".$device_types." 
                                                 ORDER BY ".$_GET["jtSorting"]."");
    } else {
        $result = mysql_query("SELECT *, (SELECT SUM(a.time_lasting) AS sum_time_lasting 
                                                                        FROM addons_2_devices AS a2d 
                                                                            JOIN addons AS a ON a2d.addon_id = a.id 
                                                                        WHERE a2d.device_id = d.id) AS sum_addons 
                                                 FROM devices AS d 
                                                 WHERE d.project_id = ".$project_id." ".$sites." ".$device_types." 
                                                     AND d.device_name LIKE '%".$_POST['device_name']."%' 
                                                 ORDER BY ".$_GET["jtSorting"]."");
    }
    $rows = array();
    while($row = mysql_fetch_array($result))
    {
        $rows[] = array_map('utf8_encode', $row);
    }
    $jTableResult = array();
    $jTableResult['Result'] = "OK";
    $jTableResult['Records'] = $rows;
    print json_encode($jTableResult);
}

There's probably something in the SQL which requires a few grey cells to debug. Firstly I observe your php is not handling any mysql errors or even empty results. There is mechanism to return a proper error to jTable, so I would put a little effort there.

I've surprised myself with this error in the past when developing on a LOCAL server. Two tricks which might help you debug yourself. First I change the action line to jt.debug.php which is very simple

<?php
date_default_timezone_set("Europe/London");
$buffer['Result'] = "ERROR";
$buffer['Message'] = "php Debugging: " . $_SERVER['QUERY_STRING'] . "  Post : " . var_export($_POST, true);
echo json_encode($buffer);
?>

This way I see in the jt error dialog exactly what php sees, so I check the javascript outgoing is correct. Secondly, as a matter of taste I always build my SQL string into a variable. I would then add the line

$jTableResult['sql'] = $sql;

and look in Firebug to see the query which I'd cut and paste into mysql browser and debug there.

Hope all this helps you out, even if not directly debugging your code.

volosoft / jtable

filtering with more fields #1247