SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    139
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Starting php/mysql site, need advice

    hi guys, just read through 'build your own database..' book and admittedly got a little overwhelmed around chapter 7.

    so, purely for my own practice and to try and hammer this stuff into my brain, I am going to make a little private site that allows users to register and make lists of movies they own.

    when they register I want their own directory created i.e site.com/user/ and that will be their homepage to show information about the movies they own.

    but to get me started I am trying to figure out the tables I will need to get me started.

    CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
    username VARCHAR(55),
    email VARCHAR(55),
    password CHAR(55)
    ) DEFAULT CHARACTER SET utf8;

    so that would be the table to store my userbase in - does it look ok for that purpose? I want them to be able to login with either: user+pass or email+pass.

    where I'm really having trouble is figuring out what kind of table I would use to store a list of movies.

    I want to have a pre-made list of movies that users will be able to select from and add to their 'owned' list that will be shown on their personal page.

    so how to make that table of pre-made movies? hmm.

    CREATE TABLE movies (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
    format VARCHAR(255),
    movie VARCHAR(255),
    genre VARCHAR(255),
    region VARCHAR(255)
    ) DEFAULT CHARACTER SET utf8;

    does anyone have any tips regarding that one? I'm probably missing something regarding the format of the movie table, for the purposes of ease-of-linking to other tables etc.. but that's what I want advice on..

    thanks for any help

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your movies table looks fine

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

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    You would want to have a "joining table" called something like movie_ownership recording in each row a user id and the id of a movie.

    The region field only needs to be 1 character long if it is refering to the regions that DVD releases are split up into.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    139
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok thank you very much guys.

    I will also re-read the section in the book that goes over joining.


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
  •