Getting the number of images of an item : Join and Count - 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: Getting the number of images of an item : Join and Count (/showthread.php?tid=72788) |
Getting the number of images of an item : Join and Count - demyr - 02-12-2019 Hi there, As you may guess from topic title I have got 2 tables : (let's say) posts and post_images. POSTS TABLE consists of: post_id - posts_name - post_text - post_date POST_IMAGES consists of: post_image_id - post_id - post_url And on my admin panel I have got a page which lists posts as a table (as a result of this Model and Controller). Model: PHP Code: function my_posts(){ Controller: PHP Code: public function posts(){ result page : post_name - post_date - how-many-images-does-this-post-have example1 01.12.2019 ?????? example2 02.12.2019 ?????? How can I get the image number of each blog post? P.S. I have tried lots of examples on stackoverfow and here about joins-counts and grouping even, however as I couldn't succeed it, I am posting here as plain as possible Thanks in advance RE: Getting the number of images of an item : Join and Count - neuron - 02-12-2019 PHP Code: function my_posts(){ when you do left join all the rows from posts are retrieved. left join is used in case if there is no image for the post, if left is not used then that post is won't be retrieved. if there are N images for the post the there are will be N records(rows) for the post, but as you see I used to GROUP BY clause which groups by post_id that means those N recordes are become 1 row. COUNT(pi.post_image_id) counts the number of image in the group. if you join another table you may need change it to COUNT(DISTINCT pi.post_image_id) RE: Getting the number of images of an item : Join and Count - demyr - 02-12-2019 (02-12-2019, 01:34 AM)neuron Wrote: #neuron Thank you so much for your detailed and explanatory answer. Probably my last question will be this: How to show this on my View? I used to have this foreach structure within my view: PHP Code: <?php $countit=1; foreach($posts as $post) { ?> RE: Getting the number of images of an item : Join and Count - demyr - 02-12-2019 #neuron Oh, my mistake, sorry. I found it out while I was thinking what is the "as image_count" for? It was the answer : PHP Code: $post->image_count; Thank you very much again! |