Problem with the where condition which is not taken into account

Problem with the where condition which is not taken into account

yurispyyurispy Posts: 29Questions: 6Answers: 0

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();

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • yurispyyurispy Posts: 29Questions: 6Answers: 0
    edited January 2022

    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
            }
        ]
    }
    
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • yurispyyurispy Posts: 29Questions: 6Answers: 0

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • yurispyyurispy Posts: 29Questions: 6Answers: 0

    Sorry, I forgot an argument. It's functional

  • yurispyyurispy Posts: 29Questions: 6Answers: 0

    Hello,

    Have you seen anything?
    Thanks

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • yurispyyurispy Posts: 29Questions: 6Answers: 0

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • yurispyyurispy Posts: 29Questions: 6Answers: 0

    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...

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • yurispyyurispy Posts: 29Questions: 6Answers: 0

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
        ->where('produit.supp', '0' )
        ->where('produit.archive', '0' )
    

    Will produce that kind of query.

    Allan

Sign In or Register to comment.