+- 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: Help with RIGHT JOIN conversion? (/showthread.php?tid=75285)
Help with RIGHT JOIN conversion? - nfoboy - 01-21-2020
MySQL query is as follows:
Code:
SELECT RossResearchDB.property.id, RossResearchDB.property.lat, RossResearchDB.property.long, RossResearchDB.property.property_name, RossResearchDB.liked_property.FK_Renter FROM RossResearchDB.liked_property right join RossResearchDB.property
on RossResearchDB.liked_property.FK_Property = RossResearchDB.property.id
and FK_Renter = 2;I am lost on how to set this up using the CI syntax.
Any help on the way ahead is appreciated!
Ross
RE: Help with RIGHT JOIN conversion? - nfoboy - 01-21-2020
I have this figured out so far: (shortened my requested fields until I get this figured out)
Code:
$this->db->select('property.id , property.lat, liked_property.FK_Property');
$this->db->from('property');
$this->db->join('liked_property', 'liked_property.FK_Property = property.id', 'left');
$this->db->where('liked_property.FK_Renter', 2);except that "where" is not giving me the AND portion... I get
SELECT `property`.`id`, `property`.`lat`, `liked_property`.`FK_Property` FROM `property` LEFT JOIN `liked_property` ON `liked_property`.`FK_Property` = `property`.`id` WHERE `liked_property`.`FK_Renter` = 2
RE: Help with RIGHT JOIN conversion? - php_rocs - 01-22-2020
@nfoboy,
Try this https://codeigniter.com/user_guide/database/queries.html?highlight=query%20builder#query-bindings it should do the job for you.
RE: Help with RIGHT JOIN conversion? - vincent78 - 01-22-2020
$this->db->select('property.id , property.lat, liked_property.FK_Property');
$this->db->from('property');
$this->db->join('liked_property', 'liked_property.FK_Property = property.id and liked_property.FK_Renter = 2', 'left');
$this->db->where('liked_property.FK_Renter', 2);
RE: Help with RIGHT JOIN conversion? - nfoboy - 01-22-2020
(01-22-2020, 07:48 AM)php_rocs Wrote: @nfoboy,I saw that, but I didn't get that I could use it with the join statement, as vincent78 pointed out.
Try this https://codeigniter.com/user_guide/database/queries.html?highlight=query%20builder#query-bindings it should do the job for you.
RE: Help with RIGHT JOIN conversion? - nfoboy - 01-22-2020
(01-22-2020, 09:59 AM)vincent78 Wrote: $this->db->select('property.id , property.lat, liked_property.FK_Property');Thanks so much! I was trying the Query Builder documentation, but just did not catch this particular nuance of chaining...
$this->db->from('property');
$this->db->join('liked_property', 'liked_property.FK_Property = property.id and liked_property.FK_Renter = 2', 'left');
$this->db->where('liked_property.FK_Renter', 2);
RE: Help with RIGHT JOIN conversion? - php_rocs - 01-22-2020
@nfoboy,
For future reference.
Query binding allows you to write a full sql statement with or without any variables that are needed to complete the query.
$sql = "SELECT p.id, p.lat, l.FK_Property FROM property as p LEFT JOIN liked_property as l ON l.FK_Property = p.id and l.FK_Renter = 2";
$this->db->query($sql);
RE: Help with RIGHT JOIN conversion? - nfoboy - 01-22-2020
(01-22-2020, 02:45 PM)php_rocs Wrote: @nfoboy,Thanks... I'm trying to get my CI Judo up to snuff, and having to translate SQL to CI, has been driving me batty at times. I can use this as a last resort to push what I need through....
For future reference.
Query binding allows you to write a full sql statement with or without any variables that are needed to complete the query.
$sql = "SELECT p.id, p.lat, l.FK_Property FROM property as p LEFT JOIN liked_property as l ON l.FK_Property = p.id and l.FK_Renter = 2";
$this->db->query($sql);
RE: Help with RIGHT JOIN conversion? - jreklund - 01-23-2020
You don't need to use the Query Builder. Just use your queries as is with query bindings instead.
https://codeigniter.com/user_guide/database/queries.html#query-bindings