Feed a select field with data from multiple mysql tables

carrarachristophe

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

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

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

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

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

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

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

allan

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

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

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

allan

Try:

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

Allan