Displaying latest date issue

genista

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:

   $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:

$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:

$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 looked at putting the first set of results into an array and then foreach content_id getting the latest post, however I feel I will still be in the same position - I hope this can be resolved with a tweak to a query?

Thanks for your help in advance!

dalecosp

item_date is what datatype?

genista

Hi Dalescosp,

It is a DATE column.

Thanks,

dalecosp

Thanks for the reply. I suppose something as simple as ORDER BY DATE DESC LIMIT 1 doesn't help?

genista

dalecosp;11038659 wrote:
Thanks for the reply. I suppose something as simple as ORDER BY DATE DESC LIMIT 1 doesn't help?

Sadly not, its not even ordering them by date. Here is the full code before I start printing the results:

    $query="SELECT content_id, title, source_image, url FROM content_detail where approved='y'";
    $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'];



$query2 = "SELECT item_id, item_title, MAX(item_date) as item_date from rssingest WHERE content_id='$content_id' ORDER BY item_date DESC LIMIT 1";
$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'];

Thanks,

cretaceous

What can help here is creating a temporary table with the max date for each author. Then join or left join to that in your main query.
I had some like this and as well as removing the filtering and ordering difficulties, it changed what had become a slow query (on several thousand items that had to join to themselves and group on counts) to a blisteringly fast query.

dalecosp

cretaceous;11038685 wrote:
What can help here is creating a temporary table with the max date for each author. Then join or left join to that in your main query.
I had some like this and as well as removing the filtering and ordering difficulties, it changed what had become a slow query (on several thousand items that had to join to themselves and group on counts) to a blisteringly fast query.

Would a VIEW also work for that?

cretaceous

dalecosp;11038711 wrote:
Would a VIEW also work for that?

You got me there.. never used VIEW, but after a quick read up they look like they'd do the job just the same and are apparently slightly faster.
I'm always well behind the curve...
(I do think a lot of the complex sub selects I see asked about would perform much better this way - if you want 'order by' on a derived calculation this is the fix)

Weedpacket

Wouldn't MySQL at least consider constructing a temporary table to be an option when planning a subselect (I recall its explainer sometimes saying something like "using filesort; using temp")? If so, then an explicit temp table wouldn't be necessary. One thing is probably: unless you want the results of the outer query for something other than using their values to fill in parts of the inner query, there should be no need to run the inner query in a loop driven by the results of the outer query.

As for selecting the items that are the latest by each author - something like (and I'm guessing and leaving bits out because I haven't quite inferred from the admittedly incorrect queries shown just which tables contain what)...?

Select content_id from ??? t1 where item_date in (
    Select MAX(item_date) from ??? t2 where t1.author=t2.author group by author)

cretaceous

Mysql might do that but with judicious planning the temp table can be a much smaller subset of the data - of course every set of data and project has very different requirements.. but when Mysql does it all in one query on large data sets with several joins it can really bog down.
(the two situations I used it on with most effect both joined one table back to itself with two categories of user and their relationships - and counts of those with ordering)
I know the web often states 'Using filesort' is sometimes the straw to break the camel's hump, though of itself I've not noticed an issue...