Welcome Guest, Not a member yet? Register   Sign In
Rewriting query in query builder
#1

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 Smile
Reply
#2

(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'])]); 
Reply
#3

(02-08-2019, 08:53 AM)php_rocs Wrote: Thanks for reply, I was looking to use CI query builder Smile

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

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




Theme © iAndrew 2016 - Forum software by © MyBB