Maybe this is something to add to the documentation.
HOWTO Use a Extra Info field in a custom Query.
Needed (to make life easy):
PhpMyAdmin. (Recent enough so you have support for relations. Follow the installation manual to get it going. You will need a webserver (apache++) & php for this.)
Introduction:
I added this Extra info field to set stuff as "wanted". (When you have an incomplete series and waiting for a release for example)
And then I needed to have some query that would show me what stuff that is missing.
In this short howto I'll only discuss how to get wanted episodes. You should rewrite the query for "Just Movies". (Maybe I'll try to do both in 1 query... But that's uncertain).
To make my life easy, I want a list of the name of the series, episode number and name of everything marked as wanted.
First step: Add the Additional Field
You can add the field by opening the Movie Manager, in the menu Database select Additional Info Fields. Add new field at the bottom, "Wanted" in my case, and add it.
Second step: Creating the custom query.
We will be using phpmyadmin to build up this query. Get a browser running and go to the phpmyadmin site you have hosted. Open your database for the movie manager. Mine is called "xmm" so I'll be using this from now on.
Select the database.
You'll see you have some tables in here.
Open the first table:
Additional_Info
Make sure you have opened the tables Structure. When you've set phpmyadmin up correctly, there is a link "Relation View" at the bottom. *Click*
You get a list of all Fields in this table. Map the field "ID" to "General_Info->ID".
Do the same for the other tables:
* Additional_Info_Episodes
"ID" mapped to "General_Info_Episodes->ID"
* Extra_Info
"ID" mapped to "General_Info->ID"
* Extra_Info_Episodes
"ID" mapped to "General_Info_Episodes->ID"
* General_Info_Episodes
"movieID" mapped to "General_Info->ID"
Now go back to the database overview and click Query.
First of all, select the tables we will be using left at the bottom under "Use Tables":
Extra_Info_Episodes, General_Info, General_Info_Episodes, and click "Update Query".
Go to the first Field (first column) and select `General_Info`.`Title`. Check the box in the "Show"-row.
The second and third field to be selected are:
`General_Info_Episodes`.`episodeNr` and `General_Info_Episodes`.`Title`. Also set the Show-checkbox for them. Now we are short 1 column. Select Add/Delete Field Columns and set it to 1. *Click Update Query*.
In the new Column, select `Extra_Info_Episodes`.`Wanted` and in the textfield in the "Criteria"-row. Put "=1".
Now click "Update Query" once again.
It seems we are ready for the test now.
Click "Submit Query". If everything goes well You'll get the Name of the Series, Episode Number and name of the episode you still want.
Now copy the query you have used. Copy it in a file WantedEpisodes.qry and put it in your Query Folder.
Ready!
------
I don't know if you find this usefull. Use it as you see fit. The layout of this HOwto... There is no layout. It's hard typing it in a textarea this small :)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Maybe this is something to add to the documentation.
HOWTO Use a Extra Info field in a custom Query.
Needed (to make life easy):
PhpMyAdmin. (Recent enough so you have support for relations. Follow the installation manual to get it going. You will need a webserver (apache++) & php for this.)
Introduction:
I added this Extra info field to set stuff as "wanted". (When you have an incomplete series and waiting for a release for example)
And then I needed to have some query that would show me what stuff that is missing.
In this short howto I'll only discuss how to get wanted episodes. You should rewrite the query for "Just Movies". (Maybe I'll try to do both in 1 query... But that's uncertain).
To make my life easy, I want a list of the name of the series, episode number and name of everything marked as wanted.
First step: Add the Additional Field
You can add the field by opening the Movie Manager, in the menu Database select Additional Info Fields. Add new field at the bottom, "Wanted" in my case, and add it.
Second step: Creating the custom query.
We will be using phpmyadmin to build up this query. Get a browser running and go to the phpmyadmin site you have hosted. Open your database for the movie manager. Mine is called "xmm" so I'll be using this from now on.
Select the database.
You'll see you have some tables in here.
Open the first table:
Additional_Info
Make sure you have opened the tables Structure. When you've set phpmyadmin up correctly, there is a link "Relation View" at the bottom. *Click*
You get a list of all Fields in this table. Map the field "ID" to "General_Info->ID".
Do the same for the other tables:
* Additional_Info_Episodes
"ID" mapped to "General_Info_Episodes->ID"
* Extra_Info
"ID" mapped to "General_Info->ID"
* Extra_Info_Episodes
"ID" mapped to "General_Info_Episodes->ID"
* General_Info_Episodes
"movieID" mapped to "General_Info->ID"
Now go back to the database overview and click Query.
First of all, select the tables we will be using left at the bottom under "Use Tables":
Extra_Info_Episodes, General_Info, General_Info_Episodes, and click "Update Query".
Go to the first Field (first column) and select `General_Info`.`Title`. Check the box in the "Show"-row.
The second and third field to be selected are:
`General_Info_Episodes`.`episodeNr` and `General_Info_Episodes`.`Title`. Also set the Show-checkbox for them. Now we are short 1 column. Select Add/Delete Field Columns and set it to 1. *Click Update Query*.
In the new Column, select `Extra_Info_Episodes`.`Wanted` and in the textfield in the "Criteria"-row. Put "=1".
Now click "Update Query" once again.
It seems we are ready for the test now.
Click "Submit Query". If everything goes well You'll get the Name of the Series, Episode Number and name of the episode you still want.
Now copy the query you have used. Copy it in a file WantedEpisodes.qry and put it in your Query Folder.
Ready!
------
I don't know if you find this usefull. Use it as you see fit. The layout of this HOwto... There is no layout. It's hard typing it in a textarea this small :)
Hi
Maybe someone can try this out and see if they get through without too much trouble...
I'll try to find some time to check it out myself.
Thanks alot for your interest in this!
Bro