Welcome Guest, Not a member yet? Register   Sign In
Writing Query in Active Record
#1

[eluser]RMinor[/eluser]
How would I write the following query in CodeIgniter's active record class?

Code:
SELECT event . * , GROUP_CONCAT(CONCAT( base_url() .'media/photos/thumbnail/', photo_thumbnail )
SEPARATOR '|' ) AS thumbnails
FROM event
LEFT JOIN photo ON event_id = photo_event
WHERE event_user = ?
GROUP BY event.event_id
#2

[eluser]PhilTem[/eluser]
Did you have a look at the AR documentation? If not, go for this first, then we can help with solving problems Wink
#3

[eluser]RMinor[/eluser]
Yeah I looked it over. I used it to make every query active record, but couldn't figure out how to make this one work.
#4

[eluser]RMinor[/eluser]
Here is what I have so far...

Code:
$this->db->select('event.*');
$this->db->select('GROUP_CONCAT(CONCAT(' . base_url() . '"media/photos/thumbnail/", photo_thumbnail) SEPARATOR "|" ) AS thumbnails');
$this->db->from('event');
$this->db->join('photo', 'event_id = photo_event', 'left');
$this->db->where('event_user', $user_id);
$this->db->group_by('event.event_id');
$query = $this->db->get();
if ($query->num_rows() > 0) {
    return $query->result_array();
}
return FALSE;

...and this is my error.

Code:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://weddingconnect.minorcorpllc.com/"media/photos/thumbnail/", `photo_thumbnail)`' at line 1

SELECT `event`.*, GROUP_CONCAT(CONCAT(http://weddingconnect.minorcorpllc.com/"media/photos/thumbnail/", `photo_thumbnail)` SEPARATOR "|" ) AS thumbnails FROM (`event`) LEFT JOIN `photo` ON `event_id` = `photo_event` WHERE `event_user` = '6' GROUP BY `event`.`event_id`
#5

[eluser]RMinor[/eluser]
Okay I figured it out. Here is the finished query for those interested.

Code:
$this->db->select('event.*');
  $this->db->select('GROUP_CONCAT(CONCAT(\'' . base_url() . 'media/photos/thumbnail/\', photo_thumbnail ) SEPARATOR \'|\' ) AS thumbnails');
  $this->db->from('event');
  $this->db->join('photo', 'event_id = photo_event', 'left');
  $this->db->where('event_user', $user_id);
  $this->db->group_by('event.event_id');
  $query = $this->db->get();
  if ($query->num_rows() > 0) {
   return $query->result_array();
  }
  return FALSE;
#6

[eluser]CroNiX[/eluser]
You should also look at the 2nd parameter of db:Confusedelect() for more complex queries.
#7

[eluser]RMinor[/eluser]
Okay I will keep that in mind when writing complex ones. Also, do I need to add FALSE as a second parameter to the second select.
#8

[eluser]CroNiX[/eluser]
Not if it's working...

Here is a similar select I have to show...
Code:
$db->select("CONCAT('http://listings.', user_agent_profile.website_url, 'somedomain.com/i/', user_agent_profile.website_idx_cid, '/') as idx_url", FALSE)

Notice how much cleaner it is (it's also all wrapped in double quotes while quoted text within the query is using single quotes - such as in the CONCAT. You're using single quotes for everything which makes you have to escape them first for the ones that are actually supposed to be quotes in your query)




Theme © iAndrew 2016 - Forum software by © MyBB