SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    New To PHP and MYSQL

    Hey guys im new to databases so take it easy please! But basically its an important aspect of the website i am developing so its something i have to learn. A summary of what im looking for from the database, (although i know i have alot to learn) the database must be able to:
    allow users to register/login
    provide the user their own space
    allow users to upload pictures and also delete pictures
    allow users to comment and also thumbs up other users pictures
    allow users to post threads
    allow users to reply to these threads

    I know its alot how much will be solved by creating a database, and can anyone point me in the right direction please?

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, Most of what you have described is to be done by your server side language. the database is simply a place to store the data.

    It may help if you read up on:

    database normalisation
    entity diagrams
    referential integrity
    foreign keys.


    I started by listing on paper each pice of data I needed to store. then I grouped them which meant that user details like user_name, pwd, displayed name etc were in one table. address in another. a table for photos which uses the usernames foreign key, to connect/relate them. you'll also need a set of tables, maybe two maybe three, for storing a thread title, then the thread content and then the thread responses, where each table has a value from another, to relate the records.

    Have you tried building anything yet?

  3. #3
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow thanks for your reply its really really helpful in getting me started ie the listing of what i need for example. But no I have never built anything yet but hope to make this my first project, any tutorials or extra help will be much appreciated, i know im not going to pick this up in a day or 2

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    My general database design steps are three:

    1: Identify Entities (User, Picture, Comment, etc) [These will be your main tables]
    2: Identify Properties of the Entities (User's Name, Email, etc) [These will be fields in your tables]
    3: Identify Relationships Between Entities (A Comment is posted by a User) [These will be Foreign Keys and/or Join Tables]

    Or more wordily: What are you storing information about, what information are you storing about them, and what ties do they have to each other.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    My general database design steps are three:

    1: Identify Entities (User, Picture, Comment, etc) [These will be your main tables]
    2: Identify Properties of the Entities (User's Name, Email, etc) [These will be fields in your tables]
    3: Identify Relationships Between Entities (A Comment is posted by a User) [These will be Foreign Keys and/or Join Tables]

    Or more wordily: What are you storing information about, what information are you storing about them, and what ties do they have to each other.
    beautifully summarized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    brilliant like r937 said, brilliantly simplified and summarised, not to mention thank you IBazz for your info aswell!! can you provide me a visual on how these would look, say for example something common, say the entity is User, the properties of the entity being first name, second name, password, email address, info, so these would be in a row of 6 right?
    but let me get it right, these would refer back to the database (MySQL) which holds the actual information right?

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    My general database design steps are three:

    1: Identify Entities (User, Picture, Comment, etc) [These will be your main tables]
    2: Identify Properties of the Entities (User's Name, Email, etc) [These will be fields in your tables]
    3: Identify Relationships Between Entities (A Comment is posted by a User) [These will be Foreign Keys and/or Join Tables]
    If an explanation can be beautiful, that one surely is. makes me wobble though. i'm going to go through all of my 200 tables and see if I actually did it like that.
    I may be some time !!

    @imamul:
    the table would be like this

    Code MySQL:
    create table users
    ( id int not null auto_increment primary key
    , first_name varchar(99) not null
    , last_name varchar(99) not null
    , password varchar(99)
    , email_address varchar(99)
    ) engine = innodb default charset=utf8 collate=utf8_unicode_ci;

    '99' is to be set to a number so that it is big enough to allow the actual amount of characters that may ever be in that column (sometimes called field)
    the default charset can be whatever you need it to be so look up mySQL charsets
    and collate can be as you require as well, so look up MySQL collate or MySQL collations.

    now;
    if you have an address table for storing addresses (postal, delivery, office etc), you would have a table something like this:

    Code MySQL:
    create table addresses
    ( id int not null auto_increment primary key
    , address_1 varchar(99) not null
    , address_2 varchar(99)
    , zip varchar(24)
    , country varchar (99) not null
    ) engine =innodb default charset=utf8 collate=utf8_unicode_ci;

    and then you could have a joining table which would relate the user to their addresses, potentially a one-to-many relationship because we can have more than one address.

    Code MySQL:
    create table user_addresses
    ( id int not null auto_increment 
    , address_id int not null
    , user_id int not null
    , address_type varchar(24) not null
    , primary key ( address_id, user_id, address_type)
    , index reverse_ix ( user_id, address_id, address_type)
    , constraint userAddresses_addresses_fk
        foreign key (address_id)
          references addresses(id)
    , constraint userAddresses_users_fk
        foreign key (user_id)
          references users(id) 
    ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;

    the last table shows the IDs of 'addresses' and 'users' tables as foreign keys so your queries can join the tbales and get the data from both as related. And that last table also clarifies which address you may be looking for so, if you query wants the delivery address for a user, it asks for the address for the user where address-type = 'delivery' or whichever.


    hth

    bazz


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
  •