Rewriting query in query builder

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

@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'])]); 


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'])]); 


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') ;