How do the revisions work in Wordpress?

I created a web service to pull the database info to out put to an XML file. I don't understand how to pull the latest revision with the original post info (author, date published etc.)

I have attached my SQL query.
thsotoAsked:
Who is Participating?
 
DzynitConnect With a Mentor Commented:
When revision save in the database, the post_name is where to look. So I suppose you could set up your query to first get the original post - for the example lets say that is ID=101

Each revision will save a post name like this:
101-revision
101-revision-2
101-revision-3
etc.

So after you get the info you need from the original, you could run a query on the post_name first to find the latest revision, then to get the info you want from it.

You would need a do until type statement to get the latest revision because I don't see that the original stores anything to say which is the latest revision.


I'm not completely sure I understand why you're needing to do this. I'm wondering if you misunderstand how the revisions work. The revisions are always the previous version each time you update a page. The latest edit will actually be the original. And the author ID of the page/post will not change from the original author unless you manually specify it to.

Please let me know if this helps or if I'm not answering your question right.
0
 
gwkgCommented:
You didn't attach your query.

'revision' is considered a post type so maybe use 'post_type' = 'post' in your WHERE clause.

0
 
thsotoAuthor Commented:
I actually meant to delete the query...just forgot to delete that sentence.

I think WP is using some sort of logic to pull certain info from the original post and pull edits from the revision.

Example:

You (author=1) write an article and it is ID=101, post_author=1, post_status=post, post_name=my_title, post_content="This has a typo". The URL would be www.myurl.com/?p=101

Next, I (author=2) go in and edit your post because of a typo. my entry is now post ID=102, post_author=2, post_status=revision, post_name=101-revision, post_content="This has no typo".

My SQL query needs to pull post_author from ID=101 (the original) and post_title and post_content from ID=102 (the revision)

Does that make sense?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
thsotoAuthor Commented:
I need the most up-to-date post.

Are you saying that the post-type=post and post-name=my_title, which would have the original post ID, is the most up-to-date edit?

0
 
DzynitCommented:
Yes, that's correct. The revisions mainly work as a backup copy of before your edit in case a mistake is made - you can bring the prior version back.
0
 
thsotoAuthor Commented:
OK.... I thought the original post was pointing to the revisions; therefore pulling some info from the original and some from the revisions.

From what you are saying, the original post gets republished to the database as "revision-1" and the new edit replaces the the original post as the original ID.
0
 
DzynitCommented:
Correct. But each revision increases a number - so the newest revision would be postid-revision-highestnumber.

In a way the revisions are just space takers in the db, but they do at times come in handy if someone accidentally over-writes a paragraph or something. You have a backup copy and wordpress also auto-saves every so many minutes. Which is also nice if for example your browser crashed in the middle of a long article you might be typing.

But always the original post with the post name is the freshest and most recent edit.
0
 
thsotoAuthor Commented:
Thanks for clearing that up for me. Apparently, I was doing everything right... I just didn't understand how WP was working in the background. I'll run a few more tests on Monday when I get back to work before I close this question. It appears everything is working correctly but I need to make a final test of it.
0
 
DzynitCommented:
No problem and good luck. Glad it helped.
0
 
thsotoAuthor Commented:
Here is my sql statement that I used in case someone needs more info.

Thanks for explaining things to me.

SELECT * FROM (SELECT DISTINCT (p.post_title), p.ID AS ID, p.post_date, p.post_name, p.post_status, u.display_name FROM `wp_posts` p, `wp_term_relationships` tr, `wp_users` u WHERE p.ID = tr.object_ID AND p.post_type LIKE 'post' AND p.post_author = u.ID ORDER BY p.post_date DESC LIMIT 0,20) AS temp ORDER BY temp.post_date DESC

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.