Codeigniter: Trying to run a query within a loop in the View

Question

I have a page that displays posts and I want to get the name of the author next to each post. There is a DB table for pages (with a column called title and author) and one for authors (with a column called id and author).

Control

function pages() { // QUERIES $pages_query = $this->db->get('table_pages'); $authors_query = $this->db->get_where('table_authors', array('id =' => $row->author)); // DATA $data['pages'] = $pages_query; $data['authors'] = $authors_query; // CREATE $this->load->view('admin_pages', $data); }

View

 <? foreach ($pages->result() as $row): ?> Title: <?=$row->title?>, by <?=$authors['id']?><br /> <? endforeach; ?>

I think my issue is in calling the author based on the id in control but I just can't seem to figure it out.

I think you need join tables, is ther author_id column in table_pages ? — safarov
– safarov, Commented Mar 30, 2012 at 9:36

Bram · Accepted Answer · 2012-03-30 09:39:00Z

Basically, you want to JOIN the two tables, so you can access the author names directly, you don't need to use two queries for that...

You want to do something like:

function pages() { // QUERIES $this->db->join('table_authors', 'table_pages.table_authors_id = table_authors.id'); $pages_query = $this->db->get('table_pages'); // DATA $data['pages'] = $pages_query; // CREATE $this->load->view('admin_pages', $data); }

View

<? foreach ($pages->result() as $row): ?> Title: <?=$row->title?>, by <?=$row->table_authors_name?><br /> <? endforeach; ?>

You might have to adjust some column names, as I don't know your database structure. If both table_pages and table_authors contain name or title fields, you'll have to rename the columns in your query. You can do this using

$this->db->select('table_authors.title AS author_name, ...');

Thank you. This worked for me. The JOIN Active Record Class doesn't have very good documentation on the CI site and I've been trying to avoid using in-line queries as a practice.
The Active Record class is nothing more than a set of wrappers to write SQL queries. If you're uncertain of how to do something with Active Record, try figuring out how to do it with pure SQL. A better understanding of (My)SQL will help you a lot!

Srinivasulu Rao · Accepted Answer · 2015-08-21 14:29:44Z

You can do this in a very simple way, just write the query, get the data and pass it to the view loading function.

 For controller you can write like this function pages() { $pages_query = $this->db->query("SELECT * FROM table_authors as a INNER JOIN table_pages as p ON a.id=p.table_authors_id"); // DATA $data['pages'] = $pages_query->result_array(); // CREATE $this->load->view('admin_pages', $data); // Passing the data to the view loading function. } For Views You can write like this. <?php foreach($pages as $page): echo "Title: ".$page['title'].", by ".$page['table_authors_id']; endforeach; ?>

Collectives™ on Stack Overflow

Codeigniter: Trying to run a query within a loop in the View

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related