Feed a select field with data from multiple mysql tables

carrarachristophe · September 2022

Hello,

I am using the following:

        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( Options::inst()
                ->table( 'bibliotheque_oeuvres' )
                ->value( 'oeuvre_id' )
                ->label( array('oeuvre_id', 'titre',  'tome',  'auteur') )
                ->render( function ( $row ) {
                    if ( $row['tome'] & $row['auteur']) {
                    return $row['oeuvre_id'].' - '.$row['titre'].' ('.$row['tome'].') - ' .$row['auteur'] ;
                    }
                    if ( $row['tome']) {
                    return $row['oeuvre_id'].' - '.$row['titre'].' ('.$row['tome'].')' ;
                    }
                    return $row['oeuvre_id'].' - '.$row['titre'].' - ' .$row['auteur'].'' ;
                 }))
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' ))),

To feed a select2 field:

            {
                label: "Oeuvre:",
                name: "bibliotheque_bibliotheques.oeuvre",
                type: "select2"
            },

The problem is that:
* if oeuvre_id, titre and tome are all part of the mysql table bibliotheque_oeuvres
* I would rather like to use the field bibliotheque_auteurs.auteur (the name) instead of auteur (the id, foreign key)

Anyone would know how I can trick the above code to achieve that?

allan · September 2022

One option is to use the the Options class with a function that will get the data from the database using a join.

Another option is to use a VIEW in the database which will do the join and then you just read from the VIEW (rather than using the table name, use the view name in the options class).

Allan

carrarachristophe · September 2022

Hi Allan,
Are you suggesting to replace that part

