Complex Where not working (PHP)

danilorago

My Where clause depending on one information that I get from my Session on PHP, but it's not working.
Somebody faced the same issue? Anyone know any tip?

<?php
// >>> SESSION (PROJECT ID)
session_start();
$Int__ID__Project = $_SESSION["ID__Project"];
// <<< SESSION (PROJECT ID)

include("DataTables/php/lib/DataTables.php");
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst($db, 'REL__Building_Block__Category_X_Business__Process', 'ID__REL__Building_Block__Category_X_Business__Process')
    ->fields(
        Field::inst('REL__Building_Block__Category_X_Business__Process.ID__REL__Building_Block__Category_X_Business__Process'),
        Field::inst('Project.Project__Name')
            ->set(false),
        Field::inst('Business__Area.Business__Area')
            ->set(false),
        Field::inst('REL__Building_Block__Category_X_Business__Process.ID__Business__Process')
            ->options(
                Options::inst()
                    ->table('Business__Process')
                    ->value('ID__Business__Process')
                    ->label('Business__Process__Name')
            )
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message('Error: Fill the Process field!')
            ))
        ,
        Field::inst('Business__Process.Business__Process__Name'),
        Field::inst('REL__Building_Block__Category_X_Business__Process.ID__Building_Block__Category')
            ->options(
                Options::inst()
                    ->table('Building_Block__Category')
                    ->value('ID__Building_Block__Category')
                    ->label('Building_Block__Category__Name')
            )
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message('Error: Fill the End to End Process field!')
            ))
        ,
        Field::inst('Building_Block__Category.Building_Block__Category__Name'),
        Field::inst('REL__Building_Block__Category_X_Business__Process.REL__Building_Block__Category_X_Business__Process__Date')->setValue(date("Y-m-d")),
        Field::inst('REL__Building_Block__Category_X_Business__Process.ID__Login')->setValue($_SESSION['ID__Login'])
            ->options(
                Options::inst()
                    ->table('Login')
                    ->value('ID__Login')
                    ->label('Login__Name')
            )
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message('Error: Fill the Login field!')
            ))
        ,
        Field::inst('Login.Login__Name')
    )
    ->leftJoin('Building_Block__Category', 'Building_Block__Category.ID__Building_Block__Category', '=', 'REL__Building_Block__Category_X_Business__Process.ID__Building_Block__Category')
    ->leftJoin('Business__Process', 'Business__Process.ID__Business__Process', '=', 'REL__Building_Block__Category_X_Business__Process.ID__Business__Process')
    ->leftJoin('Business__Area', 'Business__Area.ID__Business__Area', '=', 'Business__Process.ID__Business__Area')
    ->leftJoin('Project', 'Project.ID__Project', '=', 'Business__Area.ID__Project')
    ->leftJoin('Login', 'Login.ID__Login', '=', 'REL__Building_Block__Category_X_Business__Process.ID__Login')
    ->where(function($q) use ($Int__ID__Project){
        if($Int__ID__Project<>0){
            $q->where('Business__Area.ID__Project', $Int__ID__Project)
        }else{
            $q->where('Business__Area.ID__Project', $Int__ID__Project, '>')
        }
    });
    ->process($_POST)
    ->json();

<?php
>
?>

kthorngren

I believe if you add ->debug(true) before process() you can see the generated SQL statement by using the browser's network inspector to view the JSON response. For example;

    ->debug(true)
    ->process($_POST)

Does that help yup debug the where clause?

When you say it's not working what exactly happens?

Kevin

danilorago

I put, but withou any message on inspector, dont run, bring the message:
"DataTables warning: table id=DT__List - Invalid JSON response. For more information about this error, please see https://datatables.net/tn/1"

If I use only simple works, as: ->where('Business__Area.ID__Project', $Int__ID__Project)

kthorngren

Are you saying adding the debug statement causes the Invalid JSON response or does the complex where clause cause the error.

Did you follow the troubleshooting steps at the link in the error?
https://datatables.net/tn/1

Let us know what you find.

Kevin

rf1234

Just dig deeper into your server response in the "Network" tab of your browser. Just had this today. Same message from Data Tables "invalid JSON response". Then I found a PHP error message in the respective "Fetch/XHR" task. You'll figure it out. In my case it was a simple syntax error that aborted the PHP code execution and led to the "invalid JSON response" due to no PHP response.

bur

This may be a copy-paste mistake in composing the question, but it seems like you have a stray ; right before ->process($_POST).

danilorago

Hello @bur with both situations, with or without give me error...
Hello @kthorngren and @rf1234, I did: give me the error:

<br />
<b>Parse error</b>:  syntax error, unexpected '}' in <b>/home/itxm/public_html/datatables--building-block-vs-process.php</b> on line <b>71</b><br />

Line 68: ->where(function($q) use ($Int__ID__Project){
Line 69:        if($Int__ID__Project<>0){
Line 70:            $q->where('Business__Area.ID__Project', $Int__ID__Project)
Line 71:        }else{
Line 72:            $q->where('Business__Area.ID__Project', $Int__ID__Project, '>')
Line 73:        }
Line 74:    })

rf1234

Sorry, @danilorago
What IDE are you using? I copied your code into Netbeans and it immediately highlighted your syntax errors to me.

Just add ";" at the end of your statements ... see screenshot below

danilorago

What's IDE?
Thanks a lot, worked now @rf1234

kthorngren

Another option is to use a PHP code checker if you don't have an IDE (Integrated Development Environment). Thanks to @rf1234 for finding the errors. I'm not familiar with PHP so didn't figure out the missing ;. I guess PHP is more particular about the ; than Javascript

Kevin

danilorago

Thanks for the tip, I will start to use!

rf1234

I guess PHP is more particular about the ; than Javascript

Oh yes, Kevin. PHP wants those dearly.

Thanks for the tip, I will start to use!

I'd be completely lost without a good IDE (or a code checker as Kevin said). It is extremely hard to find those typical typos without it. I only saw the missing ";" when I copied your code into NetBeans.