Feed a select field with data from multiple mysql tables

Feed a select field with data from multiple mysql tables

carrarachristophecarrarachristophe Posts: 39Questions: 10Answers: 0

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?

Answers

  • allanallan Posts: 57,236Questions: 1Answers: 9,113 Site admin

    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

  • carrarachristophecarrarachristophe Posts: 39Questions: 10Answers: 0

    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?

  • allanallan Posts: 57,236Questions: 1Answers: 9,113 Site admin

    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

  • carrarachristophecarrarachristophe Posts: 39Questions: 10Answers: 0

    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

  • colincolin Posts: 14,526Questions: 1Answers: 2,482

    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

  • carrarachristophecarrarachristophe Posts: 39Questions: 10Answers: 0

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

  • allanallan Posts: 57,236Questions: 1Answers: 9,113 Site admin
    edited September 12

    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

  • carrarachristophecarrarachristophe Posts: 39Questions: 10Answers: 0

    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;
                    } )
    
  • tangerinetangerine Posts: 3,244Questions: 30Answers: 385

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

  • allanallan Posts: 57,236Questions: 1Answers: 9,113 Site admin

    Try:

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

    Allan

Sign In or Register to comment.