->options( Options::inst()

by

->options( function () {

correct?

But how can I replace the value and label (closed list in the example) by some fields from various tables?

allan · September 2022

Correct. Something like:

function () use ($db) {
  $res = $db->query( 'select' )
    ->table( 'bibliotheque_oeuvres' )
    ->get( 'oeuvre_id', 'titre', 'auteur', ... )
    ->join( 'table2', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur' )
    ->order( ... )
    ->exec();

  $out = [];
  $rows = $res->fetchAll();

  for ($i=0 ; $i<count($rows) ; $i++) {
    $out[] = [
      'value' => $row['oeuvre_id'],
      'label' => ...
     ];
  }

  return $out;
}

A few gaps to be filled in, but hopefully that will help!

Allan

carrarachristophe · September 2022

Hi Allan,
Thank you for the tip.
Unfortunatly, I cannoy get it work, even when I simplify it at its maximum:

        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( function () use ($db) {
                $res = $db->query( 'select' )
                    ->table( 'bibliotheque_oeuvres' )
                    ->get( 'oeuvre_id', 'titre')
                    ->exec();

                $out = [];
                $rows = $res->fetchAll();

                for ($i=0 ; $i<count($rows) ; $i++) {
                    $out[] = [
                        **'value' => $row['oeuvre_id'],**
                        'label' => $row['titre']
                    ];
                }

                return $out;
                } )

I am getting the following message, related to the above line 13:

Notice: Undefined variable: row in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 39

Notice: Trying to access array offset on value of type null in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 39

colin · September 2022

My PHP isn't best, but shouldn't lines 13 and 14 be referencing $rows[i], as the error says row is undefined. Also, you're in a loop - the value of $out is being reset on each iteration,

Colin

carrarachristophe · September 2022

Thanks Colin.
I think this is out of my abilities.
There is no way to do that in the js side instead?

allan · September 2022

No - you need to get the information from the database, so you must do this server-side.

It is exactly as Colin says, you need to use:

$rows[$i]['oeuvre_id']

Or you could have $row = $rows[$i] at the top of the loop.

Allan

carrarachristophe · October 2022

Dear both,
Thank. But unfortunately I could not get it work.

I am getting the following message:
Notice: Undefined index: bibliotheque_oeuvres.oeuvre_id in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 39
Notice: Undefined index: titre in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 40

Please note that I tried with and without the table name.

        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( function () use ($db) {
                $res = $db->query( 'select' )
                    ->table( 'bibliotheque_oeuvres' )
                    ->get( 'oeuvre_id', 'titre')
                    ->exec();

                $out = [];
                $rows = $res->fetchAll();

                for ($i=0 ; $i<count($rows) ; $i++) {
                    $out[] = [
                        'value' => $rows['bibliotheque_oeuvres.oeuvre_id'],
                        'label' => $rows['titre']
                    ];
                }

                return $out;
                } )

tangerine · October 2022

You seem to have ignored the advice you were given by Colin and Allan.

allan · October 2022

Try:

$rows['bibliotheque_oeuvres']['oeuvre_id']

Allan

carrarachristophe · March 2023

Thank you Allan.
It works with the following code:

        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( function () use ($db) {
                $res = $db->query( 'select' )
                    ->table( 'bibliotheque_oeuvres' )
                    ->get( 'oeuvre_id', 'titre', 'auteur', 'tome')
                    ->exec();

            $out = [];
            $rows = $res->fetchAll();

            for ($i=0 ; $i<count($rows) ; $i++) {
                $out[] = [
                    'value' => $rows[$i]['oeuvre_id'],
                    'label' => ( array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['auteur'] ) )
                ];
            }

            return $out;
        } )

But in addition to be missing the if conditions, on which I will work later, I am back to my initial problem, with bibliotheque_oeuvres.auteur quoted instead of bibliotheque_auteurs.auteur.
How can I had a leftjoin to the code? I tried the following:

        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( function () use ($db) {
                $res = $db->query( 'select' )
                    ->table( 'bibliotheque_oeuvres', 'bibliotheque_auteurs' )
                    ->get( 'oeuvre_id', 'titre', 'auteur', 'bibliotheque_auteurs.auteur', 'tome')
                    ->exec();

            $out = [];
            $rows = $res->fetchAll();

            for ($i=0 ; $i<count($rows) ; $i++) {
                $out[] = [
                    'value' => $rows[$i]['oeuvre_id'],
                    'label' => ( array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) )
                ];
            }

            return $out;
        } )

But am getting the following message:
DataTables warning: table id=bibliotheque_bibliotheques - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bibliotheque_auteurs.auteur' in 'field list'

allan · March 2023

How can I had a leftjoin to the code?

Use the join method or the newer left_join (added because it is so common).

        $res = $db->query( 'select' )
            ->table( 'bibliotheque_oeuvres' )
            ->get( 'oeuvre_id', 'titre', 'auteur', 'bibliotheque_auteurs.auteur', 'tome')
            ->join( 'bibliotheque_auteurs', 'condition', 'LEFT' )
            ->exec();

Where condition is replaced with whatever your join condition is. Probably something like bibliotheque_oeuvres.auteur = bibliotheque_auteurs.id.

Allan

carrarachristophe · April 2023

Thank you Allan,
Here is the code that works for me:

        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( function () use ($db) {
                $res = $db->query( 'select' )
                    ->table( 'bibliotheque_oeuvres' )
                    ->get( 'oeuvre_id', 'titre', 'bibliotheque_auteurs.auteur', 'tome')
                    ->join( 'bibliotheque_auteurs', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur_id', 'LEFT' )
                    ->exec();
         
                    $out = [];
                    $rows = $res->fetchAll();
                 
                    for ($i=0 ; $i<count($rows) ; $i++) {
                        $out[] = [
                            'value' => $rows[$i]['oeuvre_id'],
                            'label' => ( array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) )
                        ];
                    }
                 
                    return $out;
            } )

What I am just missing now is to include in label something close to what I used to have in render, for example only display $rows[$i]['tome'] in the array only if this field exists.
Should I use something like if (in_array(?

carrarachristophe · April 2023

Dear all,
I tried the below without success:

Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
    ->options( function () use ($db) {
        $res = $db->query( 'select' )
            ->table( 'bibliotheque_oeuvres' )
            ->get( 'oeuvre_id', 'titre', 'bibliotheque_auteurs.auteur', 'tome')
            ->join( 'bibliotheque_auteurs', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur_id', 'LEFT' )
            ->exec();
 
            $out = [];
            $rows = $res->fetchAll();
         
            for ($i=0 ; $i<count($rows) ; $i++) {
                $out[] = [
                    'value' => $rows[$i]['oeuvre_id'],
//                          'label' => array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ),
                    'render' => function ( $rows ) {
                        if ( $rows[$i]['tome'] & $rows[$i]['auteur']) {
                            array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) ;
                        }
                        if ( $rows[$i]['tome']) {
                            $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].')' ;
                        }
                            $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' - ' .$rows[$i]['bibliotheque_auteurs.auteur'].'' ;
                     }
                ];
            }
         
            return $out;
    } )
    ->validator( Validate::notEmpty( ValidateOptions::inst()
        ->message( 'Requis' ))),

when I keep the label part (line 15), the render part is not taken into account (label is displayed)
when I remove the label part (line 15), the render part gives a blank info in the select field
I tried to return using array (line 18) or not (line 21), no impact
I initially used 'render' => function ( $rows[$i] ) { in line 16 but ir returns a http://datatables.net/tn/7 error message

Can anyone help me include some if conditions?

allan · May 2023

You don't have a return statement in your rendering function. So it is effectively a no-op. You probably want returns on line 21 and 23. It should return the string to display, not an array.

Allan

carrarachristophe · August 2023

Hi Allan,

Adding return on line 21 and 23 does not solve the issue. The select field is still blank.

allan · August 2023

Can you show me your latest code please?

Also if you could enable debug mode (add ->debug(true) just before the ->process(...) call, and then send me the JSON response from the server from the Ajax request for the table load, that would be useful.

Thanks,
Allan

carrarachristophe · August 2023

Hi Allan,
Here is my latest code:

Editor::inst( $db, 'bibliotheque_bibliotheques', 'bibliotheque_id' )
    ->fields(
        Field::inst( 'bibliotheque_bibliotheques.utilisateur' )
            ->options( Options::inst()
                ->table( 'utilisateurs' )
                ->value( 'utilisateur_id' )
                ->label( 'utilisateur' ))
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' ))),
        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( function () use ($db) {
                $res = $db->query( 'select' )
                    ->table( 'bibliotheque_oeuvres' )
                    ->get( 'oeuvre_id', 'titre', 'bibliotheque_auteurs.auteur', 'tome')
                    ->join( 'bibliotheque_auteurs', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur_id', 'LEFT' )
                    ->exec();
         
            $out = [];
            $rows = $res->fetchAll();
         
            for ($i=0 ; $i<count($rows) ; $i++) {
                $out[] = [
                    'value' => $rows[$i]['oeuvre_id'],
/*                  'label' => array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ),*/
                    'render' => function ( $rows ) {
                        if ( $rows[$i]['tome'] & $rows[$i]['auteur']) {
                            array ( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) ;
                        }
                        if ( $rows[$i]['tome']) {
                            $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].')' ;
                        }
                            $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' - ' .$rows[$i]['bibliotheque_auteurs.auteur'].'' ;
                    }
                ];
            }
            return $out;
    } )
    ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' ))),
        Field::inst( 'bibliotheque_oeuvres.type' )
            ->options( Options::inst()
                ->table( 'bibliotheque_types' )
                ->value( 'type_id' )
                ->label( 'type' )),
        Field::inst( 'bibliotheque_oeuvres.serie' )
            ->options( Options::inst()
                ->table( 'bibliotheque_series' )
                ->value( 'serie_id' )
                ->label( 'serie' )),
        Field::inst( 'bibliotheque_oeuvres.auteur' )
            ->options( Options::inst()
                ->table( 'bibliotheque_auteurs' )
                ->value( 'auteur_id' )
                ->label( 'auteur' ))
            ->validator( Validate::dbValues() ),
        Field::inst( 'bibliotheque_bibliotheques.possession' ),
        Field::inst( 'bibliotheque_bibliotheques.date_achat' )
            ->validator( Validate::dateFormat( 'd/m/Y' ) )
            ->getFormatter( Format::datetime( 'Y-m-d', 'd/m/Y' ) )
            ->setFormatter( Format::datetime( 'd/m/Y', 'Y-m-d' ) ),
        Field::inst( 'bibliotheque_bibliotheques.prix_achat' )
            ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst( 'bibliotheque_bibliotheques.neuf' ),
        Field::inst( 'bibliotheque_bibliotheques.voeux' ),
        Field::inst( 'bibliotheque_oeuvres.oeuvre_id' ),
        Field::inst( 'bibliotheque_oeuvres.titre' ),
        Field::inst( 'bibliotheque_oeuvres.cycle' ),
        Field::inst( 'bibliotheque_oeuvres.tome' ),
        Field::inst( 'utilisateurs.utilisateur' ),
        Field::inst( 'bibliotheque_oeuvres.pages' ),
        Field::inst( 'bibliotheque_oeuvres.format' ),
        Field::inst( 'bibliotheque_oeuvres.langue' ),
        Field::inst( 'bibliotheque_oeuvres.parution' )
            ->validator( Validate::dateFormat( 'd/m/Y' ) )
            ->getFormatter( Format::datetime( 'Y-m-d', 'd/m/Y' ) )
            ->setFormatter( Format::datetime( 'd/m/Y', 'Y-m-d' ) ),
        Field::inst( 'bibliotheque_oeuvres.prix' ),
        Field::inst( 'bibliotheque_oeuvres.resume' ),
        Field::inst( 'bibliotheque_editeurs.editeur' ),
        Field::inst( 'bibliotheque_collections.collection' ),
        Field::inst( 'bibliotheque_types.type' ),
        Field::inst( 'bibliotheque_genres.genre' ),
        Field::inst( 'langues.langue' ),
        Field::inst( 'bibliotheque_series.serie' ),
        Field::inst( 'bibliotheque_auteurs.auteur' ),
        Field::inst( 'bibliotheque_coloristes.coloriste' ),
        Field::inst( 'bibliotheque_dessinateurs.dessinateur' ),
        Field::inst( 'bibliotheque_scenaristes.scenariste' )
    )
    ->leftJoin( 'utilisateurs', 'utilisateurs.utilisateur_id', '=', 'bibliotheque_bibliotheques.utilisateur' )
    ->leftJoin( 'bibliotheque_oeuvres', 'bibliotheque_oeuvres.oeuvre_id', '=', 'bibliotheque_bibliotheques.oeuvre' )
    ->leftJoin( 'bibliotheque_auteurs', 'bibliotheque_auteurs.auteur_id', '=', 'bibliotheque_oeuvres.auteur' )
    ->leftJoin( 'bibliotheque_coloristes', 'bibliotheque_coloristes.coloriste_id', '=', 'bibliotheque_oeuvres.coloriste' )
    ->leftJoin( 'bibliotheque_dessinateurs', 'bibliotheque_dessinateurs.dessinateur_id', '=', 'bibliotheque_oeuvres.dessinateur' )
    ->leftJoin( 'bibliotheque_scenaristes', 'bibliotheque_scenaristes.scenariste_id', '=', 'bibliotheque_oeuvres.scenariste' )
    ->leftJoin( 'bibliotheque_series', 'bibliotheque_series.serie_id', '=', 'bibliotheque_oeuvres.serie' )
    ->leftJoin( 'bibliotheque_types', 'bibliotheque_types.type_id', '=', 'bibliotheque_oeuvres.type' )
    ->leftJoin( 'bibliotheque_genres', 'bibliotheque_genres.genre_id', '=', 'bibliotheque_oeuvres.genre' )
    ->leftJoin( 'bibliotheque_editeurs', 'bibliotheque_editeurs.editeur_id', '=', 'bibliotheque_oeuvres.editeur' )
    ->leftJoin( 'bibliotheque_collections', 'bibliotheque_collections.collection_id', '=', 'bibliotheque_oeuvres.collection' )
    ->leftJoin( 'langues', 'langues.langue_id', '=', 'bibliotheque_oeuvres.langue' )
    ->where( 'bibliotheque_bibliotheques.possession', 1 )
    ->process( $_POST )
    ->json();

tangerine · August 2023

Allan needs the debug info, which will be at the tail-end of your JSON response.

carrarachristophe · August 2023

My apologies:

And an excerpt of the json response:

"debug":["Editor PHP libraries - version 2.2.2",{"query":"SELECT  `bibliotheque_bibliotheques`.`bibliotheque_id` as 'bibliotheque_bibliotheques.bibliotheque_id', `bibliotheque_bibliotheques`.`utilisateur` as 'bibliotheque_bibliotheques.utilisateur', `bibliotheque_bibliotheques`.`oeuvre` as 'bibliotheque_bibliotheques.oeuvre', `bibliotheque_oeuvres`.`type` as 'bibliotheque_oeuvres.type', `bibliotheque_oeuvres`.`serie` as 'bibliotheque_oeuvres.serie', `bibliotheque_oeuvres`.`auteur` as 'bibliotheque_oeuvres.auteur', `bibliotheque_bibliotheques`.`possession` as 'bibliotheque_bibliotheques.possession', `bibliotheque_bibliotheques`.`date_achat` as 'bibliotheque_bibliotheques.date_achat', `bibliotheque_bibliotheques`.`prix_achat` as 'bibliotheque_bibliotheques.prix_achat', `bibliotheque_bibliotheques`.`neuf` as 'bibliotheque_bibliotheques.neuf', `bibliotheque_bibliotheques`.`voeux` as 'bibliotheque_bibliotheques.voeux', `bibliotheque_oeuvres`.`oeuvre_id` as 'bibliotheque_oeuvres.oeuvre_id', `bibliotheque_oeuvres`.`titre` as 'bibliotheque_oeuvres.titre', `bibliotheque_oeuvres`.`cycle` as 'bibliotheque_oeuvres.cycle', `bibliotheque_oeuvres`.`tome` as 'bibliotheque_oeuvres.tome', `utilisateurs`.`utilisateur` as 'utilisateurs.utilisateur', `bibliotheque_oeuvres`.`pages` as 'bibliotheque_oeuvres.pages', `bibliotheque_oeuvres`.`format` as 'bibliotheque_oeuvres.format', `bibliotheque_oeuvres`.`langue` as 'bibliotheque_oeuvres.langue', `bibliotheque_oeuvres`.`parution` as 'bibliotheque_oeuvres.parution', `bibliotheque_oeuvres`.`prix` as 'bibliotheque_oeuvres.prix', `bibliotheque_oeuvres`.`resume` as 'bibliotheque_oeuvres.resume', `bibliotheque_editeurs`.`editeur` as 'bibliotheque_editeurs.editeur', `bibliotheque_collections`.`collection` as 'bibliotheque_collections.collection', `bibliotheque_types`.`type` as 'bibliotheque_types.type', `bibliotheque_genres`.`genre` as 'bibliotheque_genres.genre', `langues`.`langue` as 'langues.langue', `bibliotheque_series`.`serie` as 'bibliotheque_series.serie', `bibliotheque_auteurs`.`auteur` as 'bibliotheque_auteurs.auteur', `bibliotheque_coloristes`.`coloriste` as 'bibliotheque_coloristes.coloriste', `bibliotheque_dessinateurs`.`dessinateur` as 'bibliotheque_dessinateurs.dessinateur', `bibliotheque_scenaristes`.`scenariste` as 'bibliotheque_scenaristes.scenariste' FROM  `bibliotheque_bibliotheques` LEFT JOIN `utilisateurs` ON `utilisateurs`.`utilisateur_id` = `bibliotheque_bibliotheques`.`utilisateur`  LEFT JOIN `bibliotheque_oeuvres` ON `bibliotheque_oeuvres`.`oeuvre_id` = `bibliotheque_bibliotheques`.`oeuvre`  LEFT JOIN `bibliotheque_auteurs` ON `bibliotheque_auteurs`.`auteur_id` = `bibliotheque_oeuvres`.`auteur`  LEFT JOIN `bibliotheque_coloristes` ON `bibliotheque_coloristes`.`coloriste_id` = `bibliotheque_oeuvres`.`coloriste`  LEFT JOIN `bibliotheque_dessinateurs` ON `bibliotheque_dessinateurs`.`dessinateur_id` = `bibliotheque_oeuvres`.`dessinateur`  LEFT JOIN `bibliotheque_scenaristes` ON `bibliotheque_scenaristes`.`scenariste_id` = `bibliotheque_oeuvres`.`scenariste`  LEFT JOIN `bibliotheque_series` ON `bibliotheque_series`.`serie_id` = `bibliotheque_oeuvres`.`serie`  LEFT JOIN `bibliotheque_types` ON `bibliotheque_types`.`type_id` = `bibliotheque_oeuvres`.`type`  LEFT JOIN `bibliotheque_genres` ON `bibliotheque_genres`.`genre_id` = `bibliotheque_oeuvres`.`genre`  LEFT JOIN `bibliotheque_editeurs` ON `bibliotheque_editeurs`.`editeur_id` = `bibliotheque_oeuvres`.`editeur`  LEFT JOIN `bibliotheque_collections` ON `bibliotheque_collections`.`collection_id` = `bibliotheque_oeuvres`.`collection`  LEFT JOIN `langues` ON `langues`.`langue_id` = `bibliotheque_oeuvres`.`langue` WHERE `bibliotheque_bibliotheques`.`possession` = :where_0 ","bindings":[{"name":":where_0","value":1,"type":null}]},{"query":"SELECT DISTINCT  `utilisateur_id` as 'utilisateur_id', `utilisateur` as 'utilisateur' FROM  `utilisateurs` ","bindings":[]},{"query":"SELECT  `oeuvre_id` as 'oeuvre_id', `titre` as 'titre', `bibliotheque_auteurs`.`auteur` as 'bibliotheque_auteurs.auteur', `tome` as 'tome' FROM  `bibliotheque_oeuvres` LEFT JOIN `bibliotheque_auteurs` ON `bibliotheque_oeuvres`.`auteur` = `bibliotheque_auteurs`.`auteur_id` ","bindings":[]},{"query":"SELECT DISTINCT  `type_id` as 'type_id', `type` as 'type' FROM  `bibliotheque_types` ","bindings":[]},{"query":"SELECT DISTINCT  `serie_id` as 'serie_id', `serie` as 'serie' FROM  `bibliotheque_series` ","bindings":[]},{"query":"SELECT DISTINCT  `auteur_id` as 'auteur_id', `auteur` as 'auteur' FROM  `bibliotheque_auteurs` ","bindings":[]}]

allan · August 2023

Thanks. I can see for that debug trace that it is executing your query:

  SELECT
    `oeuvre_id` as 'oeuvre_id',
    `titre` as 'titre',
    `bibliotheque_auteurs`.`auteur` as 'bibliotheque_auteurs.auteur',
    `tome` as 'tome'
  FROM  `bibliotheque_oeuvres`
  LEFT JOIN `bibliotheque_auteurs` ON `bibliotheque_oeuvres`.`auteur` = `bibliotheque_auteurs`.`auteur_id`

Looking at your current code, I can see that you still don't have a return statement in your render function:

                    'render' => function ( $rows ) {
                        if ( $rows[$i]['tome'] & $rows[$i]['auteur']) {
                            array ( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) ;
                        }
                        if ( $rows[$i]['tome']) {
                            $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].')' ;
                        }
                            $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' - ' .$rows[$i]['bibliotheque_auteurs.auteur'].'' ;
                    }

That function just always returns undefined.

However, I don't think that is actually the issue in retrospect. If you use a custom function to create the options for a select input, you are required to return an array with objects that contain label and value properties. You are currently returning value and render. label is commented out for some reason? Also the render function wouldn't be executed.

I would also suggest adding some debug to make sure your SQL query is actually returning any results - i.e. what is the value of $rows?

Allan

carrarachristophe · August 2023

Hi Allan,

I neutralised the label part because when present, this is what is rendered rather than the render part of the code.

I removed the return because I am getting an error message. I think it is not needed anyway as return $out; should be sufficient.
Would you have any working example of any kind of a 'render' => function ( $rows ) {?

Feed a select field with data from multiple mysql tables

Feed a select field with data from multiple mysql tables

Answers

Howdy, Stranger!

Categories

DataTables

Feed a select field with data from multiple mysql tables

Feed a select field with data from multiple mysql tables

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables