-
roland
Newbie
-
Posts: 2
Threads: 1
Joined: Feb 2019
Reputation:
0
Hi there,
I'm pretty new to Codeigniter. I have trouble with Mysql query converting to CI query builder.
So, original query is this:
PHP Code: $query = $this->db->query('SELECT' . ' p_pedestal.*,' . ' dev.dev_name AS device,' . ' p_port.title AS port,' . ' UNIX_TIMESTAMP(p_pedestal.created) AS created_t,' . ' UNIX_TIMESTAMP(p_pedestal.updated) AS updated_t' . ' FROM p_pedestal' . ' LEFT JOIN p_port' . ' ON p_pedestal.port_id = p_port.id' . ($this->system->isOwner()? ' JOIN p_port_user_rel ON (p_port_user_rel.username = "' . strtolower($_SESSION['user']['username']) . '") AND p_port_user_rel.port_id = p_pedestal.port_id' : '' ) . ' INNER JOIN dev' . ' ON p_pedestal.dev_id = dev.dev_id' . ' ORDER BY p_pedestal.formatted_address ASC ' );
It works fine, all is good.
Now, I tried rewriting it and it just never works, I have no idea why.
This is what I got so far:
PHP Code: $query = $this->db ->select('p_pedestal.*, dev.dev_name AS device, p_port.title AS port, UNIX_TIMESTAMP(p_pedestal.created) AS created_t, UNIX_TIMESTAMP(p_pedestal.updated) AS updated_t' ) ->from('p_pedestal') ->join('p_port', 'p_pedestal.port_id = p_port.id', 'left') ->join('p_port_user_rel', 'p_port_user_rel.username =' . strtolower($_SESSION['user']['username']) ) ->where('p_port_user_rel.port_id', 'p_pedestal.port_id') ->join('dev', 'p_pedestal.dev_id = dev.dev_id', 'inner') ->order_by('p_pedestal.formatted_address') ->get();
And second part of my question is- how would you go about $this->system->isOwner()?
Would you put it in if statement, run one query or run another one with additional Join?
Thanks in advance
-
php_rocs
Administrator
-
Posts: 1,415
Threads: 103
Joined: Jun 2016
Reputation:
73
02-08-2019, 08:53 AM
(This post was last modified: 02-08-2019, 09:00 AM by php_rocs.)
@ roland,
Why not do it this way (documentation: https://codeigniter.com/user_guide/datab...y-bindings )...
PHP Code: $sql = "SELECT p_pedestal.*, ev.dev_name AS device, p_port.title AS port, UNIX_TIMESTAMP(p_pedestal.created) AS created_t, UNIX_TIMESTAMP(p_pedestal.updated) AS updated_t FROM p_pedestal LEFT JOIN p_port ON p_pedestal.port_id = p_port.id JOIN p_port_user_rel ON (p_port_user_rel.username = ? ) AND p_port_user_rel.port_id = p_pedestal.port_id INNER JOIN dev ON p_pedestal.dev_id = dev.dev_id ORDER BY p_pedestal.formatted_address ASC"; $this->db->query($sql, [strtolower($_SESSION['user']['username'])]);
-
roland
Newbie
-
Posts: 2
Threads: 1
Joined: Feb 2019
Reputation:
0
(02-08-2019, 08:53 AM)php_rocs Wrote: Thanks for reply, I was looking to use CI query builder
Thanks
@roland,
Why not do it this way (documentation: https://codeigniter.com/user_guide/datab...y-bindings )...
PHP Code: $sql = "SELECT p_pedestal.*, ev.dev_name AS device, p_port.title AS port, UNIX_TIMESTAMP(p_pedestal.created) AS created_t, UNIX_TIMESTAMP(p_pedestal.updated) AS updated_t FROM p_pedestal LEFT JOIN p_port ON p_pedestal.port_id = p_port.id JOIN p_port_user_rel ON (p_port_user_rel.username = ? ) AND p_port_user_rel.port_id = p_pedestal.port_id INNER JOIN dev ON p_pedestal.dev_id = dev.dev_id ORDER BY p_pedestal.formatted_address ASC"; $this->db->query($sql, [strtolower($_SESSION['user']['username'])]);
-
dave friend
Posting Freak
-
Posts: 1,015
Threads: 15
Joined: Jun 2015
Reputation:
50
This might be what you're looking for. Obviously, I cannot test it so there might be small syntax errors. But this should be pretty close.
I'm not confident that the UNIX_TIMESTAMP part of the select statement will work as desired. That might be part of why "...it just never works," Please tell us what, if any, error messages the code produces.
PHP Code: $this->db ->select("SELECT p_pedestal.*, dev.dev_name AS device, p_port.title AS port, UNIX_TIMESTAMP(p_pedestal.created) AS created_t, UNIX_TIMESTAMP(p_pedestal.updated) AS updated_t") ->join("p_port", "p_pedestal.port_id = p_port.id", 'LEFT');
if($this->system->isOwner()) { $user = strtolower($_SESSION['user']['username']) ; //Note use of double quotes on second argument, Need them so $user will evaluate. $this->db->join('p_port_user_rel', "p_port_user_rel.username = $user AND p_port_user_rel.port_id = p_pedestal.port_id"); }
$query = $this->db ->join('dev', 'p_pedestal.dev_id = dev.dev_id', 'INNER') ->order_by('p_pedestal.formatted_address', 'ASC') ->get('p_pedestal') ;
|