PVD .9.9.21 Website Template Easy to Web scripts based on PK Template

[devilingrey]

This is an updated version of PK's web export tailored to PVD .9.9.21 with an easy to use tutorial.  I've updated some of the bugs from the last posting of this in 2009 and will continue to keep fixing bugs as time allows.  Hope you find this helpful.

I updated the sorting meathods so you can do multiple sorting, and click out of any of the sort options.

version 0.0.1 for PVD .9.9.21
date: 7.19.11

Pre-Setup

1. Download webscripts.0.0.1.7z from the forum
   a. http://www.mediafire.com/?50nd05493cddss6
   
2. Download SaxonHE
   a. http://saxon.sourceforge.net/#F9.3HE
   
3. Move ‘PVD to Web.ptm’ into “/Personal Video Database/plugins/templates/”

4. Move ‘PVD_Movies.xsl’ into “/SaxonHE9.3/bin/”

Pre-Website Setup

1. Download My version of PK’s MovieDB.7z from
   a. http://www.mediafire.com/?ve86efm21395z4d
   b. Modify Config Variables in mdb_config.inc
2. Copy the folder movies from movie_db_files.7z’ up to your server
   a. Verify you can get their by accessing the movies folder from a web browser
3. You will need a  database backend (phpmyadmin works great)
   a. Make sure you can login to your mysql backend
4. Make sure you can access the folder on your website with ftp/scp client (WinSCP)
5. Create a database (mine was xx_movies).  You can leave it empty

Export xml

1. Launch PVD, Open your database, make sure all movies visible.
Note: 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.
2. Click Export, run ‘PVD to Web’
3. Select folder “/SaxonHE9.3/bin/”
4. Save filename as “movies.xml”

Run XSL Transform Script

Note: If you have older imdb data, you will have this garbage in the tagline fields of your data, simply replace "<div class="info-content">\r\n" with "" in movies.xml using your favorite txt ediror (notepad++)

1. Double Click saxon/bin/Transform.bat
   a. “This creates the PVD_Movies_Import.sql” file

Upload SQL file to your server

1. Upload “PVD_Movies_Import.sql” to your mysql database
2. Verify your sql content looks correct in the mysql database
3. Copy over images folder from /bin/images/ to /antexport/images/ on your remote installation server
Note: If you have run this before, make sure you clear the cache folder on the server!
Cache folder is in the main movies/cache/ directory
4. Run processperiod.php script file at http://yourwebsite.com/moviesfolderroot/processperiod.php
5. Goto Your Site!
   a. http://yourwebsite.com/movies/

Note: If you sort your movies by folders for quality type (i.e. 480p, 720p, 1080p) you can use the added feature of sorting by labeling your movies in PVD.
1. Open PVD, filter by 'path', type in 480p,
2. Select all movies and do a multiple file edit
3. Under Quality->Set to '480p'
4. Repeat for each movie type
Run/rerun .xml export


[attachment deleted by admin]

[koliniol]

Hi there
I tried to Use your Template but cannot find Transform.bat.

I currently using the
PK's MovieDB dynamic website template http://www.videodb.info/forum_en/index.php/topic,1029.0.html

my site is
digitalmovies.ekoliniol.com

[devilingrey]

Hey,

Here is what is in Transform.bat

Transform.exe -t -s:movies.xml -xsl:PVD_Movies.xsl -o:PVD_Movies_Import.sql

Just create a .bat file and put that in there.

[koliniol]

thanks a lot
I tried it but the sql file only contains the create table command and not the actual data to be inserted
see attached file
any ideas ?


[attachment deleted by admin]

[devilingrey]

The .sql file that came with it is there for place holder only.  Use the PVD export to sql I wrote to generate your .xml file and images folder.  Put them into the saxonh folder where the .xsl file is and the transform.bat file is.  then you should be able to run it successfully.

[koliniol]

I'm doing exactly what you said and it only produces the PVD_Movies_Import.sql that i sent in previous post.
Could it be somethig wrong with the PVD_Movies.xsl file
It looks as if it only reads up to line 15 that produces the Create Table command.

[botez]

