Author Topic: PK's MovieDB dynamic website template  (Read 43635 times)

0 Members and 1 Guest are viewing this topic.

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
PK's MovieDB dynamic website template
« on: October 08, 2008, 09:06:39 am »
Hi,
I managed to write an sql export template for PVD, which seems to work fine for my use.
The php website template I'm using is a modified version of PK's MovieDB, originally written for Ant's Movie Catalog. Since AMC and PVD use similar fields, I had no problem filling the corresponding fields.

I had a few minor issues:
- %value=43 (Audio Codec) returns nothing, I reckon this is a bug in PVD
- Not sure how to replace existing ['] (as in won't) with [''] in options (this is necessary since 's are used as value delimiters in a sql script).
When putting replace="'<->''" in options I end up with four ''''. It is easily fixed using search/replace though.


Compared to the original unmodified template for PK's MovieDB I use more fields (technical details mostly). Imdb ratings are also handled differently. I use the ripper's tag for the Media Label field (showing as "Version" on the website). I also don't use large images, so I took out some code to resize images.


Here is my database as exported from PVD:
http://blue334.awardspace.com

If you hit a page that throws you out, that just means it contains a word in the host's filter list. I'm trying to work out a complete list of filtered words.Here is my database as exported from PVD:
http://blue334.awardspace.com

If you hit a page that throws you out, that just means it contains a word in the host's filter list. I'm trying to work out a complete list of filtered words.



Quote
PK's MovieBD dynamic website template.
Modified and adapted for Personal Video Database (PVD).


Date 2009-05-31


1.  Unpack the first .zip file to an empty folder

2.  Register a website at www.byethost.com or other free host.

3.  Create a database "yourdomain_movies" using the management tools for the site.
    Log into phpmyadmin. Run the SQL query "fields.sql" (included in the secon zip file)
    to create a table "movies" with the necessary fields. Check that the structure looks
   ok afterwards. Take note of servername, your credentials, the database name and the table name.
   
4.  Edit mdb_config.inc in the first file with the database values.

5.  Log on to the domain with an ftp manager.

6.  Where the dummy index.html file resides, upload contents
    of the first zip file (including the modified mdb_config.inc)

7.  Check that you can access your domain http://yourdomain.blahblah.com/index.php

8.  On your local computer, create a directory for export.
    Create a subdirectory "images" for pictures export.

9.  PVD uses system settings to format dates. The website template assumes sortable
    date format YYYY-MM-DD for date entries. If your regional settings are
    different, change them in Control Panel>Regional Settings temporarily.

    An alternative method to fix wrongly formatted dates is regular expressions.
   Notepad++ supports search-replace using regexp.
   Example to convert dates formatted as 26.06.2007 to 2007-06-26:
   Find ([^0-9])([0123][0-9])\.([01][0-9])\.([1-2][0-9][0-9][0-9])([^0-9])
    Replace with \1\4-\3-\2\5
   The regexp method applies to the finished output file in [11]
   
10. Start PVD

11. Export database using the sql export template pk_sql.ptm (put this and all other
    files with .ptm extension in \Personal Video Database\Plugins\Templates first).
    Export to the directory you created in [7].

12. Open the file in notepad++ or similar editor that will save UTF-8 NO BOM format.

13. Search-Replace all instances of [''''] with ['']

14. Search-Remove all instances of [images/]

15. If you have poster images of multiple formats, search-replace all
    non-jpg extensions in the textfile to .jpg (e.g. .png, .gif, .jpeg).

16. Format file as UTF-8 NO BOM and save.

17. Resize/convert images to jpg W=200px using a batch tool (e.g. Xnview, Faststone)

18. Log on to phpmyadmin again.

19. Import the sql script into the
    database.

20. Upload the resized images to the resource folder using your ftp file manager.

21. Run http://yourdomain.blahblah.com/processperiod.php

22. Enter website and you're done. Ask me if there are problems.


*MORE*

UPDATING THE SITE
When updating drop the entire table in phpmyadmin and run the fields.sql query again.

ENABLING THE "PERIOD" SIDE PANEL.
The Period panel groups years into periods, which is much better than individual years if you
have many older movies. This requires a separate php script to be run AFTER you're
done uploading your database, ref. [21] above.

If you perform an update on your site you must run processperiod.php again.
Thanks to ty3001 at the AMC forums for the processperiod.php script.

PERSONALIZATION
You probably want to change the logo. It is located in templates\default\images
To change the color scheme, just edit the stylesheet. Some gif files
in the logo folder will have to be modified as well.

BEFORE YOU START
This is a basic step-by-step method for getting your database online.
A few things will not work out of the box, like custom fields.
The example files require the presence of a custom field "Ripper" which must be edited out.
It has been kept in as an example only.
The example also require unique indexes for each movie entry. It is possible to work around this.


All required files uploaded 2009-05-31:
http://www.mediafire.com/file/mdmzyztym4n/pkdb for 0.9.9.8 - website files.zip
http://www.mediafire.com/file/z2dmwgmmhjg/templates -  sql scripts.zip

Example site in my sig.
« Last Edit: May 31, 2009, 09:35:23 pm by blue334 »
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: Sql dump from PVD
« Reply #1 on: October 08, 2008, 10:45:57 am »
Quote
Here is my database as exported from PVD

Wow! That's impressive. :o  I don't know anything about setting up websites, but I'll ask what others are bound to... Are you able to share the website template? And with that, would a novice be able to do this? This would be a great solution for those who have asked how to display their database on a website.

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #2 on: October 08, 2008, 04:47:50 pm »
A novice with a little bit of patience can do this, that's who I am. It is not really difficult, but since a database backend is required, there are a number of possible problems if you're new to it (sql scripts being rejected for various reasons, codepage issues are the most troublesome)

I tried a couple of other free hosts without success before awardspace. You may want to read the thread over at the AMC forum to see what hosts people are using.

I'll gladly make available my modified template, but be aware that I'm no php programmer and what changes I've made are ad hoc, ugly and undocumented.

There are other dynamic website templates available for AMC, my sql template is proof of concept that these should also work with PVB
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #3 on: October 08, 2008, 07:44:44 pm »
Deleted.

See later post.
« Last Edit: October 18, 2008, 08:22:03 pm by blue334 »
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline nostra

  • Administrator
  • *****
  • Posts: 2852
    • View Profile
    • Personal Video Database
Re: Sql dump from PVD
« Reply #4 on: October 08, 2008, 10:41:36 pm »
Very cool, blue. Thanks for your contribution.
I'll consider adding the export template and maybe some additional automation to make the process easier.
Gentlemen, you can’t fight in here! This is the War Room!

Offline deazo

  • Power User
  • ****
  • Posts: 278
    • View Profile
Re: Sql dump from PVD
« Reply #5 on: October 09, 2008, 10:01:59 pm »

 WOW that is exactly what I needed, thank you so much!
 I will not be able to try that for a while because I am away from home for a while, but I'll definitely give it a shot when I get home.
 I have noticed you did not include the tags/keywords in your database, is this a choice or a limitation?
 Keywords are very important to me, as they can help me find movies within a specific theme.
 Thanks again!

 D.

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #6 on: October 10, 2008, 08:25:50 am »
The reason for missing keywords is that AMC doesn't use/import them, so they were never part of the original template. I could try to add them, shouldn't be difficult.
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #7 on: October 10, 2008, 01:04:25 pm »
I managed to add the tags, but unfortunately they choke the server. Too much information to handle for the php parser presumably. They may work with smaller databases than mine, or more powerful webservers.
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: Sql dump from PVD
« Reply #8 on: October 10, 2008, 08:14:23 pm »
I'm not about to use this myself, but it's just too cool not to comment on...

If you were to add user rating and comments, people could use this to publish their own opinions and recommendations. They could be presented as a separate view, similar or identical to detail view, with comments instead of description and user rating instead of, or in addition to the IMDb rating.

Data added is a sort option, but it doesn't seem to appear anywhere. If user ratings and comments were included, this (or perhaps better, date viewed) would be useful. Return visitors, for example, could then easily identify movies most recently added/viewed/commented on.

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #9 on: October 11, 2008, 08:24:15 pm »
I'll consider what you suggest, seems like good ideas. The existing comment field would be impractical, since that is already used in a default imdb import. A custom field would be better, provided it can be exported by the export plugin.
Date viewed could also be made a custom field I suppose, if it can be exported it could be grouped in a separate section with user rating and comments.
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline nostra

  • Administrator
  • *****
  • Posts: 2852
    • View Profile
    • Personal Video Database
Re: Sql dump from PVD
« Reply #10 on: October 11, 2008, 08:59:55 pm »
Quote
A custom field would be better, provided it can be exported by the export plugin.

All fields can be exported

Quote
Date viewed could also be made a custom field

What for? There is already a native date viewed field in PVD.
Gentlemen, you can’t fight in here! This is the War Room!

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #11 on: October 11, 2008, 09:41:00 pm »
Indeed there is.. hadn't noticed since I'm using that field as a generic checkbox for other purposes. My movies are batch-added as seen by default, but that doesn't set a date value. Then I tick them as unseen when I'm done adding the media info.
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: Sql dump from PVD
« Reply #12 on: October 11, 2008, 10:31:04 pm »
Quote
The existing comment field would be impractical, since that is already used in a default imdb import.

Not exactly. There's an option to save the IMDb description in Comments, rather than Description (in case one is getting their description from elsewhere). But that same option can be used to put the description in a custom field. So I think it's "fair" to assume Comments is being used for user comments of some sort. And it was probably it's original intended purpose.

Quote
Date viewed...could be grouped in a separate section with user rating and comments.

Yes. I think it would make good design sense to keep all the user data together and separate from the other movie information.

Please remember I'm just offering suggestions for what might be a reasonable generic template. I assume that's what you want to do. From there, you can advise users generally how to make their own modifications.

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #13 on: October 11, 2008, 10:48:31 pm »
You're right about comments. The reason I thought they were imported is that my database is full of imdb user comments in that field, but those entries (about 70% of my database) come from AMC import. I hadn't really noticed the difference.
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #14 on: October 14, 2008, 09:44:05 pm »
Added the suggested fields in a separate section, no custom fields had to be used fortunately. Further comments and suggestions welcome. Will tidy up code and readme file before I upload a new set of files.

http://blue334.awardspace.com/ as before. You will notice I don't use date viewed, user rating or personal comments myself (yet).

One question: is it possible (by some magic) to name poster images by movie id instead of number increment? This would make it easier to do smaller updates to the website, because image paths would be statically linked with an entry in PVD. If I now delete an entry in PVD I get new links to most or all images, meaning I have to upload all 3,000+ again.

EDIT: Notice the new "Sort by" options in the pulldown menu.
« Last Edit: October 14, 2008, 09:46:19 pm by blue334 »
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: Sql dump from PVD
« Reply #15 on: October 15, 2008, 02:02:39 am »
Looks good. Nice having all those sort options too.

I notice for many movies the actors are sorted backwards (i.e., from their IMDb credits order) so the lead actors/stars do not appear as stars in the summary.

This post describes my method of renaming poster files. It's probably not as convenient as the magic you're looking for, but you could adapt it to get the job done.

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #16 on: October 15, 2008, 06:47:43 pm »
The credits sorting is a problem with my database, not with the template. It was probably caused by the AMC import plugin (only imported records have the problem).

I suppose your method would make it possible to rename image files, but it may not be worth the hassle in my case. Uploading the full set of image files with every update may be time-consuming, but fortunately only for my ftp client.
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline blue334

  • User
  • ***
  • Posts: 100
    • View Profile
Re: Sql dump from PVD
« Reply #17 on: October 16, 2008, 02:26:00 pm »
Another question: I mentioned in my top post that my free host awardspace.com have a list of filtered words/phrases. If a page rendered contain any of these words/phrases, you are redirected to their homepage. This is quite annoying, but I can understand why a free service has to discourage commercial/phishing/porn sites on their servers. Unfortunately, in imdb movie descriptions these words and phrases will pop up occasionally.

Several of these words/phrases are known, and I want to make automatic string replacement of filtered words an optional addition to the export template. The only problem is the replace function is case sensitive, meaning replace="fake id<->f ake id" works, but not if it contains uppercase (Fake ID, Fake id, Fake Id etc.).

Provided it is possible to change this, would it matter to other uses of the replace function if it was made case insensitive?
Check out PKs MovieDB dynamic web template - exported from PVD: My site

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: Sql dump from PVD
« Reply #18 on: October 16, 2008, 09:02:56 pm »
I have no idea, but I imagine this function ultimately uses the Firebird SQL equivalent which happens to be case sensitive. I recall having a similar question... Perhaps nostra will answer both by explaining how replace works. My interest, generally, is using it to "clean up" imported data (by exporting and re-importing)—but replace doesn't help much with these limitations.

Offline nostra

  • Administrator
  • *****
  • Posts: 2852
    • View Profile
    • Personal Video Database
Re: Sql dump from PVD
« Reply #19 on: October 17, 2008, 01:12:45 am »
The export plugin just receives data from the main application and puts this data into a file replacing variables in templates, so it has nothing to do with SQL.
The replace function is indeed case sensitive now, but I can change this behavior easily.
Gentlemen, you can’t fight in here! This is the War Room!