value in a JOIN - Printable Version

value in a JOIN - 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: value in a JOIN (/showthread.php?tid=51320)

Pages: 1 2

value in a JOIN - El Forum - 04-29-2012

[eluser]truman_truman[/eluser]
Hello,
I translated with google translator
I do not speak English. sorry.

why does not this work?:

$this->db->join('users u', 'u.id = 1');

OR

$this->db->join('users u', 'u.id ='.$my_variable);

thanks

value in a JOIN - El Forum - 04-29-2012

[eluser]Stefan Hueg[/eluser]
Try this:

Code:
$this->db->join('users AS u', 'u.id = '.$my_variable);

value in a JOIN - El Forum - 04-29-2012

[eluser]truman_truman[/eluser]
[quote author="Stefan Hueg" date="1335724419"]Try this:

Code:
$this->db->join('users AS u', 'u.id = '.$my_variable);

[/quote]

That does not work

Error Number: 1054

Unknown column '1 'in' on clause '

JOIN `users` AS u ON `u`.`id` = `1`

thanks for your answer!

value in a JOIN - El Forum - 04-29-2012

[eluser]Stefan Hueg[/eluser]
Post your complete function please. There must be something else missing.

value in a JOIN - El Forum - 04-29-2012

[eluser]truman_truman[/eluser]

Code:
function listar_facturas($cuantos, $desde = 0) {

        $my_variable = 1;

        $this->db->select('

            f.id, 

            f.fecha_emision, 

            f.fecha_vencimiento,

            f.id_estado,

            f.total,

            f.id_servicio,

            s.servicio,

            fe.estado

            ');

        $this->db->from('facturas f');

        $this->db->join('servicios s', 'f.id_servicio = s.id');       

        $this->db->join('usuarios AS u', 'u.id = ' . $my_variable);

        $this->db->join('fact_estados fe', 'fe.id = f.id_estado');

        $this->db->order_by('fecha_emision ' . 'desc');

        $this->db->limit($cuantos, $desde);

        $query = $this->db->get();

        return $query->result();

    }

value in a JOIN - El Forum - 04-29-2012

[eluser]Stefan Hueg[/eluser]
Try this:

Code:
function listar_facturas($cuantos, $desde = 0) {

        $my_variable = 1;

        $this->db->select('

            f.id, 

            f.fecha_emision, 

            f.fecha_vencimiento,

            f.id_estado,

            f.total,

            f.id_servicio,

            s.servicio,

            fe.estado

            ');

        $this->db->from('facturas AS f');

        $this->db->join('servicios AS s', 'f.id_servicio = s.id');       

        $this->db->join('usuarios AS u', 'u.id = ' . $my_variable);

        $this->db->join('fact_estados AS fe', 'fe.id = f.id_estado');

        $this->db->order_by('fecha_emision', 'desc');

        $this->db->limit($cuantos, $desde);

        $query = $this->db->get();

        return $query->result();

    }

value in a JOIN - El Forum - 04-29-2012

[eluser]truman_truman[/eluser]
That does not work.
The problem is:
$this->db->join('usuarios AS u', 'u.id = ' . $my_variable);

Code:
'u.id = ' . $my_variable

codeigniter interprets what is on the right of the sign "=" as a column
So he says: Unknown column '1 'in' on clause '

(codeigniter interpreta lo que está a la derecha del signo "=" como una columna)
por eso dice: Unknown column '1 'in' on clause '

value in a JOIN - El Forum - 04-29-2012

[eluser]Stefan Hueg[/eluser]
Oh okay I see it now. But is it your intention to join the users table on every row with u.id = $my_variable?

Shouldn't that be rather:

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

...and joining the users table on something else that is useful for you?

value in a JOIN - El Forum - 04-29-2012

[eluser]truman_truman[/eluser]
that is an effective solution, but my question is:
you can put a variable value?

Code:
'u.id = ' . $my_variable

eso es una solucion efectiva, pero mi pregunta es:
se puede poner un valor variable?

Code:
'u.id = ' . $my_variable

value in a JOIN - El Forum - 04-29-2012

[eluser]Stefan Hueg[/eluser]
That's because the number 1 is escaped automatically to secure the database queries. And for your database ´1´ looks like a column name.

You can of course use $this->db->query('SELECT * FROM ....') to write your own SQL statement but ensure that your variables are escaped safely.