All,

I am trying to display the latest post from my authors, the posts are in one table, the author in another. The queries I have join on a column called content_id, and I am trying to work with MAX(item_date) which is the date the post was published.

Here are some queries I am running and the output, I seem to be getting everything except the latest posts from all authors:

First (and this bit is working) we need to get information about the author:

PHP Code:
$query="SELECT content_id, title, source_image, url FROM content_detail where approved='y'"
//$query="SELECT content_id, title, source_image, city, state, country, url, type 
    
$result1 $mysqli->query($query) or die($mysqli->error.__LINE__); 

// GOING THROUGH THE DATA 
 
if($result1->num_rows 0) { 
     

while(
$fetch=mysqli_fetch_array($result1)) { 
$title=$fetch['title']; 
$feed_rss=$fetch['url']; 
$content_id=$fetch['content_id']; 
$source_image=$fetch['source_image']; 
Now on that basis lets get the posts, round 1:

PHP Code:
$query2 "SELECT item_id, item_title, MAX(item_date) as item_date from posts WHERE content_id='$content_id' GROUP BY content_id"
$result2 $mysqli->query($query2) or die($mysqli->error.__LINE__); 

// GOING THROUGH THE DATA 
 
if($result2->num_rows 0) { 


while(
$rows2=mysqli_fetch_array($result2)) {   


$item_id $rows2['item_id']; 
$item_title $rows2['item_title']; 
$item_date $rows2['item_date']; 
This pulls back an in interesting set of results, the latest date of a post from an author, but not the latest title!

I have tried GROUP BY item_title and content_id to no avail.

The following pulls back just records that have more than one date in the database, the problem is (as this is new) some authors only have one post, these are not being displayed:

PHP Code:
$query2 "SELECT item_id, item_title, item_date FROM posts AS a WHERE content_id = $content_id AND item_date = ( 
    SELECT MAX(item_date) 
    FROM rssingest AS b 
)  
"


I have tried:

[php]
ORDER BY DATE DESC LIMIT 1
[/php

at the end of my queries to no avail.

item_date is a DATE type in the table.

Any help would be greatly appreciated!

Thanks,

G