Migration problem

[daebak74]

Hi, It's hours now that I try everything but nothing. I hope to find help for this problem.

The problem This class work and I use for populate a table from db. I load the framework too and no problem. Below e test file and the class. When I use in controller I receive a SyntaxError JSON.parse unexpected end of data at line 1 of the JSON data and I can't take any output. :(

I know that I should to create a new DB connection but just for try it

test.php

$autoLoadPath = __DIR__.'/vendor/autoload.php';

// if the Composer autoloader has been generated
if (file_exists($autoLoadPath)) {
    // include the autoloader that loads all dependencies
    require $autoLoadPath;
}

// load the configuration into the environment variables
$envLoader = new Dotenv\Dotenv(__DIR__.'/config');
$envLoader->overload();

$app = new \Delight\Foundation\App(__DIR__.'/storage/app', __DIR__.'/views', __DIR__.'/storage/framework');

use Delight\Foundation\App;

require ('ssp.class.php');

//$app->setContentType('json');

$sql_details = array(
    'user' => 'USER',
    'pass' => 'PASS',
    'db'   => 'DB',
    'host' => 'HOST'
);

$table = 'users';
$primaryKey = 'id';

$columns = array(
    array('db' => 'id', 'dt' => 'id')
);

$_POST['columns'][0]['data'] = '';
$_POST['columns'][0]['name'] = '';
$_POST['columns'][0]['searchable'] = false;
$_POST['columns'][0]['orderable'] = false;
$_POST['columns'][0]['search']['value'] = '';
$_POST['columns'][0]['search']['regex'] = false;

$_POST['columns'][1]['data'] = '';
$_POST['columns'][1]['name'] = '';
$_POST['columns'][1]['searchable'] = false;
$_POST['columns'][1]['orderable'] = false;
$_POST['columns'][1]['search']['value'] = '';
$_POST['columns'][1]['search']['regex'] = false;

$_POST['columns'][2]['data'] = 'id';
$_POST['columns'][2]['name'] = '';
$_POST['columns'][2]['searchable'] = true;
$_POST['columns'][2]['orderable'] = true;
$_POST['columns'][2]['search']['value'] = '';
$_POST['columns'][2]['search']['regex'] = false;

$_POST['order'][0]['column'] = '2';
$_POST['order'][0]['dir'] = 'asc';
$_POST['draw'] = 1;

$_POST['search']['value'] = '';
$_POST['search']['regex'] = false;
$_POST['start'] = 0;
$_POST['length'] = 10;

$d = SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns );

//var_dump ($d);
echo (json_encode($d));

ssp.class.php

<?php

class SSP {
    /**
     * Create the data output array for the Tables rows
     *
     */
    static function data_output ( $columns, $data )
    {
        $out = array();

        for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
            $row = array();

            for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
                $column = $columns[$j];

                // Is there a formatter?
                if ( isset( $column['formatter'] ) ) {
                    $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
                }
                else {
                    $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
                }
            }

