Two Queries, one html table

[eluser]Kemik[/eluser]
Hello,

I've got two queries, one to get latest 5 news and one to get latest 5 comments. I'm going to display them in a table on my homepage.

$query = news
$query2 = comments

Code:

<table width="95%">
                    <tr>
                      <th>new news</th>
                        <th>new comments</th>
                    </tr>
                    <tr class="row-a">
                      <td><strong>1</strong> | <a href="index.html">$row->news</a></td>
                        <td><strong>1</strong> | <a href="index.html">$row2->comment</a></td>
                    </tr>
                    <tr class="row-b">
                      <td><strong>2</strong> | <a href="index.html">Some news</a></td>
                        <td><strong>2</strong> | <a href="index.html">Some comments</a></td>
                    </tr>
                    <tr class="row-a">
                      <td><strong>3</strong> | <a href="index.html">Some news</a></td>
                        <td><strong>3</strong> | <a href="index.html">Some comments</a></td>
                    </tr>
                    <tr class="row-b">
                      <td><strong>4</strong> | <a href="index.html">Some news</a></td>
                        <td><strong>4</strong> | <a href="index.html">Some comments</a></td>
                    </tr>
                    <tr class="row-a">
                        <td><strong>5</strong> | <a href="index.html">Some news</a></td>
                        <td><strong>5</strong> | <a href="index.html">Some comments</a></td>
                    </tr>
              </table>

Usually I'd use <?php foreach($query->result() as $row): ?> content <?php endforeach; ?> but that won't work because the comments are pulled in one query and the news is pulled in a second query. Anyone got any ideas?

[eluser]xwero[/eluser]
As they both are joined with the news id you can merge them in the controller.

Code:

$comments = $query2->result_array();
$news = $query->result_array();
foreach($news as $key => $row)
{
   foreach($comments as $row2)
   {
      if($row2['news_id'] == $row['id'])
      {
          $news[$key]['comments'] = $row2;
      }
   }
}

But i think it's best for performance to use a query that merges the data instead of this solution.

[eluser]Kemik[/eluser]
The data isn't related otherwise I would have used a join.

[eluser]kgill[/eluser]
I think I see what you're trying to do, two columns of unrelated data sitting next to each other in a table. Is there a specific reason why you need to use a table instead of something simpler like outputting the results to separate divs placed next to each other?

If the table has to be there your best bet is probably nested tables, failing that you could do some really ugly coding and ditch the associative arrays in favor of plain 0 based ones - that way you can use the index of the one result set you're looping through to output the results of the other. Of course that brings a host of other code needed, count each result and see if one is bigger, use the larger one in your foreach, add conditional logic to make sure the index exists in the other before you attempt to reference it, etc.

- K

[eluser]xwero[/eluser]
I have to agree with kgill mixing unrelated data can create a mess of checks and you have to use an array to accomplish it instead of an object.

But it can be done

Code:

$comments = $query2->result_array();
$news = $query->result_array();
$biggest = array();
$smallest = array();
$commentsnr = count($comments);
$newsnr = count(news);
if(count($newsnr == $commentsnr) // most common occurrence  
{
    $biggest = $news; // you can switch them
    $smallest = $comments;
}
elseif($newsnr > $commentsnr) // can also be switched
{
    $biggest = $news;
    $smallest = $comments;
}
else
{
   $biggest = $comments;
   $smallest = $news;
}
// html
foreach($biggest as $key => $item)
{
   ?&gt;<td>&lt;?php echo $item; ?&gt;</td>
   <td>&lt;?php echo (isset($smallest[$key]))?$smallest[$key]:''; ?&gt;</td>&lt;?php
}

[eluser]webthink[/eluser]
There's about 5 different ways to do what you want to do but if you're sure you'll always have an equal number of news and comments this is probably the easiest. You just advance the pointer in query two at the same time you do in query 1 only you do it as a lone statement rather than a foreach param.

Code:

<?php foreach($query->result() as $row): ?>
<?php $row2=$query2->result(); ?>
                    <tr class="row-a">
                      <td><strong>1</strong> | <a href="index.html">$row->news</a></td>
                        <td><strong>1</strong> | <a href="index.html">$row2->comment</a></td>
                    </tr>
&lt;?php endforeach; ?&gt;

[eluser]xwero[/eluser]
The problem with my previous snippet is that the comments and news columns can change and that is not what you want

Code:

$comments = $query2->result_array();
$news = $query->result_array();
$commentsnr = count($comments);
$newsnr = count(news);
$highestnr = ($newsnr >= $commentsnr)?$newsnr:$commentsnr;
// html
for($i=0;$i<$highestnr;$i++)
{
   ?&gt;<td>&lt;?php echo (isset($news[$i]))?$news[$i]:''; ?&gt;</td>
   <td>&lt;?php echo (isset($comments[$i]))?$comments[$i]:''; ?&gt;</td>&lt;?php
}

After a few edits i finally got it right, maybe i should start drinking coffee

[eluser]Kemik[/eluser]
It's basically a table showing the latest 5 news articles and latest 5 comments. Comments can be for different news articles. I'm using a basic select statement with a 5 row limit ordered by date_posted DESC.

I'll try the code xwero posted above this reply and if all else fails I'll just use a div and restlye it to look like the table.