SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Enthusiast Lazarus's Avatar
    Join Date
    Aug 1999
    Location
    Singapore
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How would you database movie reviews?

    Hi SitePointForum members,

    I'm new to the PHP/MySQL scene and I truly need your help. I have read Kevin's excellent tutorial on databasing one's site with PHP and MySQL and intend to get his book when it becomes available.

    I owned a movie website located at http://www.moviem.com/ and am having trouble in thinking how to database the site in a well-designed database. It seems that I have lots of elements that need 'databasing' like title, reviewer, the review itself, cast and crew etc. How should I go about doing this?

    After entering all the information, should I use variables (review.php?title=pearlhabor)? Or should I have individual pages (pearlhabor.php) and use PHP tags to extract information from the database into the page itself?

    Which method is most efficient yet advantageous? Are there other methods?

    Thanks for all your help.
    Lazarus (8946037)
    http://www.moviem.com

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would go for the first option using variables and templates, in which you parse the values of the variables.

    As for the database, you could do everything in one table. Make a row for every review, and have the columns (which you set when you create the table) refer to the title, director etc.

    Have you already read Kevin Yank's tutorial on building a database driven website. It is a real good teacher on both PHP and MySQL, and also teaches you a lot on how to handle databasing a site like yours. It is a must read for every PHP-programmer, check it out here...

  3. #3
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    None
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I completely agree with Mark.
    One thing though, I'd use review.php?id=1 (being ID - unique and auto_increment) rather than review.php?title=pearlhabor as you might have several movies with the same title.

  4. #4
    SitePoint Enthusiast Lazarus's Avatar
    Join Date
    Aug 1999
    Location
    Singapore
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I were to use variables, the search engines will not be able to index the pages. Am I correct?

    However, wouldn't that be disadvantageous?

    What other methods are there? I've seen sites that have PHP pages, however, they do not use an ID to identify the title.

    How is it possible to use PHP in a webpage, without using identifying variables, and still be an effective measure?
    Lazarus (8946037)
    http://www.moviem.com

  5. #5
    SitePoint Guru
    Join Date
    Sep 2001
    Location
    Vancouver
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can very easily replace the "?" sign in a URL with a simple slash (/), so that search engines (most) can crawl your site. Several lines of code, that's it.

  6. #6
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as for the database design I would recomend using 3 tables

    movies(mid, movie_name, cast, crew, ect.....)
    reviews(rid, name_of_reviewer, review)
    mid_rid(mid, rid)

    this way u can have multiple reviews for one movie..

  7. #7
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    None
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Search engines are able to index PHP pages with variables. For example - somepage.php?id=1

  8. #8
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually, most cant... they wont read anything after the ?

    I knwo google does though..not sure if there are any others

  9. #9
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    None
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Google, Yahoo! (Well, it's still google), AltaVista and GoTo.com can. That's for sure.

  10. #10
    SitePoint Guru
    Join Date
    Sep 2001
    Location
    Vancouver
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, but why not convert that "?" sign to a slash and then even larger amount of search engines would be able to crawl your pages. Not all, but certainly more.

  11. #11
    SitePoint Enthusiast Lazarus's Avatar
    Join Date
    Aug 1999
    Location
    Singapore
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm clueless. How do I change the "?" to a "/"?
    Lazarus (8946037)
    http://www.moviem.com

  12. #12
    SitePoint Guru
    Join Date
    Sep 2001
    Location
    Vancouver
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Put this above all your code, should be the very first thing on the page:
    Code:
    <?php
    $url_array = explode("/",$PATH_INFO); 
    for($i=1;$i<count($url_array);$i++) { 
    $data[$url_array[$i]] = $url_array[++$i]; 
    } 
    $extract = @extract($data);
    ?>
    Then if you have a URL like this:
    Simply change that to:
    Get it? It's easy. Just make sure you have that code on the very top of your page(s).

  13. #13
    SitePoint Enthusiast Lazarus's Avatar
    Join Date
    Aug 1999
    Location
    Singapore
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Darin.

    (I think I'm getting annoying)
    But I have another question.


    For both of the URLs below, they point to the same movie. However, how is the "movie-1101854" done?
    Is this "movie-1101854" the review-id in their database?

    http://www.rottentomatoes.com/movies...ills/index.php

    http://www.rottentomatoes.com/movie-1101854/
    Lazarus (8946037)
    http://www.moviem.com

  14. #14
    SitePoint Guru
    Join Date
    Sep 2001
    Location
    Vancouver
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're welcome! I'm not really sure about that URL. They could be calling a table called movie, and the ID could be 1101854, but like I said, I'm not sure at all.

    Could be just a directory which re-directs to the other page...

  15. #15
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's what I'd have.

    users (uid, name, email, whatever)
    movies (mid, name, description, etc)
    reviews (rid, movie, author, review)

  16. #16
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would separate the director,cast info into separate tables.

    Bob Ross may appear in 30 different movies -- that's wasting a lot of space to enter his name in 30 rows.

    Something like:
    cast( castid_pk, first_name, ... );
    director( directorid_pk, first_name, ... );
    movie_cast( movieid_fk, castid_fk );
    movie( movieid_pk, ... directorid_fk );

  17. #17
    SitePoint Enthusiast Lazarus's Avatar
    Join Date
    Aug 1999
    Location
    Singapore
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Matt, I'm afraid I'll have to disagree with you to some extent. If I were to enter Bob Ross in all this movies, I can see which movies he has been in.

    Personally, I find creating a row for every celebrity is a chore.

    What I've done is this. Tell me what you all think.

    movie (mid, rid, title, rating, review, cast, director, producer etc.)
    reviewer(rid, name, email, country)

    Finally, how do I add a whole review into a column? How do I have paragraphing?
    Lazarus (8946037)
    http://www.moviem.com

  18. #18
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Lazarus
    Matt, I'm afraid I'll have to disagree with you to some extent. If I were to enter Bob Ross in all this movies, I can see which movies he has been in.
    Well if you do the lookup table you can easily find out all the movies bob ross has been in:
    Code:
    SELECT movietitle
      FROM movie_cast,
           movie
     WHERE movie_cast.castid_fk  = {Bob's ID}
       AND movie_cast.movieid_fk = movie.movieid_pk
    That would output all movies Bob Ross has starred in. Or you can do it that way; just giving a suggestion.

    Originally posted by Lazarus
    Personally, I find creating a row for every celebrity is a chore.
    Yep; in the long run it would be better though (you can have links on all cast members to see movies they have been in for example). Really you could take it a movie at a time; use the cast as already entered into the DB, if they are not in there add them at that time.

    Originally posted by Lazarus
    Finally, how do I add a whole review into a column? How do I have paragraphing?
    There was a topic on just that a while ago. I can't remember where 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
  •