BetterPHP

I want to basically have 3 blogs but have one page where the posts from all both are shown. I would think you could do something like:

<?php
		    
    $projects = get_projects();
    
    foreach ($projects as $project){
        ?>
        <h2><a href="project.php?prid=<?php echo $project['id']; ?>"><?php echo $project['title']; ?></a></h2>
        <h4>By <?php echo $project['user']; ?> on <?php echo $project['date']; ?></h4>
        <h4>(<?php echo $project['total_comments']; ?> comments, last comment <?php echo $project['last_comment']; ?></h4>
        
        <hr />
        
        <p><?php echo $project['preview']; ?></p>
        <?php
    }
    
    $announcements = get_announcements();
    
    foreach ($announcements as $announcement){
        ?>
        <h2><a href="announcement.php?aid=<?php echo $announcement['id']; ?>"><?php echo $announcement['title']; ?></a></h2>
        <h4>By <?php echo $announcement['user']; ?> on <?php echo $announcement['date']; ?></h4>
        <h4>(<?php echo $announcement['total_comments']; ?> comments, last comment <?php echo $announcement['last_comment']; ?></h4>
        
        <hr />
        
        <p><?php echo $announcement['preview']; ?></p>
        <?php
    }
    
    $tutorials = get_tutorials();
    
    foreach ($tutorials as $tutorial){
        ?>
        <h2><a href="tutorial.php?tid=<?php echo $tutorial['id']; ?>"><?php echo $tutorial['title']; ?></a></h2>
        <h4>By <?php echo $tutorial['user']; ?> on <?php echo $tutorial['date']; ?></h4>
        <h4>(<?php echo $tutorial['total_comments']; ?> comments, last comment <?php echo $tutorial['last_comment']; ?></h4>
        
        <hr />
        
        <p><?php echo $tutorial['preview']; ?></p>
        <?php
    }
		    
?>

But I don't want that because it would create something like

• new project
  old project
  oldest project

• new announcement
  old announcement
  oldest announcement

• new tutorial
  old tutorial
  oldest tutorial

but I just want normally sorted by date. not by date and by category. How would I do this?

You would have to combine the 3 arrays into one big array and then sort that. Would it be possible to get the three types of post in a single query though ? If you could do that you could let MySQL do the sorting. What does your table structure look like ?

Excluding user/admin tables...

• announcements
  • post_id
    post_title
    post_body
    post_user
    post_date
  announcement_comments
  • comment_id
    post_id
    comment_body
    comment_user
    comment_date
  projects
  • post_id
    post_title
    post_body
    post_user
    post_date
  project_comments
  • comment_id
    post_id
    comment_body
    comment_user
    comment_date
  tutorials
  • post_id
    post_title
    post_body
    post_user
    post_date
  tutorial_comments
  • comment_id
    post_id
    comment_body
    comment_user
    comment_date

You could put them into one table

• posts
  • post_id
    blog_id
    post_title
    post_body
    post_user
    post_date
  comments
  • comment_id
    post_id
    comment_body
    comment_user
    comment_date
  blogs
  • blog_id
    blog_name

Ugh is there any way to do this without combining everything? I've been trying to make the correct query all day and I just can't do it.

I just did what I thought I could not. I wrote my own code

<?php

function get_stream(){
$sql = "SELECT
                `tutorials`.`post_id` AS `id`,
                `tutorials`.`post_title` AS `title`,
                `tutorials`.`post_cat` AS `cat`,
                LEFT(`tutorials`.`post_body`, 512) AS `preview`,
                `tutorials`.`post_user` AS `user`,
                DATE_FORMAT(`tutorials`.`post_date`, '%m/%d/%Y %h:%i:%s %p') AS `date`,
                `tutorial_comments`.`total_comments`,
                DATE_FORMAT(`tutorial_comments`.`last_comment`, '%m/%d/%Y %h:%i:%s %p') AS `last_comment`
            FROM `tutorials`
            LEFT JOIN (
                SELECT
                    `post_id`,
                    COUNT(`comment_id`) AS `total_comments`,
                    MAX(`comment_date`) AS `last_comment`
                FROM `tutorial_comments`
                GROUP BY `post_id`
            ) AS `tutorial_comments`
            ON `tutorials`.`post_id` = `tutorial_comments`.`post_id`
            ORDER BY `tutorials`.`post_date` DESC";

$result = mysql_query($sql);

if ($result) {
   while ($row = mysql_fetch_array($result)) {
      $myarray[] = $row;
   }
}


$sql = "SELECT
                `announcements`.`post_id` AS `id`,
                `announcements`.`post_title` AS `title`,
                LEFT(`announcements`.`post_body`, 512) AS `preview`,
                `announcements`.`post_user` AS `user`,
                DATE_FORMAT(`announcements`.`post_date`, '%m/%d/%Y %h:%i:%s %p') AS `date`,
                `announcement_comments`.`total_comments`,
                `announcements`.`post_cat` AS `cat`,
                DATE_FORMAT(`announcement_comments`.`last_comment`, '%m/%d/%Y %h:%i:%s %p') AS `last_comment`
            FROM `announcements`
            LEFT JOIN (
                SELECT
                    `post_id`,
                    COUNT(`comment_id`) AS `total_comments`,
                    MAX(`comment_date`) AS `last_comment`
                FROM `announcement_comments`
                GROUP BY `post_id`
            ) AS `announcement_comments`
            ON `announcements`.`post_id` = `announcement_comments`.`post_id`
            ORDER BY `announcements`.`post_date` DESC";

$result = mysql_query($sql);

if ($result) {
   while ($row = mysql_fetch_array($result)) {
      $myarray[] = $row;
   }
}


$sql = "SELECT
                `stream_extras`.`post_id` AS `id`,
                `stream_extras`.`post_title` AS `title`,
                `stream_extras`.`post_cat` AS `cat`,
                LEFT(`stream_extras`.`post_body`, 512) AS `preview`,
                `stream_extras`.`post_user` AS `user`,
                DATE_FORMAT(`stream_extras`.`post_date`, '%m/%d/%Y %h:%i:%s %p') AS `date`,
                `stream_extras_comments`.`total_comments`,
                DATE_FORMAT(`stream_extras_comments`.`last_comment`, '%m/%d/%Y %h:%i:%s %p') AS `last_comment`
            FROM `stream_extras`
            LEFT JOIN (
                SELECT
                    `post_id`,
                    COUNT(`comment_id`) AS `total_comments`,
                    MAX(`comment_date`) AS `last_comment`
                FROM `stream_extras_comments`
                GROUP BY `post_id`
            ) AS `stream_extras_comments`
            ON `stream_extras`.`post_id` = `stream_extras_comments`.`post_id`
            ORDER BY `stream_extras`.`post_date` DESC";

$result = mysql_query($sql);

if ($result) {
   while ($row = mysql_fetch_array($result)) {
      $myarray[] = $row;
   }
}


$sql = "SELECT
                `projects`.`post_id` AS `id`,
                `projects`.`post_title` AS `title`,
                `projects`.`post_cat` AS `cat`,
                LEFT(`projects`.`post_body`, 512) AS `preview`,
                `projects`.`post_user` AS `user`,
                DATE_FORMAT(`projects`.`post_date`, '%m/%d/%Y %h:%i:%s %p') AS `date`,
                `project_comments`.`total_comments`,
                DATE_FORMAT(`project_comments`.`last_comment`, '%m/%d/%Y %h:%i:%s %p') AS `last_comment`
            FROM `projects`
            LEFT JOIN (
                SELECT
                    `post_id`,
                    COUNT(`comment_id`) AS `total_comments`,
                    MAX(`comment_date`) AS `last_comment`
                FROM `project_comments`
                GROUP BY `post_id`
            ) AS `project_comments`
            ON `projects`.`post_id` = `project_comments`.`post_id`
            ORDER BY `projects`.`post_date` DESC";

$result = mysql_query($sql);

if ($result) {
   while ($row = mysql_fetch_array($result)) {
      $myarray[] = $row;    
   }
}

function cmp($a, $b)
{
    if ($a['date'] == $b['date']) {
        return 0;
    }
    return ($a['date'] > $b['date']) ? -1 : 1;
}

usort($myarray  , "cmp");

return $myarray;

}

?>

Yeah just doing three queries is the other way to do it