CMS - additional form elements.

[JiminSA]

Inevitably regardless of time spent designing a database, the client (or circumstance) will find data they want to be added into to the existing database.

This would necessitate an upgrade to the db (i.e. read in current format and write out new format)

Are there any guidelines available to achieve this (MySql / .dat files)?

[thruska]

This blog article I recently wrote may help:

http://cubicspot.blogspot.com/2012/05/cr...abase.html

It is a fantastic solution to a very common problem: Managing field change requirements in a database without having to refactor a ton of code and reduces the risk of breaking the system.

[JiminSA]

Ah beautiful - I love the non-conformist, innovative approach and see from your excellent article, the ease with which I can introduce new fields! Thanks for your insight, my friend!
Incidentally, I did not receive email notification of your responses to my last 2 threads!?

[thruska]

Glad it helped. No idea why e-mail notifications didn't work for you - e-mail is flaky like that.

[JiminSA]

Sorry to bug you Thomas, but do you have a working example of a serialised file add-on/merge? I need to append a field to an existing .dat file to handle photos. (Yes, I have become a serialise/un-serialise file fan and am using it in my day-to-day applications as a great alternative to SQL.)

[thruska]

SQL has its advantages: If you need to search data or quickly identify a row to make changes to, SQL with serialize() is a pretty sweet combo. The data that doesn't need to be indexed goes into the serialized array.

I generally use a database when there are rows of data. For me, serialized data on the file system is rare - restricted to global configuration data or apps where I don't want to require a database (e.g. Barebones CMS).

In order to make the arrays I store in a serialized format more flexible, I store the data such that I can expand or eliminate options later using key-value pairs. I add new options easily by using a function to load the data and calling that function instead of unserialize() directly. The function checks to see if each option exists and, if not, sets a default value. The caller is then guaranteed that each option exists even if the original data doesn't have it. I can add new options without having to go back and change all the rows in the database that already exist. The same concept applies to removing options - the data is left untouched and never deleted if, for some reason, I decide I really did want the option.

For your scenario, if I were using a database, one of the columns would have an auto_increment field for an integer ID. If there would only ever be one photo, the quick solution would be to put the photos in a directory called 'photos' and name the photo 'ID_photo.jpg' or similar - uncreative, but it would work. It is important to delete photos if the database row is removed (unlink() function). If there can be multiple photos, it is easier to create a directory for the photos and then track the last photo number used in the serialized array for the item. Files would then be stored as 'ID_#_photo.jpg'. Deletion gets a little more complicated.

The above can be applied to PHP arrays but requires more lines of code. The unique ID is the key of the item in the array.

[JiminSA]

Thank you Thomas, that was very helpful.

Incidentally I manually checked this thread to find your answer as no notification was received via email...