I was following along here and noticed the same issues that koliniol posted previously.  I fixed it by removing the <viddb> and </viddb> tags from the exported xml file. Similarly, you could just remove those from the export script so that they aren't inserted when you export the XML file from PVD.

[b_sleeth]

Thank you so much for this updated web export.  After installing the update, I came across a few things

In the list_view.tpl needed to change $LANG[RATING] to $LANG[IMDBRATING]

Movie titles are not escaped when being used as "alt=" and "title=" on HTML elements.
-This is only a problem if the movie title contains a single quote.
-I solved this by adding a function to mdb_movie.inc for $MOVIE[TITLEESC] which basically consists of:
return htmlspecialchars($this->data['TITLE'], ENT_QUOTES);
-I then changed all references of "='$MOVIE[TITLE]" to "$MOVIE[TITLEESC]" in the cover*.tpl files

I have some very non-standard width x height cover sizes which do not display correctly
-This required changing a few things
--Changed various "#mdb .movies A.cover*" CSS instruction in style.css by:
---For "IMG" elements: removed width/height, changed margin to auto, and added display:block.
---For non-"IMG" instructions: Added background-color
---This resulted in the following:

Code: [Select]

...
#mdb .movies A.cover_details IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px; }
#mdb .movies A.cover_details            { background-color: #88806B; display: block; padding: 0px; border: 4px solid #88806B; margin-right: 10px; width: 130px; height: 180px;}
...
#mdb .movies A.cover IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover            { background-color: #88806B; display: block; padding: 0px; border: 4px solid #88806B; margin-right: 10px; width: 156px; height: 216px;}
...
#mdb .movies A.cover2 IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover2            { background-color: #88806B; display: block; padding: 0px; border: 4px solid #88806B; margin-right: 10px; width: 80px; height: 115px;}
...
#mdb .movies A.cover3 IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover3            { background-color: #88806B; display: block; padding: 0px; border: 6px solid #88806B; margin-right: 10px; width: 315px; height: 450px;}
...
#mdb .movies A.cover_random IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover_random            { background-color: #88806B; display: block; padding: 0px; border: 3px solid #88806B; margin-right: 0px; width: 55px; height: 77px;}
...--Modified the resize.php so that if both width and height are given, they are considered as maximum width/height. Bascially, replaced

Code: [Select]

if ($h == NULL) { $h = $height * ($w / $width); }
if ($w == NULL) { $w = $width * ($h / $height); }
with

Code: [Select]

if ( !empty($h) && !empty($w) ) {
$hSizedByTargetW = $height * ($w / $width);
$wSizedByTargetH = $width * ($h / $height);
// If resizing based on width causes the height to exceed target,
//  resize by the target height.
if ( $hSizedByTargetW > $h ) {
$w = $width * ($h / $height);
// If resizing based on height causes the width to exceed target,
//  resize by the target width.
} elseif ( $wSizedByTargetH > $w ) {
$h = $height * ($w / $width);
} else {
  if  ( abs($hSizedByTargetW - $h) < abs($wSizedByTargetH - $w) ) {
$w = $wSizedByTargetH;
  } else {
$h = $hSizedByTargetW;
  }
}
} else {
  if ($h == NULL) { $h = $height * ($w / $width); }
  if ($w == NULL) { $w = $width * ($h / $height); }
}--Added height to all invocations of the resize.php script.  This was done by replacing the "&w=?" in the *.tpl files with "&w=?&h=??".  This needed to be done for all 5 image widths:
---&w=130 -> &w=130&h=180
---&w=156 -> &w=156&h=216
---&w=80 -> &w=80&h=115
---&w=315 -> &w=315&h=450
---&w=55 -> &w=55&h=77

I changed the processperiod.php to be more dynamic by replacing the calculated period values with:

Code: [Select]

if ( $y >= 2000 ) {
  $period = substr($y, 0, -1) . (substr($y, -1) >= '5' ? '5' : '0');
  $period = $period . '-' . ($period + 4);
} elseif ( $y >= 1930 ) {
  $period = substr($y, 0, -1) . '0';
  $period = $period . '-' . ($period + 9);
} elseif ( $y >= 1888 ) {
  $period="1888-1929";
} else {
  $period="Unknown";
}
I also made the processperiod.php re-runable by moving the call to calculate the periods outside of the test for the column having been added to the datbase.  The 2 lines moved were:

