php - How can I get list_fields of a table alias using CodeIgniter?

806

I have this model:

public function listar_jogos_fase($id_fase) {
    $this->db->select('fas.nome as `fase`, jogos.numero as `jogo`, jog1.nome as `jogador1`, jog2.nome as `jogador2`, jogos.data as `data`, jogos.hora as `hora`, jogos.local as `local`', FALSE);
    $this->db->from('jogos');   
    $this->db->join('jogadores as jog1', 'ON jog1.id = jogos.jogador1');
    $this->db->join('jogadores as jog2', 'ON jog2.id = jogos.jogador2');
    $this->db->join('fases as fas', 'ON fas.id = jogos.fase');
    $this->db->where('fase',$id_fase);
    $dados['dados'] = $this->db->get()->result();
    $dados['colunas'] = $this->db->list_fields('jogos');
    return $dados;
}

So I'd like to receive this result, with only some selected fields of my alias table:

fase      jogo   jogador1        jogador2               data        hora            local
1° Fase    1     Andy Murray     Juan Martin del Potro  2017-01-19  16:30:00 Roland Garros, Paris - Quadra 1
1° Fase    2     Novak Djokovic  Roger Federer          2017-01-19  16:30:00 Roland Garros, Paris - Quadra 2

But I'm receiving this other, with all the fields of my table:

id      fase    numero          jogador1              set1_jg1   set2_jg1  set3_jg1                       set4_jg1 set5_jg1 jogador2 set1_jg2 set2_jg2 set3_jg2 set4_jg2 set5_jg2 data hora local
1° Fase  1      Andy Murray     Juan Martin del Potro 2017-01-19 16:30:00  Roland Garros, Paris - Quadra 1
1° Fase  2      Novak Djokovic  Roger Federer         2017-01-19 16:30:00  Roland Garros, Paris - Quadra 2

Could someone help me please?! Thanks in advanced!

Ok, @Bakti Wijaya... if I use that:

public function listar_jogos_fase($id_fase) {
    $dados['dados'] = $this->db->query(
        "SELECT       
            d.nome fase,
            a.numero jogo,
            b.nome jogador1,
            c.nome jogador2,
            a.data data,
            a.hora hora,
            a.local local
        FROM jogos a
            JOIN jogadores b ON a.id = b.id
            JOIN jogadores c ON a.id = c.id
            JOIN fases d ON a.fase = d.id
        where fase=$id_fase"
    )->result_array();

    $dados['colunas'] = $this->db->list_fields('jogos');

    return $dados;
}

I'm gonna receive this:

Array
(
    [dados] => Array
        (
            [0] => Array
                (
                    [fase] => 1° Fase
                    [jogo] => 1
                    [jogador1] => Andy Murray
                    [jogador2] => Andy Murray
                    [data] => 2017-01-19
                    [hora] => 16:30:00
                    [local] => Roland Garros, Paris - Quadra 1
                )

            [1] => Array
                (
                    [fase] => 1° Fase
                    [jogo] => 2
                    [jogador1] => Novak Djokovic
                    [jogador2] => Novak Djokovic
                    [data] => 2017-01-19
                    [hora] => 16:30:00
                    [local] => Roland Garros, Paris - Quadra 2
                )

        )

    [colunas] => Array
        (
            [0] => id
            [1] => fase
            [2] => numero
            [3] => jogador1
            [4] => set1_jg1
            [5] => set2_jg1
            [6] => set3_jg1
            [7] => set4_jg1
            [8] => set5_jg1
            [9] => jogador2
            [10] => set1_jg2
            [11] => set2_jg2
            [12] => set3_jg2
            [13] => set4_jg2
            [14] => set5_jg2
            [15] => data
            [16] => hora
            [17] => local
        )

)

So, I know how to work with this results...
But i want to use, if possible, the $this->db->list_fields() of CodeIgniter to get the name of my alias table fields (alias, not the original)...
So if I use $this->db->list_fields('jogos'), I will receive all the fields of the original table 'jogos', as you can see in the array [columns]...
But I do not want all fields, I want only the 7 fields that were returned in [data] array that are: [fase],[jogo],[jogador1],[jogador2],[data],[hora],[local]...
So I'd like to use something like $this->db->list_fields('a'), where 'a' is an alias of my original table 'jogos'...
But If I use that $this->db->list_fields('a'), i get this error:

    Error Number: 42S02/1146

    Table 'tennis.a' doesn't exist

    SHOW COLUMNS FROM `a`

    Filename: C:/wamp64/www/tennis/system/database/DB_driver.php

    Line Number: 691

"This is not possible!" Is also an acceptable answer.
Thanks for you pay attention!

603

Answer

Solution:

Try this :

   public function listar_jogos_fase($id_fase) 
{
      $this->db->query("SELECT 
      fas.nome fase,
      a.numero jogo,
      b.nome jogador1,
      c.nome jogador2,
      a.data data,
      a.hora hora,
      a.local local
      FROM jogos a
      JOIN jogadores b ON a.id = b.id
      JOIN jogadores c ON a.id = c.id
      JOIN fases d ON a.fases = d.id
      where fase=$id_fase")->result_array();
}

People are also looking for solutions to the problem: php - CakePHP unable to delete associations by cascade or beforeDelete()

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.