Selecting Data

[eluser]Unknown[/eluser]
New to CodeIgniter, just reading through the User Guide and had a quick question, and I know this pretty dumb:

I am looking at selecting data from tables in my database and can do this when specifying a single table as shown in the user guide. Now I want to select 3 tables from my database what is the recommended thing to do, I can't see this in the user guide.

Do I just:

$query = $this->db->get('mytable1','mytable2''mytable3');

I would try this myself and see the results, but I am at such an early stage in my development I think a pointer in the right direction and a little insight would be more helpful.

Thanks.

[eluser]Unknown[/eluser]
So I have had a go at my earlier problem and have made some progress, but can't finish it fully by myself.

I have manged to select data from the tables in my database using the following code:

Code:

$this->db->select("a.artist_name, t.title");
$this->db->from("record_associations AS ra, artists AS a, titles AS t");
$this->db->where("ra.artist_id = a.artist_name AND ra.title_id = t.title");

When I query this in SQL form everything like I expected it to.

I also have the following lines:

Code:

$data['query'] = $this->db->get();
$this->load->view('main_view', $data);

To my View:

Code:

<?php if ($query->num_rows() > 0): ?>
<?php foreach($query->result() as $row): ?>
<p>&lt;?=$row->title?&gt;</p>
<h3>&lt;?=$row->artist_name?&gt;</h3>
<hr/>
&lt;?php endforeach; ?&gt;

Which should display my artists and titles, but it seems that $query has no rows and I don't think that the data from my db is being sent correctly. This view has worked before when I sent a single specific table.

Anyone understand where I have gone wrong?

[eluser]Murodese[/eluser]
Easiest way to debug this is just to echo $this->db->last_query(); after your query and check the query to see where it's gone horribly wrong :v

[eluser]JoostV[/eluser]
Also the code were you fetch the row from the database should not be in the view, if you want a clean separation between logic and view. It should be in your model, and passed to the view by the controller.
This way you can reuse your query in different places, but you'll only have to define it once.

So in the model (say it is called 'artist_titles'):

Code:

function get_artist_titles()
{
    // Define array to return
    $result = array();

    // Set up SQL
    $this->db->select('artists.artist_name, titles.title');
    $this->db->from('record_associations');
    $this->db->join('artists', 'record_associations.artist_id = artists.artist_name');
    $this->db->join('titles ', 'record_associations.title_id = titles.title');

    // Run query
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        $result = $query->result_array();
    }
    
    // Return results
    return $result;
}

In the controller:

Code:

// Fetch an array of titles and artists from database
$this->load->model('artist_titles');
$data['artist_titles'] = $this->artist_titles->get();

// Load view and pass result array
$this->load->view('main_view', $data);

In the view:

Code:

<?php foreach($artist_titles as $row): ?>
<p>&lt;?=$row['title']?&gt;</p>
<h3>&lt;?=$row['artist_name']?&gt;</h3>
<hr/>
&lt;?php endforeach; ?&gt;

[eluser]BobbyB[/eluser]
Hi thanks for the above code!
I just wanted to make a small correction:

Code:

function get_artist_titles()
{...

should be:

Code:

function get()
{...

Took me about 1 hour to find out
Hope I could help other beginners like me with that.
Cheers