Code: [Select]

$this->processPeriodForMovies();
print "<br>Period processed for all movies.";
I also changed the floatbox options:
-Changed "width:443 height:300" to "width:428" in the various cover*.tpl files.
-NOTE: This requires using a new version of floatbox which auto-calculates the height.  Of course, the new version of floatbox also requires a license to prevent a nag window .  If you use the floatbox (v 3.51) as included with the downloads, the height of the box will not be correct.  So, you may want to change it to something like "width:443 height:315".  This will work for most movie descriptions.

[b_sleeth]

One other note.  I changed the .xsl to automatically create and update the PERIOD column.  This means that the processperiod.php does not need to be run after you update your movie database (and if you were following along in the previous post, it also means that you do not need to modify the processperiod.php file).

The changes to the .xsl file were:

Add the PERIOD column to the table create command:
-Find: PICTURENAME TEXT
-Replace with: PICTURENAME TEXT,PERIOD VARCHAR(15)

Set PERIOD to an empty string on the insert commands:
-Find: <xsl:value-of select="');'"/>
-Replace with: <xsl:value-of select="','');'"/>

Update PERIOD after all movies have been inserted:
-Find (2 lines):
    </xsl:for-each>
  </xsl:template>
-Replace with (3 lines):
    </xsl:for-each>
    <xsl:value-of select="'UPDATE movies SET PERIOD = CASE WHEN YEAR >= 2000 THEN CONCAT(LEFT(CAST(YEAR AS CHAR(4)), 3), CASE WHEN RIGHT(CAST(YEAR AS CHAR(4)), 1) >= &quot;5&quot; THEN &quot;5&quot; ELSE &quot;0&quot; END, &quot;-&quot;, LEFT(CAST(YEAR AS CHAR(4)), 3), CASE WHEN RIGHT(CAST(YEAR AS CHAR(4)), 1) >= &quot;5&quot; THEN &quot;9&quot; ELSE &quot;4&quot; END) WHEN YEAR >= 1930 THEN CONCAT(LEFT(CAST(YEAR AS CHAR(4)), 3), &quot;0-&quot;, LEFT(CAST(YEAR AS CHAR(4)), 3), &quot;9&quot;) WHEN YEAR >= 1888 THEN &quot;1888-1929&quot; ELSE &quot;Unknown&quot; END;&#xa;'"/>
  </xsl:template>

Once again, thank you for sharing this excellent web export!

[koliniol]

Hello
Can you share your template ?
also do you have a site where we can see the resulting pages ?
Thanks

[b_sleeth]

I actually have 3 flavors of the export template.

The first one is for use with the .xslt transformation.  Besides making the exported data a well-formed XML document rooted at "<movies>", it also wraps the exported data in "<![CDATA[", "]]>" tags.  In hopes of getting the auto-load feature to work, I also replaced all CR/LF in the export data with "<br>".  This is required because the auto-load PHP script uses a read operation that breaks the data by CR/LF and this causes problems when attempting to run each line of text as a full SQL statement.  Although the "<br>" fixed this problem, the auto-load routine still suffers from a character encoding problem which causes "garbage" characters to show up in the data (e.g., "Alfonso Cuarón" might be imported as "Alfonso CuarÅn").


My second export template was an experiment with the MySQL "LOAD XML".  This export template allows you to simply export your movie database and then use the following MySQL statements to import the XML file without having to use .xslt transformation.  This method worked great on my local MySQL v5.5 database; unfortunately, my hosting site is using MySQL v5.1 which does not support the "LOAD XML" command (I think "LOAD XML" was added in 5.5).  The full SQL import (including drop/add of the movies table) is:

Code: [Select]

