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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
WordPress

From novice to tech pro — start learning today.