Personal Video Database
English => Development => Topic started by: blue334 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.
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/mdmzyztym4n/pkdb for 0.9.9.8 - website files.zip)
http://www.mediafire.com/file/z2dmwgmmhjg/templates - sql scripts.zip (http://www.mediafire.com/file/z2dmwgmmhjg/templates - sql scripts.zip)
Example site in my sig.
-
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.
-
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 (http://forum.antp.be/phpbb2/viewtopic.php?t=3429) 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
-
Deleted.
See later post.
-
Very cool, blue. Thanks for your contribution.
I'll consider adding the export template and maybe some additional automation to make the process easier.
-
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.
-
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.
-
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.
-
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.
-
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.
-
A custom field would be better, provided it can be exported by the export plugin.
All fields can be exported
Date viewed could also be made a custom field
What for? There is already a native date viewed field in PVD.
-
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.
-
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.
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.
-
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.
-
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.
-
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 (http://www.videodb.info/forum_en/index.php?topic=933.msg2628#msg2628) 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.
-
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.
-
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?
-
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 (http://www.videodb.info/forum_en/index.php?topic=952.msg2376#msg2376)... 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.
-
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.
-
The replace function is indeed case sensitive now, but I can change this behavior easily.
I'm sure if I were using this regularly, I would find occasion to want it both ways. Can you add an option, or provide two separate functions?
-
I'm sure if I were using this regularly, I would find occasion to want it both ways. Can you add an option, or provide two separate functions?
OK, no problem. There will be two replace functions.
-
Sounds good, thanks.
-
Here are some updated files. Changes are:
- cleaned the code,
- improved instructions
- optional components: "Period" panel (see my site (http://blue334.awardspace.com/)) and awardspace filter replacement commands.
PKs_MovieDB_modified_public.zip (http://www.mediafire.com/?sharekey=7aca52d8176667a6ab1eab3e9fa335ca0c7314a6c7485b9a)
Contents of current README:
PK's MovieBD dynamic website template.
Modified and adapted for Personal Video Database (PVD).
Date 2008-10-20
1. Unpack PKs_MovieDB_modified_public.zip
2. Register a website at www.awardspace.com or other free host (awardspace assumed below).
3. Create a database "yourdomain_movies" using the management tools for the site.
Log into phpmyadmin. Run the SQL query "fields.sql" 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 with the database values.
5. Log on to yourdomain.awardspace.com with an ftp manager
with yourdomain:pass as credentials (PASV mode).
6. Where the dummy index.html file resides, upload contents
of folder PKs_MovieDB_modified_public (including the modified mdb_config.inc)
7. Check that you can access your domain http://yourdomain.awardspace.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.
10. Start PVD
11. Export database using the sql export template (put sql.ptm in
\Personal Video Database\Plugins\Templates first).
Export to the directory you just created.
12. Open the file in notepad++ or similar editor that will save UTF-8 NO BOM format (not notepad).
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. If your sql export file is less than about 1,700KB, your
script is ready to go. Otherwise split file into parts no bigger than 1,700KB.
Make sure the split files are also saved as UTF-8 NO BOM (Byte Order Mark).
The filesize limit suggested by phpadmin (2,048KB) is too high and will fail to run.
17. Resize/convert images to jpg W=96px using a batch tool (e.g. Xnview, Faststone)
18. Log on to awardspace>phpmyadmin again.
19. Import the sql script(s) into the
database.
20. Upload the resized images to the antexport folder using your ftp file manager.
21. Reload website and you're done. Ask me if there are problems.
*MORE STUFF*
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.
Run http://yourdomain.awardspace.com/processperiod.php in your browser
In the side panel section of mdb_config.inc, enable the PERIOD panel (change FALSE to TRUE).
Also uncomment the three lines (remove peceding slashes).
Disable the YEAR panel since you don't need both.
Upload mdb_config.inc again and reload your site.
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.
AUTOMATICALLY REPLACE WORDs BLOCKED BY AWARDSPACE
Changes words/phrases that will cause problems on an awardspace site
(due to their list of blocked words and phrases).
Paste contents of "Awardspace filtered words.txt" into the OPTIONS section of sql.ptm
(e.g below the last replace)
The word list is not complete but it will remove the most common filtered words.
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.
-
WOW :o
That is impressive! (both the web template using the db from PVD and also your db!)
I'm not 100% sure how, but I'm going to have to have a go at setting this up on my web page too!
I only came across PVD today and I am sooo impressed with it! Lots of info and friendly folks here on the forums too!
Keep up the good work blue334!
-
Thanks!
Been trying out a couple of other free hosts, and I think I'll change my recommendation of awardspace to byethost. Speedwise they're as good as or better than awardspace, but in addition they give you up to three databases, more space, no filtered words (it seems) and no practical filesize limit for sql scripts. Overall a better choice if uptime is good.
Also tried zymic.com, but that was too slow.
-
i setup a free webspace account, althought i do already have webspace but no MySQL on it, so i set a new one up as u reccomended but having big trouble trying to figure out how these mySQL work, i created a database, but it's giving me a table full of blanks which i need to fill in and im not familiar with the language they use on these database creation things in mysql. it's asking about singed and unsigned, Null, and lots of blanks i have no idea what there are all for, i managed to fill in my colum titles but it won;t save because it keeps saying this is not a number!
Any advice?
I love the website u created, just the kind of thing i've been trying to find for ages, i found lots of free web based databases but all of them are setout just like excel or similar, instead of laid out as a webpage, and this is the first time i've found one like this.
Does anyone know of a commercial product(that needs no advanced tecnical knowledge) which would setout a excel or pvd database on a website in a similar way to what your doing hear?
-
Are you in phpmyadmin? It is the web-based management interface for mysql databases that every site will have. It should look something like this:
(http://img208.imageshack.us/img208/6487/clipboard1gs4.th.png) (http://img208.imageshack.us/my.php?image=clipboard1gs4.png)(http://img208.imageshack.us/images/thpix.gif) (http://g.imageshack.us/thpix.php)
You should not be setting up anything manually in the database, it is all done by running queries on the database. The first query is done by pasting the contents of fields.sql (a plaintext file included in the .zip) into the text box shown, and hitting "GO". If you already set up something manually, you must delete it first. Hit the "Drop" tab to do this.
This sets up the database table, the basic framework or "cupboard" for your data.
I'll gladly help you with the next steps, let me know how you do with this first.
-
thanks for the reply, i am using the free webhost u suggested,bytehost, so i went to the mySQL bit and created a new database, then named a field or somehting like it explains in your instructions called Movies, but i did not get to do the mysql query as i did not get that option i think probabbly because i was using bytehost and not the other one. So it asked how many fields i wanted, i put 28, i got this number by counting the number of fields on my PVD entries, eg. movie name, description, date etc.
So thats where i got up to, any advice would be most apreciated. I am pretty knowledgable when it comes to computers, and web design etc but never used MySQL before.
-
If you're in phpmyadmin you should have no problem locating the sql query tab. The phpmyadmin interface is more or less the same regardless of host. Make sure you clicked the link shown here:
(http://img84.imageshack.us/img84/1106/3a6tmpuy5.th.png) (http://img84.imageshack.us/my.php?image=3a6tmpuy5.png)(http://img84.imageshack.us/images/thpix.gif) (http://g.imageshack.us/thpix.php)
Please note that I'm not telling you to create any tables or fields manually as you seem to believe you must, but to run a query/script to do this job. This query is contained in the fields.sql file. If you already set up a table named movies, please delete it as instructed above (the "Drop" part). Then run the query from the sql tab.
If have no trouble understanding that this can be confusing, so just keep asking.
PS it is byethost, not bytehost if that is an actual service.
-
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.
Got the tags working properly now! I have limited the number of tags shown on the initial page, since there can be a lot of them. The complete list is displayed on the details page.
New domain/link where these are implemented: http://www.mymoviecollection.co.cc/
EDIT: if that link doesn't work properly try: http://blue334.byethost9.com
-
thanks for the reply Blue, yes i did spell the name wrong it is byethost like u said, very nice find, thnx for reccomending this.
Will try what u said and see how i get on next time. thnx for the help...
-
I had more luck this time, i didn;t read your instructions properly first time and didn;t see that i had to copy the text for the sql query. anyway i seem to be doing ok with it now, the bit i want to ask youo about is where on your premade fields query which setup the database, it has pre done fields for title, description etc. can you tell me how does the script recognise these fileds in the database info you are importing, does it recognise the words eg. title, description? or does it go by the order you put them in?
I'm asking because my database does not have the same order as yours, or i don't want to use all the fields u have included like bitrate media format etc. so i'm wondering how this might effect things when i try to add the database info to the equation?
I'm guessing that i just need to remove the fields i don't want from the my sql database table right?
But then will i need to alter any other files?
****
I'm also noticing when i'm exporting the pvd, it exports to an excel csv file, but it is not exporting all fields, any idea why this might be? for example fields like Producer, Date Added, Length are not showing on the csv file, only these fields are importing:
Title;Original Title;Year;Genre;Country;Actors;Director;Description;
I guess i have to configure it somehow.
*i just noticed in the export configuration that only those selected fields mentioned where included in the export script, so i guess that explains why they only exported these, i looked at the sql export script and it includes lots more but it also includes lots that i don;t want, or don't use and don;t want showing in the final website.
Would be very helpful if u can advise.
-
my other problem, i can;t find where pvd is saving all the posters/dvd covers! i checked the tools menu and it states a folder that doesn't exist! so im now very confused as to where pvd is saving everything, i have checked the pvd installation folder and there are no files in the posters folder either, any ideas where they may be saved or how i can find out where?
-
In the meantime i tried to import the data i had just to see what results i got, the first error is it says
#1054 - Unknown column 'VIDEOFORMAT' in 'field list'
this i guess will be due to some of the field titles not matching the ones on the mdb_config.inc file, or something to do with that anyway! something else i did while the database was created using the sql query, i deleted the fields that i didn't want to use form the database, this would have incluided the VIDEOFORMAT it mentions above, so maybe i get the error because the mdb_config.inc file is telling it to put this in ??
So i went to check that i could view the index.php file on my website which i had forget to do earlier and i cannot view it for some reason, it says 'cannot access XXX database with XXXX(my username) login, why it says that i have no idea, any suggestions?
hope im not hijacking this thread with my problems... :-\
-
You shouldn't delete fields from the database, it just adds more trouble. You may not need that data, but other files assume it is there at least as a blank value. It really doesn't matter if those fields are in the database, and stuff you don't want displayed you just comment out in the html files (.tpl files). But don't worry about that now, try getting your page up first.
Next, forget about exporting csv, it is not relevant to this. What you are exporting is sql script files with a .sql extension. If you haven't already you must copy the sql.ptm file to the ..\Personal Video Database\Plugins\Templates directory. That will make it appear as an option in the export dialog pulldown menu. Before exporting, you must make a subdirectory named "images" in the folder where you plan on saving the sql file. If you don't images won't go anywhere probably.
NB! After you have successfully created a file with .sql extension this goes into the database the same way the fields.sql file does. But in this case the file will be much bigger, so it will not be practical/possible to paste its content in the sql query area in phpmyadmin. Instead you should go to the IMPORT tab, where there is an option to select the file from your hd and run it directly.
The database connection problem you experience is due to wrong credentials or other info in mdb_config.inc
Since you're with byethost, it should look somewhat similar to this:
$this->config['mysql']['server'] = "sql207.byethost9.com";
$this->config['mysql']['username'] = "b9_2508518";
$this->config['mysql']['password'] = "mypass";
$this->config['mysql']['database'] = "b9_2508518_movies";
$this->config['mysql']['table'] = "movies";
Since you deleted fields in the database you must delete/drop it completely and run the fields.sql query again.
-
New domain/link where these are implemented: http://www.mymoviecollection.co.cc/
EDIT: if that link doesn't work properly try: http://blue334.byethost9.com
bottom link appears to be broken, currently redirecting to here: http://php0h.com/hosting-blog6/index.php with the message "YOU ARE VIEWING THE OLD WEBSITE PLEASE WAIT WHILE YOU ARE REDIRECTED". Not sure what that's about?!?
-
Every free host has its nags - they need one way or another to make a revenue. I don't yet know how much of an issue this will be, but both my domains at byethost have had downtime of about 24h during which they redirect to ads. It is basically the same as the aforementioned blocked words at awardspace, where you get redirected to their front page (where they sell commercial hosting packages).
If one wants free hosting, its only a matter of choosing the lesser evil. I don't want forced ads on my site, and I didn't much like the awardspace thing with blocked words. If byethost is still the best choice remains to be seen, depends on the frequency of these programmed downtimes.
-
Next, forget about exporting csv, it is not relevant to this. What you are exporting is sql script files with a .sql extension. If you haven't already you must copy the sql.ptm file to the ..\Personal Video Database\Plugins\Templates directory. DONE THIS OK That will make it appear as an option in the export dialog pulldown menu. Before exporting, you must make a subdirectory named "images" in the folder where you plan on saving the sql file DONE THIS AND I NOW SEE THE IMAGES ARE IN THERE. If you don't images won't go anywhere probably.
NB! After you have successfully created a file with .sql extension this goes into the database the same way the fields.sql file does DONE. But in this case the file will be much bigger, so it will not be practical/possible to paste its content in the sql query area in phpmyadmin. Instead you should go to the IMPORT tab, where there is an option to select the file from your hd and run it directly.
The database connection problem you experience is due to wrong credentials or other info in mdb_config.inc
Since you're with byethost, it should look somewhat similar to this:
$this->config['mysql']['server'] = "sql207.byethost9.com";
$this->config['mysql']['username'] = "b9_2508518";
$this->config['mysql']['password'] = "mypass";
$this->config['mysql']['database'] = "b9_2508518_movies";
$this->config['mysql']['table'] = "movies";
Since you deleted fields in the database you must delete/drop it completely and run the fields.sql query again.
Thanks for your help, i will redo the sql query and redo the database and redo the mdb config file, i think it's this that might be causing problems, u know the info u gave as an example of what it should look like, is this the only thing i should be changing? i altered a few more things on that file.
But even if the mdb config file was wrong, shoulkdn;t i have seen somehting when i tried to access the index.php file? i will redo it and see how i go this time.
Thanks again for all this help
-
I got the config file sorted now, i had the wrong password so i can now see the index.php ok. I made the new database in mysql etc.
but the problem i now have is when i try to import the .sql file it gives an error, it says 'Duplicate' i should have copied it for u to see but it highlighted the field YEAR and mentioned a duplicate in the database which gave an error. Any ideas what might be causing this?
I did change the way my pc shows the date, althought i only had the option to change the 'short date' as it says in windows options.
In PVD i actually do the dates liked: 21st Oct.2008 or 21/10/08 it changes.
-
My first thought would be that the database table is not empty, thus giving a duplicate error. But if you're sure it contains no data could you post a couple of lines from the sql file? Maybe I can spot something wrong.
You could also try to run just a couple of lines from the sql file, to narrow down the problem.
-
I removed the database, created a fresh one and i get the same error, here it is below:
******************************************************
Error
SQL query:
INSERT INTO movies( NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME )
VALUES (
'0', '', '', '', '', '2008-05-29', '', '0', '', '150 YEARS OF INDIANS IN JAMAICA', '', '', '', 'JA', '', '0', '0', '', '', 'A discussion and re-enactment of the coming of the East Indians to Jamaica.', '', '', '', '', '', '', '', '', '', '0', '0', '', ''
);
MySQL said:
#1062 - Duplicate entry '0' for key 1
*************************************************
It couldn;t be refering to a duplicate film entry could it?? as i may have some duplicate titles in there.
Here is a sample of a few lines from the SQL file i exported from PVD:
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('0', '', '', '', '', '2008-08-06', '', '7.4', 'Amandla! A Revolution in Four Part Harmony', 'Amandla', '', 'Lee Hirsch', 'Johnathan Dorfman, Temple Fennell, Lee Hirsch, Desireé Markgraaff, Sherry Simpson', 'South Africa, USA', 'Documentary, Music, History', '2002', '108', 'Walter Cronkite, F.W. de Klerk, Abdullah Ibrahim, Jesse Jackson, Duma Ka Ndlovu, Ronnie Kasrils, Sibongile Khumalo, Vusi Mahlasela, Miriam Makeba, Nelson Mandela, Winnie Mandela, Hugh Masekela, Sophie Mgcina, Thandi Modise, Sifiso Ntuli, Sibusiso Nxumalo, Dolly Rathebe, Hendrik Frensch Verwoerd, Lindiwe Zulu', 'http://www.imdb.com/title/tt0303297/', 'Through a chronological history of the South African liberation struggle, this documentary cites examples of the way that music was used in the fight for freedom. Songs united those who were being oppressed and gave those fighting a way to express their plight. The music consoled those incarcerated, and created an effective underground form of communication inside the prisons.', '', '', '', '', '', '', '0', '', '', '0', '0', '2008-07-19', 'image83.jpg');
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('0', '', '', '', '', '2008-08-06', '', '0', '', 'Amandla - Wake Up and Live (Bob Marley)', '', '', '', 'USA', '', '0', '0', '', '', '', 'Havard Stadium, boston, 21st July 1979', '', '', '', '', '', '0', '', '', '0', '0', '2008-07-19', 'image84.jpg');
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('0', '', '', '', '', '2008-09-05', '', '0', '', 'Amazing World - Jamaica', '', '', '', '', '', '2001', '0', '', '', 'series, one ja episode', '', '', '', '', '', '', '0', '', '', '0', '0', '', '');
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('0', '', '', '', '', '2008-08-12', '', '0', '', 'Amish in the City - Reggae, Never Heard of the Dude', '', '', '', '', '', '2005', '60', '', 'http://www.imdb.com/find?s=tt&q=Amish%20in%20the%20City%20-%20Reggae,%20Never%20Heard%20of%20the%20Dude', 'Reality series following young Amish people as they get their first experience of the outside world. The Amish kids grapple with LA''s public transport system, attend a music concert and indulge themselves at the races. ', '', '', '', '', '', '', '0', '', '', '0', '0', '', '');
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('0', '', '', '', '', '2008-05-29', '', '0', '', 'An Evening With Harry Belafonte', '', '', '', '', '', '2003', '57', '', '', 'Taped in Chicago in front of a live audience, Harry Belafonte was interviewed by his friend Danny Glover. The viewer will travel back in time to Mr. Belafonte''s early childhood: New York to Jamaica and back to New York, wherein he experienced racism that was life altering. His frustrations propelled him to Humanitarian causes: marching with Martin Luther King, the Free South Africa movement, and We Are The World recording session and UNICEF to name a few.', '', '', '', '', '', '', '', '', '', '0', '0', '', '');
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('0', '', '', '', '', '2008-05-29', '', '0', '', 'Anansi', '', '', 'Fritz Baumann', '', '', '2002', '80', '', '', 'German director Fritz Baumann''s 2002 immigrant drama follows the paths of four Ghanaians as they begin a journey to Germany in the hopes of reaping the benefits of a capitalist society -- the likes of which are simply not to be found in their homeland. Things go from bad to worse for the four companions as they begin their trip by stowing away on a freighter, only to be caught by the crew a short time later. They are sent out to sea in a small raft and eventually beach on a remote coast that leads to the Sahara. After spending a number of days wandering about, they encounter a caravan that leads them to the Moroccan port of Tangiers. After a number of devastating challenges, the group finally reaches its destination -- but not without some substantial losses along the way.', '', '', '', '', '', '', '', '', '', '0', '0', '', '');
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('0', '', '', '', '', '2008-05-29', '', '0', '', 'Anansi Fantasia', '', '', 'Shirley Thompson', 'UK', '', '1993', '7', '', '', 'One of a series of short productions made by young black film- and video-makers, a dramatisation of a Caribbean folk tale in which trickster Anansi tries to find out a young woman’s secret name, the narration is by Jamaican-born community activist, Connie Mark (1923-2007). Cast: Storyteller, Connie Mark, Anansi, Leon Hazlewood, Dora, Dollie Henry, King, Hi Ching , Missirolinkia, Lorraine Le-Blanc. Crew: Director of Photography Jeff Baynes, Gaffer Jim Lennon, Camera Assistant Tiffany Walker, Grip Martin McCullough, Sound Recordist Kim Joseph, Art Director Andrea Montag, Make-up Artist Michele Baylis, Stills Photographer Armet Francis, Catering H. E.Thompson, Production Manager Dominic DeVine, Production Supervisor Juliet McKoen, Off-Line Editor Peter Harvey, On-Line Editor Julian Bryant, Telecine Operator Dave Ludlam, Music Shirley Thompson, Music & Effects Recording Henry Thomas, Executive Producers Paul Corley, Rodney Wilson, Special Thanks to Anita Bell, Jason Glenister, Carol Jacobs, Michael Ho, Glen Laker, Bill Pretty, Richard Thompson, Greenwich Council, The Mill, SVC TV, Tattooist International Ltd., Theatre Royal, Stratford East. Produced and Directed by Shirley Thompson. For the Arts Council of Great Britain and Carlton Television. © Arts Council of Great Britain & Prime Productions 1993.', '', '', '', '', '', '', '', '', '', '0', '0', '', '');
-
The problem is your database entries aren't numbered. The ID number (the first value) is assigned as the "primary key" in the database, which probably means it has to be unique. I wasn't even aware that PVD would allow duplicate IDs.
Fortunately the internal PVD database has an internal ID you can use as a replacement value. Make the change shown with red to the sql.ptm file before exporting, this should assign the internal ID number instead:
%OPTIONS%
filter="SQL Files|*.sql"
encoding="UTF8"
replace=";<->,"
replace="'<->''"
replace=""<->"
replace="#13#10<-> "
replace="#13<-> "
replace="#10<-> "
imagepath="images\"
%OPTIONS%INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('{%value=0}', '', '{%value=47}', '', '', '{%value=17}', '', '{%value=9}', '{%value=3}', '{%value=2}', '', '{%value=53}', '{%value=56}', '{%value=45}', '{%value=44}', '{%value=5}', '{%value=19}', '{%value=52}', '{%value=8}', '{%value=15}', '{%value=16}', '{%value=23}', '{%value=24}', '{%value=43}', '{%value=25}', '{%value=21}', '{%value=22}', '{%value=48}', '{%value=49}', '{%value=26}', '{%value=29}', '{%value=33}', '{%value=63}');
I suspect the date values formatted like "21st July 1979" will end up as "0000-00-00" in the mysql database. It should be possible to fix this using regular expressions, let me know if it becomes a problem.
EDIT: forget the last part, seems the alternative date formatting I spotted is part of a descripton text and not a date field.
-
thanks,
you know on my pvd database where u have the first field for an entry where your supposed to put a ID number maybe, i have been using this field for the film title, i did this from the start without realizing, so where it says 'Original Title' this isn;t always filled in, i wonder if this will cause the problem?
I also remembered, i think i may have removed some of the fields on the pvd sql export config so i will put this back right aswell, maybe this was causing problems aswell? i'll let u know how i get on.
-
Well if that is the case you can't use the data in the ID field (which correctly is the short field before "Title"). If there is information in this field you want to display on the web page, you must manually move it to the "Original Title" field (if that is what it is)
Apparently PVD allows characters to be typed in the ID field, but the export plugin accepts only numbers (which is why all your ID fields are assigned ID=0 in the exported file)
You should not remove anything from the export template unless you fully understand the structure. Each '{%value=XX}' has a counterpart database field, and they're sorted in the same order.
That means for example that the 9th database field ORIGINALTITLE corresponds to the 9th export variable '{%value=3}'. If you remove one but not the other your data will end up in the wrong fields.
-
Hi, yes i thought that might be the case where like the 9th database field ORIGINALTITLE corresponds to the 9th export variable, would be easier if it could recognise the field name rather than order in the list.
But anyway i have done it successfully, in the end! i'm very greatful for your help.
I just repasted the template file into PVD as i think i may have removed some fields from the script, so fixed this, and i made the change to the script like u told me to do and it now works fine.
My next question is whats the best way to edit the main view of the database, just things like colours and aswell as adding maybe other things on either side of the database as there are 2 black empty bits where i can put extra info.
Can i do this in HTML? i think the index file is in PHP but i'm not used to using php but i do have an editor, can i edit using Front Page? or NVU?
Thanks for helping with all the info and files etc.
By the way, the film names did import ok in the end, u can have a look:
http://bwoyruff.co.cc/index.php
But i noticed the film title is not clickable like yours i just have to click the jpeg instead, but i can live with that. Actually the example on the webpage has a clickable title thats just because the entry also had info in the Title Field of PVD but most of my films titles are int he ID no section.
you will only see one entry in there right now, i did upload 2000+ but deleted them now i know it works and will now work on a new list for the web.
-
Seems you already figured out how to enable/disable the side panels in mdb_confic.inc. The files that contain the html code reside in the templates/default folder.
detail_view.tpl is the main view and details.tpl is the detailed view. If you know a little html you shouldn't have any problems with these. Not sure if they will open in a wysiwyg editor though.
The colors are all defined in the stylesheet style.css with the single exception of the main background color which is defined in index.php. I think the easiest way to edit the colors is to make a screencap of the web page and open it uncompressed in photoshop or similar software. Using the eydropper tool you can identify the various colors in the stylesheet, this is easier than trying to interpret the various pointers in the stylesheet directly. Then just tweak colors as you wish using replace color in photoshop, identify the new color value and search-replace those values in the stylesheet.
There are some gif files in templates/default/images which naturally aren't tweaked using the stylesheet, you will need to edit these manually. The logo also resides here, I suspect you don't want to keep my walking egg.
-
Seems you already figured out how to enable/disable the side panels in mdb_confic.inc.
Disabling side panels may not be a good idea, although I suppose the cause of this may be something else... If a link is selected in details, that search will be executed, but then there is no way to clear it (e.g., selecting "All Movies" does nothing—the search terms stay in the URL).
-
i think i might have disabled one of the side pannels which displayed something i didn't want, maybe it was the years? i didn't really need a years display so i think i may have edited it out somewhere.
I have photoshop but i've never figured out how to use it to save web pages, but i get what u where saying about the colour changes, the main thing i want to do is disable the video bitrate and all those fields i don't use, so u guess i just need to edit the details.tpl
thanks for all your help.. i'm going to try and build a nice website to go with the database when i get time, will post a link when i eventually finnish it.
-
Added allmovie rating/link to the template (also as sort option). Tags have also been added, as mentioned in an earlier post.
Will wait for an updated export plugin allowing the export of actor's role before posting new files.
Here's what my current template/page looks like:
http://mymoviecollection.co.cc/index.php
-
Excellent work, blue334! Probably to have last version of a plug-in, how on your site http://mymoviecollection.co.cc/index.php?
-
Hi,
The template available for download (on page 2 of this thread) is up to date except for tags and allmovie rating/link. Unless these are very important to you I'd rather wait until I can make a working template for 0.9.9.X before uploading more files (the sql export template works for version 0.9.8.20 only).
-
Hi,
The template available for download (on page 2 of this thread) is up to date except for tags and allmovie rating/link. Unless these are very important to you I'd rather wait until I can make a working template for 0.9.9.X before uploading more files (the sql export template works for version 0.9.8.20 only).
Many thanks. I just had in view of your version with a rating and tags allmovies. The plug-in for the version 0.9.9.x is much more actual, I will look forward
-
First of all, thanks to Blue334 for your efforts on this, it's a dream come true for me really I love to see my database online.
I am a complete noob at this stuff so I followed your instructions completely blind and it worked!
I had an error message from PVD when exporting to Sql, (range excess error) but it did not seem to cause a problem...
For info, I am using the http://www.000webhost.com/ web free hosting service which has 1Gb size, and a 20Mb limit on Sql file import.
Funny: The IMDB ratings do not appear on my site...?
I am capturing it on all my movies though.
Also, only 5 directors appear on the side panel. I don't remember changing anything in the way I capture directors for my movies.
Actually (I keep on editing this post!), countries also are wrong, only 1% of them seem to have been properly captured on my page.
Of course I ran into other problems, solved a few, but here is one that I have no clue about, can you help?
I receive this error message after inporting the Sql file into my Database, and I don't know what it means:
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unclosed quote @ 3251
STR: '
SQL:
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('1052', '', 'DVD322', '', '', '18/09/2008', '', '7.8', 'Trois couleurs: Bleu', 'Trois couleurs: Bleu', '', 'Krzysztof Kieslowski', 'Marin Karmitz', 'France', 'Drama', '1993', '100', 'Juliette Binoche, Benoît Régent, Florence Pernel, Charlotte Véry, Hélène Vincent, Philippe Volter, Claude Duneton, Hugues Quester, Emmanuelle Riva, Florence Vignon, Daniel Martin, Jacek Ostaszewski, Catherine Therouenne, Yann Trégouët, Alain Ollivier, Isabelle Sadoyan, Pierre Forget, Philippe Manesse, Arno Chevrier, Idit Cebula, Stanislas Nordey, Jacques Disses, Michel Lisowski, Yves Penay, Philippe Morier-Genoud, Julie Delpy, Zbigniew Zamachowski, Alain Decaux, Julie Gayet, Piotr Jaxa', 'http://akas.imdb.com/title/tt0108394 http://www.allmovie.com/cg/avg.dll?p=avg&sql=1:131104', 'Three Colors: Blue is the first part of Kieslowski''s trilogy on France''s national motto: Liberty, Equality, and Fraternity. Blue is the story of Julie who loses her husband, an acclaimed European composer and her young daughter in a car accident. The film''s theme of liberty is manifested in Julie''s attempt to start life anew free of personal commitments, belongings grief and love. She intends to spiritually commit suicide by withdrawing from the world and live completely independently, anonymously and in solitude in the Parisian metropolis. Despite her intentions, people from her former and present life intrude with their own needs. However, the reality created by the people who need and care about her, a surprising discovery and the music around which the film revolves heals Julie and irresistably draws her back to the land of the living. ', ' The first of legendary Polish director Krzysztof Kielslowski''s Three Colors trilogy (representing the three colors of the French national flag) is a hypnotic and intriguing journey which follows Juliette Binoche after she tries to gain total liberty (the representation of blue in the flag) after the deaths of her husband (a famous European composer) and young daughter in an automobile accident. Of course isolating oneself from all others is not easy to do as the past keeps on popping up and the future holds many uncertainties. The fact that Binoche''s husband had an affair and the woman is now carrying his child is both ironic and tragic for all involved. In the end will Binoche be able to stop being blue? Blue, like all of Kielslowski''s works, thrives on symbolism (the color popping up all throughout the film) and an unmistakable tone (much the way that Roman Polanski uses that feature in his better productions). The film is not for all tastes because of strong adult content and a distinct female flair (due to the almost total focus on Binoche), but Blue is still a movie which is a testament to its excellent film-maker and its unique theatrical aspects. 4 stars out of 5. ', '', '', '', '', '', '', '', '', '0', '0', '', '
SQL query:
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('1052', '', 'DVD322', '', '', '18/09/2008', '', '7.8', 'Trois couleurs: Bleu', 'Trois couleurs: Bleu', '', 'Krzysztof Kieslowski', 'Marin Karmitz', 'France', 'Drama', '1993', '100', 'Juliette Binoche, Benoît Régent, Florence Pernel, Charlotte Véry, Hélène Vincent, Philippe Volter, Claude Duneton, Hugues Quester, Emmanuelle Riva, Florence Vignon, Daniel Martin, Jacek Ostaszewski, Catherine Therouenne, Yann Trégouët, Alain Ollivier, Isabelle Sadoyan, Pierre Forget, Philippe Manesse, Arno Chevrier, Idit Cebula, Stanislas Nordey, Jacques Disses, Michel Lisowski, Yves Penay, Philippe Morier-Genoud, Julie Delpy, Zbigniew Zamachowski, Alain Decaux, Julie Gayet, Piotr Jaxa', 'http://akas.imdb.com/title/tt0108394 http://www.allmovie.com/cg/avg.dll?p=avg&sql=1:131104', 'Three Colors: Blue is the first part of Kieslowski''s trilogy on France''s national motto: Liberty, Equality, and Fraternity. Blue is the story of Julie who loses her husband, an acclaimed European composer and her young daughter in a car accident. The film''s theme of liberty is manifested in Julie''s attempt to start life anew free of personal commitments, belongings grief and love. She intends to spiritually commit suicide by withdrawing from the world and live completely independently, anonymously and in solitude in the Parisian metropolis. Despite her intentions, people from her former and present life intrude with their own needs. However, the reality created by the people who need and care about her, a surprising discovery and the music around which the film revolves heals Julie and irresistably draws her back to the land of the living. ', ' The first of legendary Polish director Krzysztof Kielslowski''s Three Colors trilogy (representing the three colors of the French national flag) is a hypnotic and intriguing journey which follows Juliette Binoche after she tries to gain total liberty (the representation of blue in the flag) after the deaths of her husband (a famous European composer) and young daughter in an automobile accident. Of course isolating oneself from all others is not easy to do as the past keeps on popping up and the future holds many uncertainties. The fact that Binoche''s husband had an affair and the woman is now carrying his child is both ironic and tragic for all involved. In the end will Binoche be able to stop being blue? Blue, like all of Kielslowski''s works, thrives on symbolism (the color popping up all throughout the film) and an unmistakable tone (much the way that Roman Polanski uses that feature in his better productions). The film is not for all tastes because of strong adult content and a distinct female flair (due to the almost total focus on Binoche), but Blue is still a movie which is a testament to its excellent film-maker and its unique theatrical aspects. 4 stars out of 5. ', '', '', '', '', '', '', '', '', '0', '0', '', '
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
Thank you again for this!
D.
-
Hi,
The sql query does not appear to close each statement properly - each line of the query should close with [);] whereas yours close with [,'] . This will cause malfunction. Maybe you could post the sql export script you're using?
-
Thanks for your answer.
I am using the script you included in your .zip package.
I am attaching it in this reply (!! I have changed the extension to txt so that I can attach it to this message, so you need to re-change it to .ptm !). I have put it in the "templates" section of my PVD folder.
Is it the wrong one?
[attachment deleted by admin]
-
Hi,
sorry for the late reply, I forgot about the thread.
The template is the correct one, for ver 0.9.8.20 output. Regardless of version, I don't quite understand why the output is being truncated like that. Can you check if the [);] closing of each statement) is also missing in the sql script (the export output file), or just in the mysql error message?
-
Forgive my ignorance, I'm not sure exactly what a statement is, but here is an example, it looks like [);] is here (see text pasted right at the end of this message).
Remember, PVD gave me an error message after a while, when exporting the database in sql.
Well it must have cut off the export right after this "trois couleurs" movie, because it is the very bottom of the sql file.
The error message was something about some range ("range check error"), and I could not find anything in this forum/site about it.
Would you know why the export was cut off? too many movies?
Also, why would so much info be missing on the actual site (missing imdb ratings, missing directors, etc...)?
Thanks.
INSERT INTO movies (NUM, CHECKED, MEDIA, MEDIATYPE, SOURCE, DATEADD, BORROWER, RATING, ORIGINALTITLE, TRANSLATEDTITLE, FORMATTEDTITLE, DIRECTOR, PRODUCER, COUNTRY, CATEGORY, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOFORMAT, VIDEOBITRATE, AUDIOFORMAT, AUDIOBITRATE, RESOLUTION, FRAMERATE, LANGUAGES, SUBTITLES, FILESIZE, DISKS, VIEWDATE, PICTURENAME) VALUES ('1785', '', 'DVD514', '', '', '07/11/2008', '', '6.8', 'Itinéraire d''''un enfant gâté', 'Itineraire d''''un enfant gate', '', 'Claude Lelouch', 'Jean-Paul Belmondo, Claude Lelouch, Gerhard Schmidt', 'France, West Germany', 'Adventure, Comedy, Drama', '1988', '120', 'Jean-Paul Belmondo, Richard Anconina, Lio, Béatrice Agenin, Marie-Sophie L., Jean-Philippe Chatrier, Gila Von Weitershausen, Michel Beaune, Pierre Vernier, Philippe Lorin, Annie Philippe, Céline Caussimon, Daniel Gélin, Alexis Gruss, Nicolas Mallet, Firmin Gruss, Stephan Gruss, Paul Belmondo, Jacques Bonnot, Max Fournel, Patricia Grillo, Elie Lauer, Jeanne Marine, Pierre Meunier, Joëlle Miquel, Charles-Henry Tissot, Wookie Mayer, Arthur Brauss, Sabi Dorr, Udo Wachtveitl, Gilles Dimicelli, Marcel Guéguan, Nicole Croisille, Eva-Maria Hagen, Salomé Lelouch', 'http://www.imdb.com/title/tt0095386/ http://www.allmovie.com/cg/avg.dll?p=avg&sql=1:119647', 'French director Claude Lelouch, of Man and a Woman fame, called the shots on Itineraire D''''un Enfant Gate. Jean-Paul Belmondo (who co-produced the film) stars as a powerful international businessman who, in his youth, had been a carnival performer. Once more bitten by wanderlust, Sam Lion (Belmondo) tries to escape his responsibilities by staging his own death. Hiding out in Tanzania, Lion meets Abert Duvivier (Richard Anconina), one of his own employees. Duvivier, informing Lion that the business has gone to hell in a handbasket thanks to the mismanagement of Lion''''s daughter Victoria (Marie-Sophie Lelouch), begs the executive to return. The literal translation of the film''''s title is Itinerary of a Spoiled Child.', '', '', '', '', '', '', '', '', '', '0', '0', '', 'images/image144.jpg');
-
OK I have re-started everything from scratch.
I used the beta version of pvd this time and did not receive the "range check error" message, the generation of the sql file went all the way.
Now the problem I have, is that actors appear as director on my page...
I know the fix should be simple, but I really don't know what it is. Where should I make a change, directly in myphpadmin, or in the sql itself?
One day I'll succeed...
Thanks
-
Hi,
you'll need an updated export template for the beta version, since there are many changes.
I have one working already for my own site, I'll try to tidy it up and upload it tomorrow.
-
Since we have a release now I have some updated files. Also made some changes, like bigger posters.
Link to my site based on these files is in my sig.
Needed files:
http://www.mediafire.com/file/mdmzyztym4n/pkdb for 0.9.9.8 - website files.zip (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 (http://www.mediafire.com/file/z2dmwgmmhjg/templates - sql scripts.zip)
The files are just proof of concept and will have to be adapted for other databases. Ask me if you decide to try.
EDIT: Updated first post with a step-by-step description and the new files
-
Very impressive! 8)
A few suggestions...
Suppress list items when count is 0.
This update is confusing to the newcomer. It seems this is an update to an original set of files—but where are they? The best practise is to always update the first message in the topic with the current release. I don't think it would hurt to delete the original text of your first message and turn it into a "latest release" message. You can still post an update for existing users here, but also link back to the original message for those needing a "full install."
-
Fixed the first post so the thread is a little more welcoming.
About suppressing items, that may be difficult though I see your point. The left side panels do have a "threshold value", but that only applies to the unfiltered selection.
-
hello.
Following a wise advice, i decided to move from AMC to PVD today.
I've been using AMC for quite some years now, and using your template.
As you probably know, some people are still working on improving your template over there too, and I did make some changes for my personal use too.
My worst regret about leaving AMC was to be unable to adapt my template (as Im really bad with php), but then I found your thread here.
I thought you disappeared, but you just moved to here :)
So, anyway, thanks a lot for your work, I can keep using PK MovieDB !! Also, I realize that the changes made on the template are very similar to the changes I made myself, so your version is pretty much what Im looking for.
Just a little question. Do you plan on adding pages for people ? That would be awesome too. Probably quite a lot of work, but I believe in you ;D
-
I have a couple of questions:
1) About characters encoding.
I don't know much about char encodes, so forgive me.
I need to include foreign characters to my DB, mostly east asian (chinese, japanese). As you know, AMC was not friendly with those so I had to enter unicodes in the database so that the characters show up in your template.
Now, I see I will have to do changes in the charset to make your template work fine, will it be ok for east asian chars ?
2) Countries
Someone in AMC forums made some changes in your script so that multiple countries can me supported better.
I see your script still doesnt do that, neither in the menu or in the movie details.
I don't want to have "France, Italy" or "USA, UK" in my menu, i prefer that my movies enter both category separatly.
Is it possible to change that ? I see your menu has both "France, Italy" and "Italy, France", I suppose it annoys you too :)
In the details, it's not as big a problem, but if countries were not grouped, that would be better too.
3) Fields
Could you possibly list all the fields you are using for your script in here ?
I'm willing to start my list from scrap (because my amc database as a lot of problems imo), and I would like to start it with the same pattern as yours :)
THANK YOU VERY VERY MUCH (oops caplocks)
btw, i just saw the support of tags, it's very nice too.
-
Hi,
About you first post, I'm unsure whether you think I'm the original author of the PK template - I'm not. Just this modified version and one earlier version for AMC (of course you could be referring to the latter as the one you've been using).
1) I've never had to deal with asian characters, but I have no trouble with special characters from e.g. Turkish or Icelandic. Just make sure the exported sql file is saved as unicode, and that the database is also unicode.
Or are you referring to text in the template itself, like headers?
2) I've only recently noticed this as a problem, I don't think it used to be like that. Maybe something changed at imdb lately? It is certainly annoying, and I will try to dig up the fix from the AMC thread.
3) I currently use only one custom field, "Ripper". Refers to the ripper tag of course. All the fields actually used by the template are listed in the "Fields.sql" file in one of the zip files.
About the people pages suggestion, would that be something similar to people view in pvd?
I should say that I actually suck at php coding too. I'm not alienated by it though so I can usually make minor changes or fixes. Not sure I will be able to code new functionality from scratch though.
-
thank you for your fast answer. I DID think you were the original author :)
My AMC database has about 1800 entries, but I have some problems with the content, and Im starting a complete new database. It might take a while, but I don't get much sleep anyway :P
I hope you will correct the problem with the countries.
I also hope (yeah I know, i ask a lot, but it is just suggestions), that you will include screenshots in your next version.
That's a great work you're doing, Im sure a lot of people is following your work :)
-
My AMC database has about 1800 entries, but I have some problems with the content, and Im starting a complete new database. It might take a while, but I don't get much sleep anyway Tongue
In case you're not aware... there's an AMC import plugin (http://www.videodb.info/bin/plugins/amc.zip). You should be able to import just the parts you don't have a problem with—or at least the titles and years if you're planning to rebuild your database.
-
If I have a Windows Home Server box, how hard would it be to set this up?
-
That depends, if you have apache+mysql+php running on your server and know what these do, you shouldn't have too much trouble. I think my description is pretty thorough.
The files I've made available is my unedited template, it won't work perfectly out of the box since your PVD database most probably isn't structured excactly like mine. Be prepared for a little work correcting for those differences.
-
Blue, hope u can help
I've been trying to get ur script to work but when i export using the sql script the file only gives me the following
INSERT INTO movies (NUM, RIPPER, DATEADDED, IMDBRATING, ORATING, RATING, ORIGTITLE, TITLE, DIRECTOR, PRODUCER, COUNTRY, GENRE, YEAR, LENGTH, ACTORS, URL, DESCRIPTION, COMMENTS, VIDEOCODEC, VIDEOBITRATE, AUDIOCODEC, AUDIOBITRATE, RESOLUTION, FRAMERATE, SUBS, SIZE, VIEWDATE, TAGS, POSTER) VALUES ('0', '', '{%value=dateadded}', '{%value=imdbrating}', '{%value=orating}', '{%value=rating}', '{%value=origtitle}', '{%value=title}', '{%value=directors}', '{%value=producers}', '{%value=country}', '{%value=genre}', '{%value=year}', '{%value=length}', '{%value=actors template="pk_actors.ptm" params="full"}', '{%value=url}', '{%value=description}', '{%value=comment}', '{%value=videocodec}', '{%value=videobitrate}', '{%value=astreams template="pk_audiocodec.ptm"}', '{%value=astreams template="pk_audiobitrate.ptm"}', '{%value=resolution}', '{%value=framerate}', '{%value=subs}', '{%value=size}', '{%value=viewdate}', '{%value=tags}', '{%value=poster}');
etc...
It doesn't actually output any information
I'm using pkdb for 0.9.9.8 - website files & templates - sql scripts
The program is v 0.9.9.8
-
Hi
Try deleting [RIPPER,] and ['{%value=Ripper custom="true"}',] in the pk_sql.ptm file.
RIPPER is a custom field in my database.
-
I thought I had done everything correct but when I try to access the PVD from the web I get this error;
* @copyright Copyright © 2006-2007, Michael Shepanski * @package PK-Designs * @version Version 1.0 (2007.01.06) * =============================================================================================== */ require_once('mdb3.php'); $moviedb = new MDB3(); ?>
get_html(); ?>
Hope you can help me with where I went wrong..
thanks
Jay
-
Thank you VERY much for an excellent script and documentation.
Rather than using the "processperiod.php" script, perhaps the following SQL statement would be more beneficial:
UPDATE movies a SET a.PERIOD = CASE
WHEN a.YEAR >= 2050 THEN '2050-9999' WHEN a.YEAR >= 2045 THEN '2045-2049'
WHEN a.YEAR >= 2040 THEN '2040-2044' WHEN a.YEAR >= 2035 THEN '2035-2039'
WHEN a.YEAR >= 2030 THEN '2030-2034' WHEN a.YEAR >= 2025 THEN '2025-2029'
WHEN a.YEAR >= 2020 THEN '2020-2024' WHEN a.YEAR >= 2015 THEN '2015-2019'
WHEN a.YEAR >= 2010 THEN '2010-2014' WHEN a.YEAR >= 2005 THEN '2005-2009'
WHEN a.YEAR >= 2000 THEN '2000-2004' WHEN a.YEAR >= 1990 THEN '1990-1999'
WHEN a.YEAR >= 1980 THEN '1980-1989' WHEN a.YEAR >= 1970 THEN '1970-1979'
WHEN a.YEAR >= 1960 THEN '1960-1969' WHEN a.YEAR >= 1950 THEN '1950-1959'
WHEN a.YEAR >= 1940 THEN '1940-1949' WHEN a.YEAR >= 1930 THEN '1930-1939'
WHEN a.YEAR >= 1888 THEN '1888-1889' ELSE 'Unknown' END;
The statement assumes the PERIOD column has already been added to the database. So, one should add the column ("PERIOD VARCHAR(15)") to the table creation script prior to creating the table, or manually alter the table by adding the column.
Also note that I changed the period descriptions slightly so as to not create overlapping descriptions. For example, the original php script creates periods 1930-1940 and 1940-1950. Using these descriptions, one is not sure if the year 1940 is in 1930-1940 or 1940-1950. The period descriptions above will create the periods as 1930-1939 and 1940-1949; which clearly shows that 1940 is part of the 1940-1949 period.
One other note. If the records in the movie table are deleted prior to importing, there is no need to drop and re-add the table. Adding the following SQL statement to the top of the export script will accomplish this:
DELETE FROM movies;
With all of this in mind, I have attached an updated export script that adds the delete and update statements in the appropriate spots. When used as the export template from PVD, the resulting SQL script file can be used to delete all existing records in the movies table, add all of the exported movies from PVD, and finally set the PERIOD on all of the imported records. Of course, your previous comments of removing all occurrences of "images/" and ensuring a valid format for the date columns in the SQL script file prior to running it are still applicable.
[attachment deleted by admin]
-
Once again, thank you for this great template.
In your instructions, you mention resizing images to 200px. In the CSS, the images will be forced to 200x284 dimension. Of course, this will cause images that are not in a 200x284 aspect ration to be squashed or stretched.
I ended up resizing my images to a maximum of Width=200px and a maximum height of 284px. I use ImageMagick's command line tool "mogrify.exe". Basically, the command line is:
mogrify -resize "200x284" *.jpg
Rather than monkeying with the CSS, I then changed the poster HTML IMG tags to include a style="width:auto;height:auto;". I believe this allows the intent of bounding the display to 200x284 (as the images have already been re-sized), while preserving the image's original aspect ratio.
Thank you again for sharing this template.
-
I've been messing with this script once again (http://botez.byethost9.com/index.php) and am trying to find a way to sort by last names for fields like Director, Actor, etc. Currently, you can only sort by the first letter of the first name.
I figure there has to be a way to do this, either via a SQL command on the existing database or some kind of regular expression on the export out of PVD. Or perhaps it all has to be done in the PHP, like the "period" function from a few posts above.
Does anyone have any ideas on this?
-
It will be difficult to sort by last name when the database keeps the entire name in a single field. Without first separating surnames from given names manually, you can't really tell where the last name starts. There's also an issue with groups of people (bands, comedy groups, whatever) being listed in the database.
Examples follow:
Lee Van Cleef
Anthony Michael Frederick
Miou-Miou
Bjork
4 Out of 5 Doctors (a band)
Ana Lúcia da Rocha Franco
-
Thanks mgpw4me. That makes perfect sense. I'll just let that one go for now :)
Two other things I'd love to get some tips on, both regarding side panels:
1) First off, I want to get them to sort by count, instead of by name. Thus a director panel would be ordered by number of movies in my database instead of alphabetically. I swear this should be a simple tweak of the panel template code and how the keys/values are sorted, but I've yet to be able to get it to work.
EDIT: I finally figured this one out -- needed to edit the mdp_panel.inc and change the following line in the sort_categories function:
$keys = array_keys($categories);
sort($categories);
to
ksort($categories);
arsort($categories);
$keys = array_keys($categories);
I then create a second function and a second class that allows the panel to be sorted this way. Yippee.
2) When you select a subset of data, say showing all the films that have the genre of "horror", the side panel for directors stays with the same set of names that it has for the entire movie collection. So, if I have 10 James Cameron films in my database and then select "horror", instead of showing me the directors with the most horror movies, it would just keep Cameron on there with a "0" next to his name, since he has no horror movies. You can click on my database if this makes no sense (http://botez.byethost9.com/index.php?genre=Horror) to see what I'm talking about.
I'd like to find a way to be able to get the side panel to update when you drill down into genres/years/etc.
Thanks for any help anyone can provide.
-
How we can block guests? We have spam here.
-
How we can block guests? We have spam here.
Thanks for the warning, I didn't see it, I would have removed it earlier, so I removed it now. Obviously, as an admin, I will have to disable guests from continuing to post on the forum.
-
Obviously, as an admin, I will have to disable guests from continuing to post on the forum.
Done.
-
Thank you. I was deleting earlier spam posts, that's why you didn't notice them.