CodeIgniter Forums
SQL Query returns error - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: SQL Query returns error (/showthread.php?tid=73029)



SQL Query returns error - PARADISE - 03-11-2019

Hello, I'm trying to convert the query below into CI's Active Record form, but I get an error :

a.alliance_id could not be bound.

Here is the sql query (when i run it directly to SQL Server works fine)

PHP Code:
SELECT * , a.pledge_ida.ruler_id AS Expr2a.name AS pledge_namea.alliance_id AS Expr4a.challenge_time AS Expr5a.root_name_value AS Expr6a.now_war_id AS Expr7a.oust_time AS Expr8a.skill_level AS Expr9a.castle_id AS Expr10a.agit_id AS Expr11a.rank AS Expr12name_value AS Expr13a.status AS Expr14a.private_flag AS Expr15a.crest_id AS pledge_crest_ida.is_guilty AS Expr17a.dismiss_reserved_time AS Expr18a.alliance_withdraw_time AS Expr19a.alliance_dismiss_time AS Expr20a.alliance_ousted_time AS Expr21b.char_namec.name as ally_namec.crest_id as ally_crest_id
from pledge 
as a
left join user_data 
as b on a.ruler_id=b.char_id
left join alliance 
as c on a.alliance_id=c.id 
order by a
.skill_level desc 

Here is where I got so far. (Maybe I have some syntax error's?)
Thank you in advance!!

PHP Code:
->select("* , 
                a.pledge_id, 
                a.ruler_id AS 'Expr2', 
                a.name AS 'pledge_name', 
                a.alliance_id AS 'Expr4',
                a.challenge_time AS 'Expr5', 
                a.root_name_value AS 'Expr6', 
                a.now_war_id AS 'Expr7',
                a.oust_time AS 'Expr8',
                a.skill_level AS 'Expr9',
                a.castle_id AS 'Expr10',
                a.agit_id AS 'Expr11', 
                a.rank AS 'Expr12', 
                name_value AS 'Expr13', 
                a.status AS 'Expr14', 
                a.private_flag AS 'Expr15', 
                a.crest_id AS 'pledge_crest_id', 
                a.is_guilty AS 'Expr17', 
                a.dismiss_reserved_time AS 'Expr18', 
                a.alliance_withdraw_time AS 'Expr19', 
                a.alliance_dismiss_time AS 'Expr20', 
                a.alliance_ousted_time AS 'Expr21', 
                b.char_name, 
                c.name AS 'ally_name', 
                c.crest_id AS 'ally_crest_id'"
)
 
               ->join('user_data as b''a.ruler_id = b.char_id''left outer')
 
               ->join('Alliance as c''a.alliance_id = c.id''left outer')
 
               ->order_by('a.skill_level''DESC')
 
               ->get('Pledge as a'); 



RE: SQL Query returns error - php_rocs - 03-11-2019

@PARADISE,

I think your missing some pieces of the query builder...

->from('Pledge as a')
->join('user_data as b', 'a.ruler_id = b.char_id', 'left outer')
->join('Alliance as c', 'a.alliance_id = c.id', 'left outer')
->order_by('a.skill_level', 'DESC')
->get();


RE: SQL Query returns error - php_rocs - 03-11-2019

@PARADISE,

Or as I like it...using query bindings( https://www.codeigniter.com/userguide3/database/queries.html?highlight=query%20builder#query-bindings )...

PHP Code:
$qry ="SELECT * , 
          a.pledge_id, 
          a.ruler_id AS Expr2, 
          a.name AS pledge_name, 
          a.alliance_id AS Expr4, 
          a.challenge_time AS Expr5, 
          a.root_name_value AS Expr6, 
          a.now_war_id AS Expr7, 
          a.oust_time AS Expr8, 
          a.skill_level AS Expr9, 
          a.castle_id AS Expr10, 
          a.agit_id AS Expr11, 
          a.rank AS Expr12, 
          name_value AS Expr13, 
          a.status AS Expr14, 
          a.private_flag AS Expr15, 
          a.crest_id AS pledge_crest_id, 
          a.is_guilty AS Expr17, 
          a.dismiss_reserved_time AS Expr18, 
          a.alliance_withdraw_time AS Expr19, 
          a.alliance_dismiss_time AS Expr20, 
          a.alliance_ousted_time AS Expr21, 
          b.char_name, 
          c.name as ally_name, 
          c.crest_id as ally_crest_id
   from pledge as a
   left join user_data as b on a.ruler_id=b.char_id
   left join alliance as c on a.alliance_id=c.id 
   order by a.skill_level desc"
;

$this->db->query($qry); 



RE: SQL Query returns error - PARADISE - 03-11-2019

Will try to edit like you mentioned and see the results! Thanks!


RE: SQL Query returns error - PARADISE - 03-12-2019

(03-11-2019, 01:15 PM)php_rocs Wrote: @PARADISE,

I think your missing some pieces of the query builder...

->from('Pledge as a')
->join('user_data as b', 'a.ruler_id = b.char_id', 'left outer')
->join('Alliance as c', 'a.alliance_id = c.id', 'left outer')
->order_by('a.skill_level', 'DESC')
->get();

You were right! It worked that way!


RE: SQL Query returns error - php_rocs - 03-12-2019

@PARADISE,

I'm glad that I could help.