guinbRo Posted May 31, 2012 Share Posted May 31, 2012 So this topic could go to code igniter, mysql queries, or here. If this is in the wrong spot I apologize in advance. Anyways, my issue is generic. I want to get better with using proper MySQL queries and I'm trying to display a list of school classes that have assignments associated with each one. (Think like categories with blog posts associated to the categories). What I want to do is display a Class, and underneath have a list of assignments. However with the code I'm currently using it's printing the school class name for each assignment there is, not what I want or need. I could do this pretty easily by simply executing two queries (one to get the class, and the one to get the assignments associated with said class), but I feel that there is an easier--and more correct way. Here's the code i'm working with: blog_view.php <body style="margin-top: 100px;"> <div class="navbar navbar-fixed-top"> <div class="navbar-inner"> <div class="container"> <a class="brand" href="#">McDonald Sandbox</a> </div> </div> </div> <div class="container-fluid"> <div class="span2"> <ul class="nav nav-pills"> <li><a href="#"><i class="icon-plus-sign"></i> Add Class</a></li> <li><a href="#"><i class="icon-plus-sign"></i> Add Assignment</a></li> <li><a href="#"><i class="icon-wrench"></i> Settings</a></li> </ul> </div> <div class="span10"> <?php foreach($categories as $item): ?> <h2><?php echo $item['cat_title']; ?></h2> <div class="well"> <h4><?php echo $item['assign_name']; ?></h4> <?php echo $item['assign_desc']; ?> </div> <?php endforeach; ?> </div> </div> </body> </html> blog_model.php <?php class Blog_model extends CI_Model { function __construct() { $this->load->database(); } function get_latest() { //Select our classes, and get assignments from each class $this->db->select('*'); $this->db->from('categories'); $this->db->join('assignments', 'categories.cat_id = assignments.assign_cat_id'); //Return the classes and assignments in an array $query = $this->db->get(); return $query->result_array(); } } ?> blog.php (controller) <?php class Blog extends CI_Controller { public function __construct() { parent::__construct(); $this->load->model('blog_model'); } public function index() { $data['categories'] = $this->blog_model->get_latest(); $this->load->view('header'); $this->load->view('blog-view', $data); } } ?> Also please feel free to criticize coding habits that you feel I could do better in (not a whole lot of actual code there, but if anything stands out). Thanks for your help . Link to comment https://forums.phpfreaks.com/topic/263409-help-with-mysql-join/ Share on other sites More sharing options...
Barand Posted May 31, 2012 Share Posted May 31, 2012 output the class only when the value changes set lastClass = '' while assignments if currentClass != lastClass output currentClass heading set lastClass = currentClass endif output assignment data endwhile Link to comment https://forums.phpfreaks.com/topic/263409-help-with-mysql-join/#findComment-1350033 Share on other sites More sharing options...
guinbRo Posted May 31, 2012 Author Share Posted May 31, 2012 Thank you. I used the group_concat and group_by to achieve the results I some what wanted, but your method is giving me more control and is leaving less of a code footprint in my view file. Thanks again . Link to comment https://forums.phpfreaks.com/topic/263409-help-with-mysql-join/#findComment-1350081 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.