Combine 3 'blogs'

[FrederickGeek8]

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:
[syntax=php]<?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
}

?>[/syntax]

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?

[jacek]

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]

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

[jacek]

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]

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]

I just did what I thought I could not. I wrote my own code
[syntax=php]<?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;

}

?>[/syntax]

[jacek]

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