USE rainfusion_movies;
DROP TABLE IF EXISTS movies;
CREATE TABLE movies (NUM INT NOT NULL, TITLE TEXT, ORIGINALTITLE TEXT, AKA TEXT, YEAR INT,
    CATEGORY TEXT, COUNTRY TEXT, STUDIO TEXT, RELEASES TEXT, RATING TEXT, TAGS TEXT,
    DIRECTOR TEXT, PRODUCER TEXT, SCENARIO TEXT, MUSIC TEXT, ACTORS TEXT, DESCRIPTION TEXT,
    COMMENT TEXT, TAGLINE TEXT, URL TEXT, IMDBRATING FLOAT, ALLMOVIERATING FLOAT, ORATING TEXT,
    DATEADDED DATE, PATH TEXT, COUNT TEXT, TYPE TEXT, MEDIATYPE TEXT, LENGTH INT, FILESIZE INT,
    LANGUAGE TEXT, SUBTITLES TEXT, TRANSLATION TEXT, RESOLUTION TEXT, FRAMERATE TEXT,
    VIDEOFORMAT TEXT, VIDEOBITRATE INT, AUDIOFORMAT TEXT, AUDIOBITRATE INT, LABEL TEXT,
    FEATURES TEXT, VIEWED TEXT, VIEWDATE TEXT, WISH TEXT, BOOKMARK TEXT, PICTURENAME TEXT,
    PERIOD VARCHAR(15), PRIMARY KEY (NUM));
LOAD XML
  LOCAL
  INFILE 'antexport/movies.xml'
  REPLACE
  INTO TABLE movies
  ROWS IDENTIFIED BY '<row>';
UPDATE movies a
  SET a.PERIOD = CASE
      WHEN a.YEAR >= 2000 THEN CONCAT(LEFT(CAST(a.YEAR AS CHAR(4)), 3),
          CASE WHEN RIGHT(CAST(a.YEAR AS CHAR(4)), 1) >= "5" THEN "5" ELSE "0" END,
          "-", LEFT(CAST(a.YEAR AS CHAR(4)), 3),
          CASE WHEN RIGHT(CAST(a.YEAR AS CHAR(4)), 1) >= "5" THEN "9" ELSE "4" END)
      WHEN a.YEAR >= 1930 THEN CONCAT(LEFT(CAST(a.YEAR AS CHAR(4)), 3), "0-", LEFT(CAST(a.YEAR AS CHAR(4)), 3), "9")
      WHEN a.YEAR >= 1888 THEN "1888-1929"
      ELSE "Unknown" END;

The third export template was another experiment, this time with MySQL DUMP.  Unfortunately, there appears to be no native method to restore a mysqldump which was dumped using the XML format (--xml option).  There is a Google Code project, mysqldump-x-restore, which provides an .xslt style sheet to transform the XML DUMP into INSERT statements.  But, that does not eliminate the extra step.  There is also a MySQL DevZone article, Using XML in MySQL 5.1 and 6.0, which discusses adding a stored procedure that will handle the import.


The bottom line is that I am using the first export template and the PVD_Movies_bs.xsl transformation style sheet (also provided).  Once my hosting site updates to MySQL 5.5 (as-if that is going to happen anytime soon), I will use the second export template so that I do not need to run the transformation.

The end result of all of this can bee seen at Sleeth DVD Library.  If you are interested my CSS and web templates, I can post those as well along with my "readme" notes to myself regarding the changes I made (FYI: My notes are "mostly" complete, but I am sure I missed one or two things).

Also note that none of my export templates attempt to fix the date format issue as documented by devilingrey.  I use a text editor that supports a RegEx find/replace command and use the following commands (without quotes ... actually, I created a macro to run all of the find/replace commands and then save and close the file):
 Find RegExp "(1[0-2])\/([1-3][0-9])\/([1-9][0-9]{0,3})"
 Replace All "\3-\1-\2"
 Find RegExp "(1[0-2])\/([1-9])\/([1-9][0-9]{0,3})"
 Replace All "\3-\1-0\2"
 Find RegExp "([1-9])\/([1-3][0-9])\/([1-9][0-9]{0,3})"
 Replace All "\3-0\1-\2"
 Find RegExp "([1-9])\/([1-9])\/([1-9][0-9]{0,3})"
 Replace All "\3-0\1-0\2"
These series of find/replace commands are a bit retentive as they are ensuring a valid month (1-9, or 10-12), a semi-valid date (1-9, 10-39), and a 4-digit year.  The end result is that dates will be reformatted as yyyy-mm-dd (where month and day have leading zeros).

[attachment deleted by admin]