Combine 3 'blogs'

FrederickGeek8 · Post by **FrederickGeek8** » Fri Jul 27, 2012 6:03 pm

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?

Post by **jacek** » Sun Jul 29, 2012 1:55 am

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 ?

FrederickGeek8 · Post by **FrederickGeek8** » Tue Jul 31, 2012 4:57 am

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

Post by **jacek** » Fri Aug 03, 2012 10:28 pm

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

FrederickGeek8 · Post by **FrederickGeek8** » Sat Aug 04, 2012 1:20 am

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.

FrederickGeek8 · Post by **FrederickGeek8** » Sat Aug 04, 2012 5:24 am

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;

}

?>

Post by **jacek** » Mon Aug 06, 2012 12:00 pm

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

BetterPHP

Combine 3 'blogs'

Combine 3 'blogs'

Re: Combine 3 'blogs'

Re: Combine 3 'blogs'

Re: Combine 3 'blogs'

Re: Combine 3 'blogs'

Re: Combine 3 'blogs'

Re: Combine 3 'blogs'