            $out[] = $row;
        }

        return $out;

    }

    /**
     * Database connection
     *
     * Obtain an PHP PDO connection from a connection details array
     *
     */
    static function db ( $conn )
    {
        if ( is_array( $conn ) ) {
            return self::sql_connect( $conn );
        }

        return $conn;
    }

    /**
     * Paging
     *
     * Construct the LIMIT clause for server-side processing SQL query
     *
     */
    static function limit ( $request, $columns )
    {
        $limit = '';

        if ( isset($request['start']) && $request['length'] != -1 ) {
            $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
        }

        return $limit;
    }

    /**
     * Ordering
     *
     * Construct the ORDER BY clause for server-side processing SQL query
     *
     */
    static function order ( $request, $columns )
    {
        $order = '';

        if ( isset($request['order']) && count($request['order']) ) {
            $orderBy = array();
            $dtColumns = self::pluck( $columns, 'dt' );

            for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
                // Convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];

                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];

                if ( $requestColumn['orderable'] == 'true' ) {
                    $dir = $request['order'][$i]['dir'] === 'asc' ?
                        'ASC' :
                        'DESC';

                    $orderBy[] = '`'.$column['db'].'` '.$dir;
                }
            }

            $order = 'ORDER BY '.implode(', ', $orderBy);
        }

        return $order;
    }

    /**
     * Searching / Filtering
     *
     * Construct the WHERE clause for server-side processing SQL query.
     *
     */
    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[] = "`".$column['db']."` LIKE ".$binding;
                }
            }
        }

        // Individual column filtering
        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            $str = $requestColumn['search']['value'];

            if ( $requestColumn['searchable'] == 'true' &&
             $str != '' ) {
                $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }

        // Combine the filters into a single string
        $where = '';

        if ( count( $globalSearch ) ) {
            $where = '('.implode(' OR ', $globalSearch).')';
        }

        if ( count( $columnSearch ) ) {
            $where = $where === '' ?
                implode(' AND ', $columnSearch) :
                $where .' AND '. implode(' AND ', $columnSearch);
        }

        if ( $where !== '' ) {
            $where = 'WHERE '.$where;
        }

        return $where;
    }

    /**
     * Perform the SQL queries needed for an server-side processing requested,
     * utilising the helper functions of this class, limit(), order() and
     * filter() among others. The returned array is ready to be encoded as JSON
     * in response to an SSP request, or can be modified if needed before
     * sending back to the client.
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array|PDO $conn PDO connection resource or connection parameters array
     *  @param  string $table SQL table to query
     *  @param  string $primaryKey Primary key of the table
     *  @param  array $columns Column information array
     *  @return array          Server-side processing response array
     */
    static function simple ( $request, $conn, $table, $primaryKey, $columns )
    {
        $bindings = array();
        $db = self::db( $conn );

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

        // Main query to actually get the data
        $data = self::sql_exec( $db, $bindings,
            "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
             FROM `$table`
             $where
             $order
             $limit"
        );

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

        // Total data set length
        $resTotalLength = self::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"            => self::data_output( $columns, $data )
        );
    }

    /**
     * Connect to the database
     */
    static function sql_connect ( $sql_details )
    {
        try {
            $db = @new PDO(
                "mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
                $sql_details['user'],
                $sql_details['pass'],
                array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
            );
        }
        catch (PDOException $e) {
            self::fatal(
                "An error occurred while connecting to the database. ".
                "The error reported by the server was: ".$e->getMessage()
            );
        }

        return $db;
    }

    /**
     * Execute an SQL query on the database
     */
    static function sql_exec ( $db, $bindings, $sql=null )
    {
        // Argument shifting
        if ( $sql === null ) {
            $sql = $bindings;
        }

        $stmt = $db->prepare( $sql );
        //echo $sql;

        // Bind parameters
        if ( is_array( $bindings ) ) {
            for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
                $binding = $bindings[$i];
                $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
            }
        }

        // Execute
        try {
            $stmt->execute();
        }
        catch (PDOException $e) {
            self::fatal( "An SQL error occurred: ".$e->getMessage() );
        }

        // Return all
        return $stmt->fetchAll();
    }

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Internal methods
     */

    /**
     * Throw a fatal error.
     */
    static function fatal ( $msg )
    {
        echo json_encode( array( 
            "error" => $msg
        ) );

        exit(0);
    }

    /**
     * Create a PDO binding key which can be used for escaping variables safely
     * when executing a query with sql_exec()
     */
    static function bind ( &$a, $val, $type )
    {
        $key = ':binding_'.count( $a );

        $a[] = array(
            'key' => $key,
            'val' => $val,
            'type' => $type
        );

        return $key;
    }

    /**
     * Pull a particular property from each assoc. array in a numeric array, 
     * returning and array of the property values from each item.
     */
    static function pluck ( $a, $prop )
    {
        $out = array();

        for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
            $out[] = $a[$i][$prop];
        }

        return $out;
    }

    /**
     * Return a string from an array or a string
     */
    static function _flatten ( $a, $join = ' AND ' )
    {
        if ( ! $a ) {
            return '';
        }
        else if ( $a && is_array($a) ) {
            return implode( $join, $a );
        }
        return $a;
    }
}

WORK and Result

{"draw":1,"recordsTotal":1,"recordsFiltered":1,"data":[{"id":"5"}]}

Now in production

Controller

<?php

