learningbyexample learningbyexample - 6 months ago 13
PHP Question

How do I access data passed from a query in codeigniter

How do I get the data that should be passed back from this query. This is in my model. I don't know if i'm doing this right since I haven't gotten back any output. I have already tested the query in phpmyadmin and it works. I need to be able to pass data from

function get_reg2()
to my controller successfully.

I dont know if the return is doing it right

return $this->db->query($query, $data);

Joins_model extends CI_Model....
function get_reg2($data){
$query = ('

SELECT DISTINCT
cm_valuacion.mano_obra,
cm_nomina.nomina,
cm_empleado.nombre,
cm_proveedor.nombre,
cm_valuacion.hojalateria,
cm_valuacion.pintura,
cm_valuacion.mecanica,
cm_valuacion.refaccion,
cm_valuacion.tipo,
cm_valuacion.shojalateria,
cm_valuacion.spintura,
cm_valuacion.smecanica,
cm_valuacion.costoHojalateria,
cm_valuacion.costoPintura,
cm_valuacion.costoMecanica,
cm_valuacion.pv_hojalateria,
cm_valuacion.pv_pintura,
cm_valuacion.pv_mecanica,
cm_valuacion.pc_hojalateria,
cm_valuacion.pc_pintura,
cm_valuacion.pc_mecanica,
cm_compras.precio,
cm_compras.status
FROM cm_valuacionr,
cm_nomina
INNER JOIN cm_empleado
ON cm_nomina.id_empleado = cm_empleado.id
INNER JOIN cm_valuacion
ON cm_valuacion.id_siniestro = cm_nomina.id_siniestro
INNER JOIN cm_compras
ON cm_compras.id_siniestro = cm_valuacion.id_siniestro
INNER JOIN cm_proveedor
ON cm_compras.id_proveedor = cm_proveedor.id
WHERE cm_valuacion.id_siniestro = ?
AND cm_valuacion.id_complemento = 0
GROUP BY mano_obra
');
return $this->db->query($query, $data);

}

}


also I don't know if im receiving it correctly here

$registros = $this->joins_model->get_reg2($id);


what I have in my controller is:

$registros = $this->joins_model->get_reg2($id);
$this->load->library('table');
$this->table->set_empty(" ");
$this->table->set_heading(
$this->st_cell('D'),
$this->st_cell('CANT.'),
$this->st_cell('PROVEEDOR'),
$this->st_cell('NOTA'),
$this->st_cell('DESCRIPCION'),
$this->st_cell('COSTO'),
$this->st_cell('VENTA'),
$this->st_cell('EMPLEADO'),
$this->st_cell('NOMINA'),
$this->st_cell('TOTAL')
);

foreach ($registros->result() as $registro){
//this is where I want to be able to specify what parameter I want
//to access from the query that was returned. How do I do this?
//for example theres *nombre* up there and it's there twice. Once
//for *empleado* and the other for *proveedor*
}


and using it correctly here;

$registros->result() as $registro

Answer

Assumptions

  1. In Model get_reg2($id) I assumed data is receiving.
  2. Your SQL code is working fine.

Changes

  1. Model code Modified, with returning objective array.
  2. Controller Function changed.
  3. Table heading modified.
  4. foreach loop pointed with data to present.

Reefer

  1. HTML Table Class in Codeigniter

Try this

In Model

function get_reg2($id)
{
    $query = ('
        SELECT DISTINCT
            cm_valuacion.mano_obra,
            cm_nomina.nomina,
            cm_empleado.nombre,
            cm_proveedor.nombre,
            cm_valuacion.hojalateria,
            cm_valuacion.pintura,
            cm_valuacion.mecanica,
            cm_valuacion.refaccion,
            cm_valuacion.tipo,
            cm_valuacion.shojalateria,
            cm_valuacion.spintura,
            cm_valuacion.smecanica,
            cm_valuacion.costoHojalateria,
            cm_valuacion.costoPintura,
            cm_valuacion.costoMecanica,
            cm_valuacion.pv_hojalateria,
            cm_valuacion.pv_pintura,
            cm_valuacion.pv_mecanica,
            cm_valuacion.pc_hojalateria,
            cm_valuacion.pc_pintura,
            cm_valuacion.pc_mecanica,
            cm_compras.precio,
            cm_compras.status
        FROM cm_valuacionr,
            cm_nomina
        INNER JOIN cm_empleado
            ON cm_nomina.id_empleado = cm_empleado.id
        INNER JOIN cm_valuacion
            ON cm_valuacion.id_siniestro = cm_nomina.id_siniestro
        INNER JOIN cm_compras
            ON cm_compras.id_siniestro = cm_valuacion.id_siniestro
        INNER JOIN cm_proveedor
            ON cm_compras.id_proveedor = cm_proveedor.id
        WHERE cm_valuacion.id_siniestro = ?
            AND cm_valuacion.id_complemento = 0
        GROUP BY mano_obra
            ');
        $query =  $this->db->query($query, $id); # Changed
        $result = $query->result_array(); # Added
        return $result; # Added
    }
}

In Controller

$registros = $this->joins_model->get_reg3($id);
//var_dump($registros);
//print_r($registros);
if (empty($registros))
{
    echo "No data recived from Database";
}else{

    ?>
    <table border="2px">
    <tr>
        <th>D</th>
        <th>Cant</th>
    </tr>
        <?php
        foreach ($registros as $registro)
        {
            ?>
            <tr>
            <td><?php echo $registro['mano_obra'] ?></td> 
            <td><?php echo $registro['nomina'] ?></td> 
            </tr>
        <?php
        }
        ?>
    </table>

    <?php
}