Complex DB query using CI helpers

Complex DB query using CI helpers - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Complex DB query using CI helpers (/showthread.php?tid=3088)

Complex DB query using CI helpers - El Forum - 09-09-2007

[eluser]Guillem[/eluser]
I have a table (altas) in my db with some foreign keys to other fields of other tables (clientes, estados, usuarios):

Quote:id
id_usuario => usuarios->id
id_estado => estados->id
id_cliente => clientes->id
...

I was trying to use the helpers of CI, since I'd like to make a query with some values that might be entered into a form. My code is that:

Code:
$this->db->select('u.usuario, c.id as cid, c.apellidos, c.nombre, c.dni, e.estado, a.id, a.fecha, a.comentario, a.linea');

if ($this->session->userdata('nivel')>1) {

    if ($_POST['usuario'] != 0) { $this->db->where('a.id_usuario',$_POST['usuario']); }

}

if ($_POST['operadora'] != 0) { $this->db->where('a.id_operadora',$_POST['operadora']); }

if ($_POST['desde'] != null) {         

    $a = explode("/",$_POST['desde'],3);

    $this->db->where('a.fecha >=',mktime(0,0,0,$a[1],$a[0],$a[3]));

}

if ($_POST['hasta'] != null) {         

    $a = explode("/",$_POST['hasta'],3);

    $this->db->where('a.fecha <=',mktime(24,59,59,$a[1],$a[0],$a[3]));

            }

if ($_POST['estado'] != 0) { $this->db->where('a.id_estado',$_POST['estado']); }

$this->db->where('u.id','a.id_usuario');

$this->db->where('c.id','a.id_cliente');

$this->db->where('e.id','a.id_estado');

$this->db->orderby('a.fecha','asc');

$data['altas'] = $this->db->get('altas a, clientes c, usuarios u, estados e');

It seemed it worked fine until I added this 3 lines:

Code:
$this->db->where('u.id','a.id_usuario');

$this->db->where('c.id','a.id_cliente');

$this->db->where('e.id','a.id_estado');

What I was trying to do is to generate a query similar to this:

Code:
$this->db->query("SELECT u.usuario, c.id as cid, c.apellidos, c.nombre, c.dni, e.estado, a.id, a.fecha, a.comentario, a.linea FROM altas a, usuarios u, estados e, clientes c WHERE a.id_usuario='".$_POST['usuario']."' AND a.id_operadora='".$_POST['operadora']."' AND a.fecha>='".$_POST['desde']."' AND a.fecha<='".$_POST['hasta']."' AND a.id_estado='".$_POST['estado']."' AND u.id=a.id_usuario AND c.id=a.id_cliente AND e.id=a.id_estado ORDER BY a.fecha DESC");

I'm really a pretty newbie in CI development, so I'm quite confused about what to do, since this seems quite coherent for me, but the query shows nothing with the helpers and data if I run it manually.

Can any body make me see the light, please?

Thank you all folks Smile