0 Replies Latest reply on Sep 14, 2009 11:05 AM by MPe-commerce

    Best practice calling only records belonging to a category


      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--->

          qVideoCategory=CreateObject("component", "myvideoapp.Components.video_categoryGateway").getAllAsQuery();

      <!---gets the specific video selected from clicking the Details link--->

      <cfset videoComp=CreateObject("component", "myvideoapp.Components.videoclipsGateway")>

      <cfif isdefined("url.videoID")>
      <cfset video=videoComp.getById(url.videoID)>
      <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--->

      <cfoutput query="qVideoclips">
                      <td><a href="videopage.cfm?videoID=#qVideoclips.videoID#">Details</a></td>


      <!---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--->


      <fieldset id="cflayoutleft">
              <legend id="cflayoutleftLegend">Categories</legend>
                <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">
              <div class="submit">
                <cfinput type="submit" name="Submit" class="submit" id="Submit" value="Submit">


      <!---video player--->



      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!




      Marty P

      MP e-commerce