SitePoint Sponsor

User Tag List

Page 3 of 3 FirstFirst 123
Results 51 to 61 of 61
  1. #51
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, I saw the manual, but it was left lacking...

    Thanks.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  2. #52
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again, I had to redo the structure of my database a bit and added fulltext searches. Everything seems to work, except that now I need to query for a specific actor and still have it come back with the list of all actors for that movie. So I have a query which works for the actor as such:

    Code MySQL:
    SELECT dvdpedia.id, dvdpedia.title, actor, d.director,  MATCH(actor) AGAINST('$actor' IN BOOLEAN MODE) AS relevance,
     
    							FROM actor 
    								INNER JOIN actor2title ON actor.id = actor2title.pId 
    								INNER JOIN dvdpedia ON actor2title.titleId = dvdpedia.id 
     
    						LEFT OUTER JOIN ( SELECT director2title.`titleId`, director
    									FROM director
    									INNER JOIN director2title
    										ON director.id = director2title.pid
    										GROUP BY director2title.titleId ) AS d
    						ON d.titleId = dvdpedia.id
     
    						WHERE MATCH(actor) AGAINST('$actor' IN BOOLEAN MODE) 
    						ORDER BY relevance DESC

    So I'm getting the correct info, but just that one actor, I need to also get a list of all actors that are associated with that title. So I amended it to this:

    Code MySQL:
    SELECT dvdpedia.id, dvdpedia.title, d.director,  
    	MATCH(actor) AGAINST('$actor' IN BOOLEAN MODE) AS relevance
    	, a.cast
    							FROM actor 
    								INNER JOIN actor2title ON actor.id = actor2title.pId 
    								INNER JOIN dvdpedia ON actor2title.titleId = dvdpedia.id 
     
    						LEFT OUTER JOIN ( SELECT director2title.titleId, director
    									FROM director
    									INNER JOIN director2title
    										ON director.id = director2title.pid
    										GROUP BY director2title.titleId ) AS d
    						ON d.titleId = dvdpedia.id
     
    						INNER JOIN ( SELECT actor2title.titleId, 
    	    				GROUP_CONCAT(actor SEPARATOR ', ') AS cast 
    	    			FROM actor 
    		    		INNER JOIN actor2title 
    		    			ON actor.id = actor2title.pId 
    		    			GROUP BY actor2title.titleId) AS a 
    	    ON a.titleId = dvdpedia.id
     
    							WHERE MATCH(actor) AGAINST('$actor' IN BOOLEAN MODE) 
    							ORDER BY relevance DESC


    Then I have a table that is joining to itself which I'm sure is not good practice and it takes about 325 ms which is long. Apart from running 2 queries, one with the IDs of the other, I'm not sure how else to do it.

    Thanks.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  3. #53
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies, but I've had to change the table structure and the above query no longer applies.

    I now have no junction table, but have added the role column to the actors table so this table will have a titleId column which corresponds to which movie the actor is in.

    Code MySQL:
    CREATE TABLE `actor` (
      `id` int(11) NOT NULL auto_increment,
      `titleId` int(11) default NULL,
      `actor` varchar(255) NOT NULL default '',
      `role` varchar(255) default NULL,
      PRIMARY KEY  (`id`),
      FULLTEXT KEY `actor` (`actor`)
    ) ENGINE=MyISAM AUTO_INCREMENT=232 DEFAULT CHARSET=utf8

    So now I need the same thing, a list of actors and their roles for each movie, along with the movie's info. I have this query:

    Code MySQL:
    SELECT dvdpedia.id
         , dvdpedia.title
         , a.cast
          FROM dvdpedia
    LEFT OUTER JOIN ( SELECT actor.titleId, 
        				GROUP_CONCAT(actor SEPARATOR ', ') AS cast 
        			FROM actor) AS a 
        ON a.titleId = dvdpedia.id
        WHERE 0=0
    ;

    which puts all actors for all movies into the cast column of a single entry.

    What am I doing wrong?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  4. #54
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    I now have no junction table
    actually, you do

    presumably this new `actor` table has multiple rows for each actor, one for each movie the actor is in, yes?

    that's a junction table

    and do you really want to allow the actor's name to be used to identify the actor? what happens if it is misspelled on one of the several rows? loss of data integrity

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #55
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm confused, my understanding of a junction table is a third table that manages the relationship between 2 other tables. In this table, I do have multiple rows for each actor, one for each movie they're in, but each distinct row corresponds to one and only one title in the dvdpedia table, there is no third table managing the relationship.

    Also, I don't understand what you mean by this:

    and do you really want to allow the actor's name to be used to identify the actor?
    The actors are identified by an id in their table. What am I missing?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  6. #56
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    I'm confused, my understanding of a junction table is a third table that manages the relationship between 2 other tables. In this table, I do have multiple rows for each actor, one for each movie they're in, but each distinct row corresponds to one and only one title in the dvdpedia table, there is no third table managing the relationship.
    but that table ~is~ the thrid table, between actors and movies


    Quote Originally Posted by pata View Post
    The actors are identified by an id in their table. What am I missing?
    you're not using the id, though --

    `actor` VARCHAR(255) NOT NULL DEFAULT '',
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #57
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just the fact that that column cannot be empty, automatically negates the id column???

    Even though I've got this:

    `id` INT(11) NOT NULL AUTO_INCREMENT,

    but that table ~is~ the thrid table, between actors and movies
    it's the second table where the actors are all stored.

    It's 2 tables with a many-to-one relationship as far as I can tell. There's something I'm not getting here.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  8. #58
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    There's something I'm not getting here.
    imagine how we feel -- we don't know what you're trying to accomplish with the different relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #59
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello, I'm coming back to a fulltext issue I'm having:

    If I search the actor table for actor and role (2 fields in the table). When the role field is empty, I'm getting no results with a fulltext search.

    Sometimes this column WILL be empty. How can I get results for this combination? It seems odd that if the column is empty, there wouldn't be a match no? I mean, I do get a result using a similar query with LIKE instead of fulltext search. I haven't seen anything about this in the manual.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  10. #60
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Back again to the table structure because I am unsure how to handle something.

    I have the main table, with a junction table for director and another table for actor which has one row for each actor and role and a foreign key for the main table to link them.

    I am doing a keyword search, where the keyword can be either title (dvdpedia table), director (director table via the junction table), or actor (actor table). I don't know what people will be looking for, could be any of these three things.

    So this works for matching title and director:
    Code MySQL:
    SELECT DISTINCT dvdpedia.id
            , dvdpedia.title
            , locale
            , d.director
            FROM dvdpedia 
     
            LEFT OUTER JOIN ( SELECT director2title.titleId,
                                GROUP_CONCAT(director SEPARATOR ', ') AS director
                                FROM director
                                INNER JOIN director2title
                                    ON director.id = director2title.pid
                                    GROUP BY director2title.titleId ) AS d
                ON d.titleId = dvdpedia.id
     
     
    WHERE MATCH(title) AGAINST(:title IN BOOLEAN MODE)
    OR d.director LIKE :director";

    :title and :director are placeholder variables

    However, I'm unsure how to match also actors. Since the actor table does not work through a junction table, I can't do the same trick. I tried doing a
    Code MySQL:
    WHERE dvdpedia.id IN (SELECT titleId FROM actor WHERE MATCH(title) AGAINST(:actor IN BOOLEAN MODE))

    Which works, but takes an obscene amount of time to run. So I'm hoping there is a much better way to do this.

    As always, thanks in advance.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  11. #61
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello and Happy New Year to everyone.

    I had to make another change to this structure because for tv series the actor would be repeated hundreds of times with the same role. So now I thought of having 3 tables:

    1 for actor name
    1 for role with a key for the actor
    and a junction table to tie it all together.

    The problem is that I now have a junction table to tie in 3 tables (actor, dvdpedia, role) and am having some problems with it.

    The simplest query works but returns repeated columns:

    Code MySQL:
    select dvdpedia.title, actor.actor, role.role FROM dvdpedia, actor, actor2role, role
    		WHERE actor.id = actor2role.actorId and dvdpedia.id = actor2role.titleId;

    What would be the proper query here?

    PS
    This is the create tables syntax:

    Code MySQL:
    CREATE TABLE `actor` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `titleId` int(11) NOT NULL,
      `actor` varchar(255) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `actor2role` (
      `actorId` int(11) NOT NULL DEFAULT '0',
      `titleId` int(11) NOT NULL DEFAULT '0',
      `roleId` int(11) NOT NULL,
      PRIMARY KEY (`actorId`,`titleId`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `role` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `actorId` int(11) NOT NULL,
      `role` text,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •