SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jun 2001
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help sort these results (complex)

    I need a little help trying to get my results sorted.

    I have two tables that kind of depend on eachother

    a movie table with the fields:
    movieid
    titleid
    other fields that don't affect this problem

    and a title table with the fields:
    titleid
    movieid
    title

    the logic behind this is there's one entry in the movie table for each movie in the database, since a movie could have any number of alternate titles, the title table lists all the titles for the movie and the titleid is inserted back into the movie table so I can find out which title is the original one.

    Having two tables depend on eachother seem kind of awkward to me, but I can't figure out a better way. I'm open to suggestions.

    What I basically want is the search results to be in order by the original title, but list all the alternate titles that contain the search word also.

    I don't have my work accessible at the moment, but basically what I am doing is

    1) query all the movieids from the title table that contain the search word

    2) go through a loop where I query each movieid from movie and title where titleid matches in both, and another query for the opposite (the ones where titleid doesn't match)

    This works for getting the results, but there is no opportunity to sort on the original titles in this method.

    Is there any way of getting everything sorted by the original title short of storing the entire search in an array and then sorting that?

    Thanks for any help.

  2. #2
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I basically want is the search results to be in order by the original title, but list all the alternate titles that contain the search word also.
    How do we know which title is "original"?

    1) query all the movieids from the title table that contain the search word
    This comment seems to indicate that the `movieid' field contains string values. Can you show us an example of what a database entry might look like?

    Is there any way of getting everything sorted by the original title short of storing the entire search in an array and then sorting that?
    Again, we don't know which title should be considered "original." If originality is based on the `titleid' value, where the lower value indicates more originality, then you could add the following at the end of your SQL statement:
    Code:
    ORDER BY titleid
    This will sort the results of your SQL query by `titleid' so that the records with lower values are first and the records with higher values are last.

    - Marshall

  3. #3
    SitePoint Member
    Join Date
    Jun 2001
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for attempting to help, I guess I wasn't clear enough.

    The original title for the movie with "movieid" is the one one that corresponds to "titleid" in the movie table.

    example:
    movie table
    movieid | titleid
    101 | 321

    title table
    titleid | movieid | title
    320 | 101 | title1
    321 | 101 | title2
    323 | 101 | title3

    for the above, the movie with movieid=101 has 3 titles (title1, title2, title3) and the original title is title2 since that titleid is in the movie table.

    But I basically solved the problem.

    What I do is query for all the movieids that match the search string, then use a loop to builder a string:
    $movieids = "movieid=100 or movieid=101 or movieid=102" etc.

    then with that string I can query all the movies for movie.titleid=title.titleid and use the "order by" to sort them.

    It doesn't seem like the most efficient way, but it takes about the same amount of steps as my previous version, but this time everything is in the order I want.

    Thanks for the reply though


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
  •