• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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
@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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.