Get Rows in Order of Added, but then get the most recently updated (datetime) of each set

Databases
1

I have a dynamic Sitemap creator using PHP. I want to have the tag on the parent sitemap index for each sitemap.

$sitemapcount = ceil($totalvids/4000);

And that’s it, but I want to get the most recently modified of each 4000 rows (which are being delivered by video_id DESC).

//this is a typical call to fill a sitemap page (there is more, I just slimmed it)
$x = $_GET['x'];
$limit = ($x - 1) * 4000;
$round1 = mysql_query("SELECT v.video_id, v.modified_date  FROM v ORDER BY v.video_id DESC LIMIT $limit, 4000",$db);

I’m trying to think of the most efficient way of getting the most recently modified from every chunk of 4000 rows added.

So, if there are 20000 rows, i first grab row primary ids 20000 to 16001, and then rows 16000 to 12001, and then rows 12000 to 8001 and so on. And, of each of those blocks of rows, I only need the row with the most recent date modified.

Is there a way to do this in a single query?

(i hope I’m explaining it right)

Cheers!
Ryan

2

any query requiring “most recent” should never use the auto_increment column, but a proper datetime column

3

Understood, I have both.

Why I need to use both is this.

In the upper most level of Sitemap you can tell google the last time a particular sitemap was modified.

In my sitemap master: http://www.traileraddict.com/sitemaps/maps.xml

<sitemap>
<loc>http://www.traileraddict.com/sitemaps/trailers/22.xml</loc>
<lastmod>2016-01-13</lastmod>
</sitemap>
<sitemap>
<loc>http://www.traileraddict.com/sitemaps/trailers/23.xml</loc>
<lastmod>2016-01-13</lastmod>
</sitemap>

The master sitemap is creating a new page of videos for every 4000 there are, but it needs to keep them in order of added (so primary_id is “video_id”)

Since I’m creating the above XML dynamically I was hoping there was a way to find the most recent modified/added post on each sitemaps trailer page (above is page 22 and page 23), so I can use that same date for lastmod of the entire sitemap page.

Or, to put it another way, /sitemaps/trailers/1.xml covers the most recent 4000 rows added to our database, and I need the row with the most recent datetime column to use that same datetime to create a “lastmod” date for the sitemap itself.

Cheers
Ryan

4

please, do not rely on an auto_increment to maintain order added sequence

it will bite you some day

use a date_added DATETIME column instead

[quote=“casbboy, post:3, topic:214053, full:true”]Since I’m creating the above XML dynamically I was hoping there was a way to find the most recent modified/added post on each sitemaps trailer page (above is page 22 and page 23), so I can use that same date for lastmod of the entire sitemap page.

Or, to put it another way, /sitemaps/trailers/1.xml covers the most recent 4000 rows added to our database, and I need the row with the most recent datetime column to use that same datetime to create a “lastmod” date for the sitemap itself.[/quote]

maybe someone else can step in here, because i still don’t understand this

5

That involves using the information returned from the database.

The first step is to make sure the database contains the information you want to use.
Then to make sure the query returns what you want it to.

Although it is good to think ahead a bit so you have an idea of the “big picture” don’t get too into what you’re going to do with the data until you know you can get it.

In this case it’s the modified_date field you’re waning to get.

An ORDER BY isn’t working?

6

Sorry for delay. I was never alerted to a response by email. Let me try to describe it better.

I use PHP to make a master sitemap.xml file, which creates a number of trailers-1.xml, trailers-2.xml files until it covers all the rows. I basically divide the total rows by 4000, and it runs a loop on the number it comes back with to output the right amount of sitemap files, each with 4000 rows, except for the last one.

http://www.traileraddict.com/sitemaps/trailers/1.xml http://www.traileraddict.com/sitemaps/trailers/2.xml http://www.traileraddict.com/sitemaps/trailers/3.xml http://www.traileraddict.com/sitemaps/trailers/4.xml http://www.traileraddict.com/sitemaps/trailers/5.xml

It’s all great, and Google checks this sitemap file every couple of minutes. Now, I want to add the tag to the master sitemap file. The date should be the date of the most recent that will be inside the site map. so…

<sitemap>
<loc>http://www.traileraddict.com/sitemaps/trailers/22.xml</loc>
<lastmod>2016-02-09</lastmod>
</sitemap>

This sitemap has rows that were added years ago (about 88,000 rows back), since the sitemaps are in order of the ID of when they were first added or of the date they were first added, not when they were updated. But one particular row got updated with new information/content, so somewhere inside that sitemap above there is a row that has:

<url>
<loc>http://www.traileraddict.com/the-other-guys/san-diego-comic-con-panel-eva-mendes</loc>
<lastmod>2016-02-09T07:31:40-07:00</lastmod>
<changefreq>yearly</changefreq>
<priority>0.6</priority>
</url>

So though most the rows are years old, this sitemap has a row that was updated on February 9th, so it’s the most recently updated of this entire sitemap. Since the most recently modified in the sitemap is the lastmod date of the entire sitemap, I’d like that to reflect.

I know the datemod is not exactly necessary, but would be cool if there was a way to pull it off.

I’m hoping that makes sense. The rows inside the sitemaps are in ORDER of when first added, but I need the date reflected of the last modified.

Cheers!
Ryan

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.