This is one of those questions that should be simple and straightforward. I am just not sure how to implement.
Here is what is working currently:
I have an app that adds, updates, deletes, and plays video clips from a mySQL database, specifically using the "videoclips" table. Each video has a unique ID (videoID). Each video belongs to one of two categories: (videocategory) "Youtube Video", or "Yahoo Video". I also have a table named "video_category" with a primary id of "videocategoryID" I use the "video_category" table for a cfselect form input to populate the "videocategory" field of the "videoclips" table.
This all works nicely, but here is one of my hangups and the first part of my question: I am not sure if I should create and populate a videocategoryID field in the "videoclips" table, and I don't know the best way to setup and match the videocategoryID in the "videocategory"table. My database is currently a "myISAM". Should I be using "innoDB" and foreign keys to match up tables, and will this solve the problem of populating the videocategoryID in two separate tables? Yes I know this seems more SQL related, but please read on.
There are two different directions I am considering and need help implementing:
1. I would like to know how to select and pass mutiple videos belonging to a specific category and display them on a page named for that category "youtubvideo.cfm", or "yahoovideo.cfm".
or this option:
2. I would like to add a formfield on the same page as the videos table where I can select the category and post back to the same page with the results, i.e. the specific category videos.
I am generating bean, DAO, and Gateway cfc's from the wizard. Using the gateway method, I am able to display all of the videos, and select a specific video to play. Here are snippets of the page:
<!---player defaults to first clip in the database--->
<cfparam name="url.videoid" default="1">
<!---gets all videoclips--->
qVideoclips = CreateObject("component", "myvideoapp.Components.VideoclipsGateway").getAllAsQuery();
<!---gets all videocategories--->
<!---gets the specific video selected from clicking the Details link--->
<cfset videoComp=CreateObject("component", "myvideoapp.Components.videoclipsGateway")>
<cfelse> No data matches this query
<!---table that displays the videoclips--->
<!---When details link is selected, the page refreshes with the selected videoID ready to play--->
<!---below is the category selection form - I would like this to post back to this page with the above table showing videos from the selected category--->
<label class="top" for="videocategory">Video Category</label>
<cfselect name="videocategory" id="videocategory" query="qVideoCategory" value="videocategoryname" display="videocategoryname" selected="#video.getvideocategory()#"></cfselect>
<cfinput type="hidden" name="videocategoryname" value="#video.getvideocategory()#" validateat="onSubmit">
<cfinput type="submit" name="Submit" class="submit" id="Submit" value="Submit">
Keep in mind, I don't have a category ID yet.
So in summary,
- I want to display only the videos from a selected category.
- I need to know if I can pass a string, which means I could use one table and pass the categoryname; I just don't know how.
- If I can only pass "videocategoryID" I would need to create and populate a field in the "videoclips" table called videocategoryID.
- I already have a table called video_category table. How can I incorporate it to get the field populated in the "videoclips" table?
I hope this is clear. Help would be greatly appreciated!