PHP Article Versioning

[Vortex566]

Okay the problem is as follows. I need to implement article versioning on a site I am building. I need the latest article to also be the one shown on the main page but I also need to be able to view all the old versions of an article.

I have started by always inserting articles into the articles table and never updating them. I have also so made it so the first time I have inserted a new article the version_id of that article becomes equal to the article_id.

I am a bit stuck on what to do now mind. I do understand that every time an insert is done on an existing article I need to make sure that the version_id becomes equal to one.

I am open to any ideas.

[sweetsam]

XML files would be perfect for this. You could allocate one file for all versions of an article and have version numbers and store the latest version number in the row corresponding to the article. That way you will be able to access the most recent version of the article. As you make revisions you can keep adding on new version to the xml file.

[Vortex566]

Thanks for the advice but I need to do it using an SQL database and PHP. Does anyone have any other ideas on how I can solve this?

[+SOOPRcow MVC]

create a related_to (or something) field with the id of the original article in the article table.

then when doing the select for the listing use the DISTINCT keyword for the related_to field. Just make sure you're sorting by the newest first.

I think that would give you what you want.

Hmm, the more I think about it Original articles would have to have the same value in there related_to and id field.

[boogerjones]

Like +SOOPRcow said, this is actually quite easy to do. You simply do the following:

1. Your articles table should have the following fields included: article_id (Primary Key, Integer), version_number (Integer), version_uid (This is the "related_to" field; it will be the same for all versions of the same article; it simply has to be some unique number or text), and modified_time (DATETIME field)

2. When you create an article, you assign it an article_id, version_number, and version_uid

3. When you edit an article, you create a new article record with a new article_id, you increment the version_number, and then assign the same version_uid as the original article.

[Vortex566]

Thanks, that looks spot on to what I am want. This should make a rollback to an older version easy as well.

[boogerjones]

Just as a caveat: in an ideal relational database system, this would be done with at least 2 tables. One table (articles) would contain the article_id and article_unique_id and other information (such as a BOOLEAN field signaling whether an article is to be displayed or not) while the other table (article_versions) would contain all of the actual article information. The tables would be related to each other by article_unique_id in a one-to-many relationship.

[Vortex566]

Yeah, that is true. However this is for some work at university, we are not going to be marked on the design of the tables. You raise a good point mind and I have taken it on board.

[Vortex566]

One more thing before I go to sleep. I want the index of the site to always display the latest version of all the articles in the database.

Anyone have any ideas on how to do this?

[boogerjones]

This will get the most recent articles by date:

SELECT * FROM articles GROUP BY article_unique_id ORDER BY modified_date DESC

This will get the most recent articles by version:

SELECT * FROM articles GROUP BY article_unique_id ORDER BY version DESC

If that doesn't do what you want, then try this:

SELECT * FROM (SELECT * FROM articles ORDER BY version) GROUP BY article_unique_id

Edited March 31, 2009 by boogerjones

[Vortex566]

It worked great, once again thank you for the input.