Perform a UNION with Codeigniter

Question

Perform a UNION with Codeigniter

Navigation

#1 by (1 votes)

0

Hi, how can I make a UNION in codeigniter, since I want my table to fit between my two tables. Something like that.

[array] => Array
        (
            [0] => stdClass Object
                (
                //TABLA 1
                    [id_tecnico] => 1698
                    [fecha] => 2018-02-12
                    [NroSolicitud] => M49320
                    [horaInicio] => 15:15
                    [horaTermino] => 17:45
                    [Comentario] => se repara bastidor .ok.
                    [total] => 2.5
                )

            [1] => stdClass Object
                (
                 //TABLA 2
                [id_tecnico] => 1698
                [NroSolicitud] => M49317
                [fecha] => 2018-02-12
                [actividad] => Reunion
                [Comentario] => reunion de trabajo
                [TotalHrs] => 0.67
                )
          )

I have the following query in my model, where I make the query to the table Mantenciones , but I also want to make a query to the table Actividades with the same WHERE

- I update the model's query and show the results.

$this->db->select('p.Nombre,s.fecha,s.NroSolicitud,d.horaInicio,d.horaTermino,d.Comentario,d.total,m.maquina');
   $this->db->from('Tecnico_Seguimiento as t');
   $this->db->join('personal as p','p.Codigo = t.id_tecnico');
   $this->db->join('MAN_SeguimientoDetalle as d','d.id_detalle = t.id_detalle');
   $this->db->join('MAN_Seguimiento as s','s.idMan_Tecnico = d.id_man_tecnico');
   $this->db->join('MAN_Solicitud as m','m.NroSolicitud = s.NroSolicitud');

   $this->db->where('t.id_tecnico',$id);
   $this->db->where('s.fecha >=',$minvalue);
   $this->db->where('s.fecha <=',$maxvalue);

   $query1 = $this->db->get_compiled_select();


   $this->db->select('act.NroSolicitud as NroAct, act.fecha as fechaAct,act.actividad,act.Comentario, act.TotalHrs, act.orden, act.horaActividad, act.id_tecnico ');
   $this->db->from('MAN_Actividades as act');
   $this->db->where('act.fecha >=',$minvalue);
   $this->db->where('act.fecha <=',$maxvalue);
   $this->db->where('act.id_tecnico <=',$id);
   $query2 = $this->db->get_compiled_select();
   return $this->db->query($query1." UNION ".$query2)->result();

With this query I get these values

[seguimientos] => Array
    (
        [0] => stdClass Object
            (
                [Nombre] => CABALLERO HUGO
                [fecha] => 2018-02-20
                [NroSolicitud] => M49301
                [horaInicio] => 17:45
                [horaTermino] => 19:30
                [Comentario] => cambio de valvula .ok.
                [total] => 1.75
                [maquina] => Torno
            )

        [1] => stdClass Object
            (
            //Aqui deberia ser los campos de la tabla actividades
                [Nombre] => M49321
                [fecha] => 2018-02-12
                [NroSolicitud] => Reunion
                [horaInicio] => reunion de trabajo
                [horaTermino] => 1
                [Comentario] => 49321
                [total] => 17:43:00
                [maquina] => 1698
            )
 )

Instead of [NroSolicitud] => Reunion should be [actividad] => Reunion

I hope you have explained me well

sql php codeigniter codeigniter-3

asked by MoteCL 17.07.2018 в 18:36

source

1 answer

How do I get the value of a record selected by combobox using php [closed] This query works well for me in mysql but I want to pass it to the CodeIgniter form, models

score 1 · Answer 1

I hope it works for you, I illustrate it with a first image of the UNION from MYSQL.

step1:

Step2:

CodeIgniter Code

class Union_model extends CI_Model {

    public function getUnion($actividad){
        $query = $this->db->query("
        SELECT * FROM (SELECT id_tecnico,fecha,NroSolicitud,horaInicio,horaTermino,Comentario,total,'' as Actividad, '' TotalHrs FROM tabla1 
        UNION ALL
        SELECT id_tecnico,'' as fecha,NroSolicitud,'' as horaInicio,'' as horaTermino,Comentario,'' as total, Actividad, TotalHrs FROM tabla2 ) AS Mantenciones
        WHERE actividad =".$actividad);
        return $query->result();
    }

}