Modified ssp.class.php to support mysql variable not working

dragon82

Hello People,

I have downloaded the modified version of ssp.class.php from the internet due to the reason that I wanted to use mysql variable (set @var) I changed the class:

static function simple($request, $sql_details, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '', $varDeclare = '') {
$bindings = array();
$db = SSP::sql_connect($sql_details);

    // Build the SQL query string from the request
    $limit = SSP::limit($request, $columns);
    $order = SSP::order($request, $columns, $joinQuery);
    $where = SSP::filter($request, $columns, $bindings, $joinQuery);

    // IF Extra where set then set and prepare query
    if ($extraWhere) {
        $extraWhere = ($where) ? ' AND ' . $extraWhere : ' WHERE ' . $extraWhere;
    }

    // Main query to actually get the data
    if ($joinQuery) {

        $col = SSP::pluck($columns, 'db', $joinQuery);

        $query =    "$varDeclare " .
                    "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $col) . "
                    $joinQuery
                    $where
                    $extraWhere
                    $groupBy
                    $order
                    $limit";
    } else {

        $query =    "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", SSP::pluck($columns, 'db')) . "
                    FROM `$table`
                    $where
                    $extraWhere
                    $groupBy
                    $order
                    $limit";
    }

    $data = SSP::sql_exec($db, $bindings, $query);

    // Data set length after filtering
    $resFilterLength = SSP::sql_exec($db, "SELECT FOUND_ROWS()"
    );
    $recordsFiltered = $resFilterLength[0][0];

    // Total data set length
    $resTotalLength = SSP::sql_exec($db, "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`"
    );
    $recordsTotal = $resTotalLength[0][0];


    /*
     * Output
     */
    return array(
        "draw" => intval($request['draw']),
        "recordsTotal" => intval($recordsTotal),
        "recordsFiltered" => intval($recordsFiltered),
        "data" => SSP::data_output($columns, $data, $joinQuery)
    );
}

My server side processing :

<?php $table = 'MOB_BUYER'; $primaryKey = 'BUYER_ID'; $columns = array( array('db' => '`B`.`BUYER_ID`', 'dt' => 'BUYER_ID', 'field' => 'ID', 'as' => 'ID'), array('db' => '`B`.`BUYER_NAME`', 'dt' => 'BUYER_NAME', 'field' => 'NAME', 'as' => 'NAME'), array('db' => '(SELECT `GR`.`GENDER` FROM `MOB_GENDER_REF` AS `GR` WHERE `B`.`BUYER_GENDER` = `GR`.`GENDER_ID`)', 'dt' => 'GENDER', 'field' => 'GENDER', 'as' => 'GENDER'), array('db' => '(TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0))', 'dt' => 'AGE', 'field' => 'AGE', 'as' => 'AGE'), array('db' => '`B`.`BUYER_MOBILE_NO`', 'dt' => 'MOBILE_NO', 'field' => 'MOBILE_NO', 'as' => 'MOBILE_NO'), array('db' => '(SELECT `CR`.`COUNTRY_NAME` FROM `MOB_COUNTRY_REF` AS `CR` WHERE `CR`.`COUNTRY_ID` = `B`.`COUNTRY` )', 'dt' => 'COUNTRY', 'field' => 'COUNTRY', 'as' => 'COUNTRY'), array('db' => '(SELECT `CSR`.`STATE_NAME` FROM `MOB_COUNTRY_STATE_REF` AS `CSR` WHERE `CSR`.`STATE_ID` = `B`.`STATE` AND `CSR`.`COUNTRY_ID` = `B`.`COUNTRY`)', 'dt' => 'STATE', 'field' => 'STATE', 'as' => 'STATE'), array('db' => '`B`.`BUYER_EMAIL`', 'dt' => 'EMAIL', 'field' => 'EMAIL', 'as' => 'EMAIL') ); // SQL server connection information $sql_details = array( 'user' => 'user', 'pass' => 'pass', 'db' => 'jack', 'host' => '127.0.0.1' ); require('ssp.class.php' ); $joinQuery = "FROM `MOB_BUYER` AS `B`"; $extraWhere = '(' . 'IF((@gender IS NULL) || (@gender = "") || (@gender < 0 && @gender > `B`.`BUYER_GENDER`), (`B`.`BUYER_GENDER` LIKE "%"), (`B`.`BUYER_GENDER` = @gender)) ' . 'AND ' . 'IF((@agefrom IS NULL) || (@agefrom = "") || (@agefrom < 0 && @agefrom > (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) LIKE "%", (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) >= @agefrom) ' . 'AND ' . 'IF((@ageto IS NULL) || (@ageto = "") || (@ageto < 0 && @ageto > (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) LIKE "%", (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) <= @ageto) ' . 'AND ' . 'IF((@mobile IS NULL) || (@mobile = "") && (@mobile != TRUE && @mobile != FALSE), (`B`.`BUYER_MOBILE_NO` LIKE "%"), IF(@mobile = TRUE, (CHAR_LENGTH(`B`.`BUYER_MOBILE_NO`) >= @mobile && CAST(`B`.`BUYER_MOBILE_NO` AS UNSIGNED) > 0), (`B`.`BUYER_MOBILE_NO` = 0))) ' . 'AND ' . 'IF((@country IS NULL) || (@country = ""), (`B`.`COUNTRY` LIKE "%"), FIND_IN_SET(`B`.`COUNTRY`, @country)) ' . 'AND ' . 'IF((@state IS NULL ) || (@state = ""), (`B`.`STATE` LIKE "%"), FIND_IN_SET(`B`.`STATE`, @state)) ' . 'AND ' . 'IF((@email IS NULL) || (@email = "") && (@email != TRUE && @email != FALSE), (`B`.`BUYER_EMAIL` LIKE "%"), IF(@email = TRUE, (CHAR_LENGTH(`B`.`BUYER_EMAIL`) >= @email), (`B`.`BUYER_EMAIL` = "" OR `B`.`BUYER_EMAIL` IS NULL)))' . ')'; $groupBy = ''; $varDeclare = 'SET @gender = NULL; ' . 'SET @agefrom = NULL; ' . 'SET @ageto = NULL; ' . 'SET @mobile = NULL; ' . 'SET @country = NULL; ' . 'SET @state = NULL; ' . 'SET @email = NULL; '; echo json_encode( SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy, $varDeclare) //SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy) //SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery) //SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere) ); ?>