namespace App\Controllers\Backend\Core;

    use Delight\Foundation\App;

    class Users {

        public static function getUsers(App $app) {
            // do something

            require('./app/table_fields/backend/core/users_fields.php');

            echo $app->view('/backend/'. $_SESSION['BE_THEME'] .'/core/users.html.twig', []);

        }

        public static function postUsers(App $app) {
            // do something

            require('./app/controllers/backend/core/ssp.class.php');

            $app->setContentType('json');

            // SQL server connection information
            $sql_details = array(
                'user' => 'USER',
                'pass' => 'PASS',
                'db'   => 'DB',
                'host' => 'HOST'
            );

            // DB table to use
            $table = 'users';

            // Table's primary key
            $primaryKey = 'id';

            $columns = array(

                array(
                    'db'        => 'id',
                    'dt'        => 'id',
                    'formatter' => function( $d, $row ) {
                        return '$'.number_format($d);
                    }
                )

            );

            //echo($_POST['columns'][2]['data']);

            $d = SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns );

            die(json_encode($d));

[POST by Ajax]

and the class above at the top of the class I added

namespace App\Controllers\Backend\Core;

Where I make mistake and how I can solve

Thanks

[ocram]

Well, that is a lot of code, and it doesn’t seem specific to this framework. But let’s see …

What’s your exact problem here? Is it that you get

{"draw":1,"recordsTotal":1,"recordsFiltered":1,"data":[{"id":"5"}]}

with the (old) test file while you get

SyntaxError JSON.parse unexpected end of data at line 1

with this framework?

Can you just check the target URL of your AJAX request in your browser and see what the actual JSON output is? Can you also open the developer tools of your browser, switch to the “Network” tab and reload the page? What MIME type does it report for the response?

[daebak74]

If I get the error from my class I didn't wrote here. The first example is the class out of the framework but I loaded the libraries foundation. Thee result it's fine and return the Json.

If I load the same class in controller with the framework I receive the error.

Yes of coarse I checked with the firebug networks and the content type is correct JSON

But the problem I don't get any even if from controller I try to make a dump nothing. How I can debug for check what is the problem and than I really don't understand why.If in the file work should work inside the controller too.

Thanks

[ocram]

There really isn’t any reason why this should not work in the controller. So I guess the cause for this problem must be quite simple, we just have to find it.

First, please don’t rely on the AJAX request suceeding or any JavaScript logic to work correctly. Just go directly to the URL your AJAX request is trying to fetch and inspect its contents. You say you have already checked the content type for that URL and it’s JSON as expected. Now what about the response body? Is the visible output really the JSON you expect?

Perhaps there is unexpected output before or after the JSON, e.g. error messages or warnings. Or the JSON output is truncated for some reason and the response is thus not complete.

[daebak74]

Thanks for the supporting I really tried all. The problem is that the page is totaly empty. The error is in console for the json_encode because the json is empty.

I am trying to recovert everything with your $app->db() for simplified the code but I didn't study it well. By the way If you copy and past the class and test.php and ssp.class.php you can see that work. Maybe give conflict my db connction with the connection of the framework ?

Before I working with mysqli but now that I am thinking PDO support only one instance ?

Anyway don't worry I rewrite the intere class using your $app->db method

[daebak74]

Ok I rewrote the entire class and in controller work I tried with a simple select but how I can convert the bind paramenter (sql_exec function)

this is the main function

// Main query to actually get the data

$data = self::sql_exec($app, $bindings,
    "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
     FROM `$table`
     $where
     $order
     $limit"
);

the qery is something like this: SELECT SQL_CALC_FOUND_ROWSidFROMusersORDER BYidASC LIMIT 0, 10

and this is the function

static function sql_exec ( $app, $bindings, $sql=null )
    {
        // Argument shifting
        if ( $sql === null ) {
            $sql = $bindings;
        }

        $stmt = $db->prepare( $sql );

        // Bind parameters
        if ( is_array( $bindings ) ) {
            for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
                $binding = $bindings[$i];
                $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
            }
        }

        // Execute
        try {
            $stmt->execute();
        }
        catch (PDOException $e) {
            self::fatal( "An SQL error occurred: ".$e->getMessage() );
        }

        // Return all
        return $stmt->fetchAll();
    }

Thanks

[ocram]

The database component has detailed documentation here and allows for the execution of arbitrary statements as well.

But just for this specific problem, it shouldn’t matter if you are using your existing database component or the one from this framework.

