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

[eluser]dallen33[/eluser]

Code:

<h1>Latest Unread Comments</h1>
    &lt;?php
        $ads_query = $this->db->get_where('ads', array('artist' => $this->dx_auth->get_user_id()));
        $ads_row = $ads_query->row_array();
        
        $comments_query = $this->db->get_where('comments', array('ad_id' => $ads_row['id']));
        
        if ($comments_query->num_rows() > 0):
        foreach($comments_query->result_array() as $comments_row):
    ?&gt;
    &lt;?php echo $comments_row['id'] ?&gt;
    &lt;?php
        endforeach;
        elseif ($comments_query->num_rows() == 0):
    ?&gt;
    Sorry, nothing found.
    &lt;? endif; ?&gt;

Basically, I want to select all rows from table 'ads' where field 'artist' equals to the user_id (in this case, 3). Then, I want to select all rows from table 'comments' where field 'ad_id' equals to any 'id' from the table 'ads' that was previously selected.

I can't seem to figure out. I keep getting my elseif statement ("Sorry, nothing found."). There is only one row in the comments table, and the ad_id equals to a row id in 'ads'.

[eluser]Christopher Blankenship[/eluser]
if you are only attempting to return 1 record from the ads table but many records from the comments table as it appears in your code you can :
output your $ads_row

Array (
[0] => array(
'information' => 'here'
)
)

if you see something like this use the following $ads_row = array_shift($ads_query->result_array());

Then your code should work.

[eluser]dallen33[/eluser]
Hate to be a pest, but not sure how to output $ads_row. I know how to echo it, but not sure what I do in this case.

[eluser]Christopher Blankenship[/eluser]
print "<pre>";
print_r($ads_row);
print "</pre>";

works for me.

[eluser]dallen33[/eluser]
What I'm trying to do is return anything from the 'comments' table that has an 'ad_id' that equals to any 'id' in 'ads' table. It could be 0 results or 100 results. I don't think what you gave me helps in this situation, unless I'm totally losing it.

[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();

[eluser]darkhouse[/eluser]
Yeah, I think you should read up on using JOINs in SQL. It will make your life much easier. And also, I think you should make use of models instead of gathering your data directly in the view file.

Just speaking from experience, using Code Igniter the way it was intended by separating logic into controllers, getting and setting data in models, and displaying output in views has increased my productivity so much. I knew of MVC before I started using CI, but never actually used it. I can't see myself doing anything else now. It's like, once I organized everything properly I could think clearly.

Anyways, that's my 2 cents.