I am getting blank result.

Anyone mind lend a helping hand?

Thanks

ignignokt

If you are getting a blank result the best thing to do would probably be to echo the SQL that is being ran, and running it in MySQL Workbench or something to make sure it is valid and that results are returned. That way you can also look over the whole query and see where something is being messed up rather than picturing it in your head.

You can also take a look at my example, which lets you use any valid query you can come up with (including SQL variables) by simply letting you write it yourself instead of it being built dynamically and being restricted by what the author allows the dynamic SQL builder to do.

dragon82

Hello ignignokt,

Here my echo query from from inside of the simple() function which belong to ssp.class.php which I have modified earlier:

SET @gender = NULL; SET @agefrom = NULL; SET @ageto = NULL; SET @mobile = NULL; SET @country = NULL; SET @state = NULL; SET @email = NULL; SELECT SQL_CALC_FOUND_ROWS B.BUYER_ID AS ID, B.BUYER_NAME AS NAME, (SELECT GR.GENDER FROM MOB_GENDER_REF AS GR WHERE B.BUYER_GENDER = GR.GENDER_ID) AS GENDER, (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) AS AGE, B.BUYER_MOBILE_NO AS MOBILE_NO, (SELECT CR.COUNTRY_NAME FROM MOB_COUNTRY_REF AS CR WHERE CR.COUNTRY_ID = B.COUNTRY) AS COUNTRY, (SELECT CSR.STATE_NAME FROM MOB_COUNTRY_STATE_REF AS CSR WHERE CSR.STATE_ID = B.STATE AND CSR.COUNTRY_ID = B.COUNTRY) AS STATE, B.BUYER_EMAIL AS EMAIL FROM MOB_BUYER AS B WHERE (IF((@gender IS NULL) || (@gender = '') || (@gender < 0 && @gender > B.BUYER_GENDER), (B.BUYER_GENDER LIKE '%'), (B.BUYER_GENDER = @gender)) AND IF((@agefrom IS NULL) || (@agefrom = '') || (@agefrom < 0 && @agefrom > (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) LIKE '%', (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) >= @agefrom) AND IF((@ageto IS NULL) || (@ageto = '') || (@ageto < 0 && @ageto > (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) LIKE '%', (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) <= @ageto) AND IF((@mobile IS NULL) || (@mobile = '') && (@mobile != TRUE && @mobile != FALSE), (B.BUYER_MOBILE_NO LIKE '%'), IF(@mobile = TRUE, (CHAR_LENGTH(B.BUYER_MOBILE_NO) >= @mobile && CAST(B.BUYER_MOBILE_NO AS UNSIGNED) > 0), (B.BUYER_MOBILE_NO = 0))) AND IF((@country IS NULL) || (@country = ''), (B.COUNTRY LIKE '%'), FIND_IN_SET(B.COUNTRY, @country)) AND IF((@state IS NULL ) || (@state = ''), (B.STATE LIKE '%'), FIND_IN_SET(B.STATE, @state)) AND IF((@email IS NULL) || (@email = '') && (@email != TRUE && @email != FALSE), (B.BUYER_EMAIL LIKE '%'), IF(@email = TRUE, (CHAR_LENGTH(B.BUYER_EMAIL) >= @email), (B.BUYER_EMAIL = '' OR B.BUYER_EMAIL IS NULL))))

Is it the PHP PDO do not allow set variable/ variable declaration query such as "SET @VAR = 1;"?

If yes, anyone know a work around for this problem?

ignignokt

I'm not overly familiar with PDO, but you might need to execute everything in one query. We use DAO and I set variables by setting them inside the same query as my select. You can set the variables inside the query with a join like so:

JOIN
(
    SELECT 
        @gender := NULL,
        @agefrom := NULL,
        @ageto := NULL,
        @mobile := NULL,
        @country := NULL,
        @state := NULL,
        @email := NULL
) r