Can't figure out how to write this query properly... involves two tables.

[eluser]Christopher Blankenship[/eluser]
Still not exactly sure what you are looking for as your code first looks at the ads table then the comments table but you are explaining in reverse. The code below would go through the ads table and retrieve all artist ids. Then would create an ads string to be used in the MYSQL IN in the comments query. Then you could output the $comments and see if you had any records returned or conduct your if else statement. Let me know if this is what you are looking for granted there may be spelling or other little issues but you get the idea what its doing.

Code:

// THE DIFFICULT WAY..............
<h1>Latest Unread Comments</h1>
&lt;?php

    $ads = '';
    $ads_query = $this->db->get('ads'); // get all records from the ads
    $ads_row = $ads_query->result_array();
    foreach($ads_row as $name => $val):
        $ads .= "'".$val['id']."',"; // build string for the MySQL IN
    endforeach;
    $ads = rtrim(', ', $ads); // trim space and comma off the end of ads
        
    $comments_query = $this->db->query("Select * from comments where ad_id IN ($ads)");
    $comments = $comments_query->result_array();
    print "<pre>";
    print_r($comments);
    print "</pre>";
?&gt;

And there are other ways of writing it, here is simpler and better way.

Code:

// THE SIMPLE WAY..........
$sql = "SELECT * FROM comments as c, ads as a WHERE c.ad_id = a.id"; //this would only be one query.
$comments_query = $this->db->query($sql);
$comments = $comments_query->result_array();