I still haven’t seen that you have checked the URL that is fetched via AJAX and its textual content. Do you have APP_DEBUG=1 in your configuration? Then you should see errors or warnings on that page that will help you.

Either the script outputs the correct content or not. If not, there must be an error or problem somewhere that can be debugged.

[daebak74]

Debug is enabled. The class work if I just put Array() return the json empty. The new PDO produce in console the error" ....unespected END OF DATA AT LINE 1 COLUMN 1 O THE JSON"

the page is the same /admin/users for GET and POST this is the rooute

$app->get('/admin/users', ['\App\Controllers\Backend\Core\Users', 'getUsers']);
$app->post('/admin/users', ['\App\Controllers\Backend\Core\Users', 'postUsers']);

this is the controller

namespace App\Controllers\Backend\Core;

use Delight\Foundation\App;

class Users {

    public static function getUsers(App $app) {

        echo $app->view('/backend/'. $_SESSION['BE_THEME'] .'/core/users.html.twig', []);

    }

    public static function postUsers(App $app) {

        require('./app/controllers/backend/core/ssp.class.php');

        $app->setContentType('json');

        // SQL server connection information
        $sql_details = array(
            'user' => 'USER',
            'pass' => 'PASS',
            'db'   => 'DB',
            'host' => 'HOST'
        );

        $d = SSP::simple( $sql_details);

        echo(json_encode($d));

    }

}

this is the class

namespace App\Controllers\Backend\Core;

use Delight\Foundation\App;

class SSP {

    static function simple ($sql_details)
    {

        return array();  // IF I RETURN ONLY THE ARRAY WORK

        // THIS GIVE THE ERROR
        $db = @new PDO(
            "mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
            $sql_details['user'],
            $sql_details['pass'],
            array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
        );

        return array();

    }

}

[ocram]

As far as I can see, both POST test.php and POST /admin/users return a JSON response and should in fact even have an identical response, right?

If you only change the URL that your AJAX request goes to and then that syntax error appears, your POST /admin/users is definitely not returning the response you’re expecting. That’s all one needs to know. No need to look anywhere else for the problem. Why don’t you finally inspect and compare the two responses (both header and body)?

Alternatively, if you’re using jQuery, you could change the data type to an explicit dataType: "text" and then immediately alert or log (or catch in the debugger) what has been fetched.

[daebak74]

Yes I got it. Doesn't return anything but of coarse and sorry. In old my script I had a custom error handler so if prepend (@) in front of any function in this case new PDO the error will be ignored and store in a variable.

I removed it and return this error:

Whoops\Exception\ErrorException: Class 'App\Controllers\Backend\Core\PDO' not found in file ... ssp.class.php on line 38

FIXED ADDING BEFORE SPP CLASS "use PDO;"

namespace App\Controllers\Backend\Core; use Delight\Foundation\App; use PDO; <-- THIS

Thank you for your patience and professional support but out any error message I was becoming crazy.

I tried to use the same instance of PDO and I use

$db = \Delight\Db\PdoDatabase::fromPdo($pdo);

but receive and error

Whoops\Exception\ErrorException: Undefined variable: pdo

By they way I checked the documentation for db but I didn't find any example with Execute a prepared statement with named placeholders and NOT with with question mark placeholders. But I make other test and if I open a question in db repository.

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

Thanks.

[ocram]

Class 'App\Controllers\Backend\Core\PDO' not found

Glad you found it. See, that’s why I suggested really checking the output and especially looking for any warnings or errors. That one here is because the file is in a namespace, so if a class has no namespace specified and is not imported explicitly, like PDO here, it looks for that class in the current namespace, where it might not exist.

use PDO;

Alternatively, you could just change PDO to \PDO so that it is known it is from the root/global namespace.

Undefined variable: pdo

That error message seems pretty clear: The variable does not exist at that point.

By they way I checked the documentation for db but I didn't find any example with Execute a prepared statement with named placeholders and NOT with with question mark placeholders.

Good point, the documentation mostly uses positional placeholders (question marks) instead of named placeholders. But you can use named placeholders in the same way.

[daebak74]

Thanks, $pdo variable doesn't exist there this is sure :) $pdo is the current instance correct ? I can get the current instance in function of my class without create a new PDO.