Problem with the where condition which is not taken into account

yurispy

Hello,

I have a problem with this code which runs fine but the conditions where not taken into account.
I tried multiple solutions. Always the same result.

product.supp is INT
product.archive is INT
I want to exclude all rows that are 1 for either

<?php
// DataTables PHP library
include( "../Editor-PHP-1.9.1/lib/DataTables.php" );

// Alias Editor classes so they are easy to use
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, 'produit_prix' )
    ->field(
        Field::inst( 'produit_prix.produit_id' )
            ->options( Options::inst()
                ->table( 'produit' )
                ->value( 'id' )
                ->label( 'produit' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'produit_prix.prix' ),
        Field::inst( 'produit_prix.saison' ),
        Field::inst( 'produit_prix.last_update')
          ->setValue( date('c') )
          ->getFormatter( 'Format::date_sql_to_format', 'jS F Y' ),
        Field::inst( 'produit.produit' ),
        Field::inst( 'produit.supp' ),
        Field::inst( 'produit.archive' )
    )
    ->leftJoin('produit', 'produit.id', '=', 'produit_prix.produit_id')
    ->where('produit.supp', '1', '!=' )
    ->where('produit.archive', '1', '!=' )
    ->process($_POST)
    ->json();

allan

That looks like it should be okay. Could you add ->debug(true) immediately before the ->process($_POST) call, and then use the debugger to upload a trace for me please? When you update the trace it will give you a unique code - if you could send me that please.

Thanks,
Allan

allan

p.s. If you are using Editor 1.9.1 PHP files, as the path name suggests, update to 1.9.6 (the latest in the 1.x series) or 2.0.6 (the current release), just in case you are hitting an old bug somewhere (although none spring to mind that would effect that).

Allan

yurispy

I updated to version 1.9.6, same problem.
Here is the debug

{
    "query": "SELECT  `produit_prix`.`id` as 'produit_prix.id', `produit_prix`.`produit_id` as 'produit_prix.produit_id', `produit_prix`.`prix` as 'produit_prix.prix', `produit_prix`.`saison` as 'produit_prix.saison', `produit_prix`.`last_update` as 'produit_prix.last_update', `produit`.`produit` as 'produit.produit', `produit`.`supp` as 'produit.supp', `produit`.`archive` as 'produit.archive' FROM  `produit_prix` LEFT JOIN `produit` ON `produit`.`id` = `produit_prix`.`produit_id` WHERE `produit`.`supp` != :where_0 AND  `produit`.`archive` != :where_1 ",
    "bindings": [
        {
            "name": ":where_0",
            "value": "1",
            "type": null
        },
        {
            "name": ":where_1",
            "value": "1",
            "type": null
        }
    ]
}

allan

Many thanks. That shows the WHERE condition being applied to the SQL that is being executed.

Can you upload a debug trace or give me a link to your page so I can check it out please?

Allan

yurispy

upload a debug trace??? I don't know how to do it, sorry.
Here is the link to test the page.
http://www.agrocropsmanager.fr/acm/gest_prix_produit.php

Thk Allan

allan

Thanks for the link - that really helps!

I'm getting an error when I load the page about DataTables not being able to load valid JSON.

The response from the server contains:

Notice: Undefined index: db_database in /volume1/web/acm/Editor-PHP-1.9.6/lib/config.php on line 17
{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected",

Allan

yurispy

Sorry, I forgot an argument. It's functional

yurispy

Hello,

Have you seen anything?
Thanks

allan

Sorry - I missed your previous reply. I'm now getting the following response from the server:

Warning: include(../Editor-PHP-1.9.6-1.9.6/lib/DataTables.php): failed to open stream: No such file or directory in /volume1/web/acm/scripts/php_gestion_prix_produit.php on line 3 Warning: include(): Failed opening '../Editor-PHP-1.9.6-1.9.6/lib/DataTables.php' for inclusion (include_path='.') in /volume1/web/acm/scripts/php_gestion_prix_produit.php on line 3 Fatal error: Class 'DataTables\Editor' not found in /volume1/web/acm/scripts/php_gestion_prix_produit.php on line 18 

Allan

yurispy

Sorry, I made the update and I forgot to change the link in the script

colin

Is this working now? I just tried (created a record for season 1970) and it appears to be behaving. If not, please let us know,

Colin

yurispy

No, it doesn't work. When I go back to the select menu there are entries that should not be there because I have excluded in the where the archive at 1 and supp at 1 but it does not take into account.
Example: the product Bolero if finds 2 while only one should appear. And ALF, OPAC etc...

allan

Many thanks for updating the site.

This is the condition on the query that is being run:

WHERE produit.supp != :where_0 AND produit.archive != :where_1 ",

Where the bindings are both 1. That seems to correspond with the data that is being returned, where supp and archive are both 0 for at least the first few records:

Are there any records being loaded in where that condition is not being applied? Perhaps the issue is that the condition is not correct for the data you want to load?

Allan

yurispy

Hello

I want
SELECT * from produit where archive = 0 AND supp = 0

Example SQL table
ID;product;cat;archive;supp
9407;ALF;Fertilizer;0;1
9417;ALF;Fertilizer;1;1
9317;ALF;Fertilizer;1;0
9413;ALF;oligos;0;0

Result
ID;product;cat;archive;supp
9413;ALF;oligos;0;0

allan

    ->where('produit.supp', '0' )
    ->where('produit.archive', '0' )

Will produce that kind of query.

Allan