Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12

Thread: Sql

  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    264
    Thanks
    19
    Thanked 0 Times in 0 Posts

    Sql

    Im just starting out in MySQL and want to make someone, but dont really know where to start. I want to make it so that on one page they have a list of options, when they click the option it stores it in their profile but I am not sure if it should be a separate table or if it is to be in the actual profile table and if it isn't then how I would link to it. I want it to pull from one table a list of options, let the user pick, and store it for him to view.

  • #2
    New Coder
    Join Date
    Sep 2011
    Location
    Blackpool
    Posts
    55
    Thanks
    7
    Thanked 1 Time in 1 Post
    Quote Originally Posted by IFeelYourPain View Post
    Im just starting out in MySQL and want to make someone, but dont really know where to start. I want to make it so that on one page they have a list of options, when they click the option it stores it in their profile but I am not sure if it should be a separate table or if it is to be in the actual profile table and if it isn't then how I would link to it. I want it to pull from one table a list of options, let the user pick, and store it for him to view.
    Creating a list of options would be HTML which would then tell the form through PHP to store it into a database.

    I think you should start off with the basics and learn HTML before you rush into things.
    Subscribe to a channel dedicated to helping people learn HTML, PHP & CSS.
    http://www.youtube.com/user/RanTutorials

  • #3
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,719
    Thanks
    25
    Thanked 662 Times in 661 Posts
    Use one table. I call the users table their permanent record. I store all kinds of stuff here. Of course their user_name and password, but also their email address, their age, hair color, DOB, name of their grandmother..... Everything associated with them. Easier to do this then link two three table together ever time you need information on one of them.

    FYI: google SQL JOINS. There a couple, left joins being the most used. This will help you if you ever need to use two tables. Like in a game and you have 2 or 3 teams. You have to link the team table to the users table.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #4
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,947
    Thanks
    2
    Thanked 170 Times in 165 Posts
    Hmmm, sunfighter if you only use 1 table, then where/when do you need to use joins?

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,947
    Thanks
    2
    Thanked 170 Times in 165 Posts
    First, you need to list each of the different types of data you need to store in the database. That will determine the required fields. Then you need to normalize that to work out how many tables you need and their relationships.

    Do a google search for "database normalization tutorial". Here are a couple links for tutorials.

    http://holowczak.com/database-normalization/
    http://www.phlonx.com/resources/nf3/

  • #6
    Regular Coder
    Join Date
    Sep 2007
    Posts
    264
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sunfighter View Post
    Use one table. I call the users table their permanent record. I store all kinds of stuff here. Of course their user_name and password, but also their email address, their age, hair color, DOB, name of their grandmother..... Everything associated with them. Easier to do this then link two three table together ever time you need information on one of them.

    FYI: google SQL JOINS. There a couple, left joins being the most used. This will help you if you ever need to use two tables. Like in a game and you have 2 or 3 teams. You have to link the team table to the users table.
    Well the thing is, I don't think I should do one table because on their user profile will be a list of video games they an choose that they already have. So I assume I would need one table for a list of the games, and then I could have them select it and run AJAX to insert into their profile, but with the number of games that I plan to add, I am unsure if I want all of that in their profile.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Ignore what Sunfighter wrote. Pay attention to Fishmonger.

    Why would you see any reason to use AJAX to do any of this? What's wrong with a good old-fashioned but *efficient* PHP page?

    Possible DB layout:
    Code:
    CREATE TABLE profiles (
        profileid INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100),
        password VARCHAR(50),
        ... other fields as needed ...
    ) ENGINE INNODB;
    
    CREATE TABLE games (
        gameid INT AUTO_INCREMENT PRIMARY KEY,
        gamename VARCHAR(100),
        ... other fields as needed ...
    ) ENGINE INNODB;
    
    CREATE TABLE platforms (
        platformid INT AUTO_INCREMENT PRIMARY KEY,
        platformname VARCHAR(100),
        ... other fields as needed ...
    ) ENGINE INNODB;
        
    CREATE TABLE user_games (
        profileid INT,
        gameid INT,
        platformid INT,
        CONSTRAINT FOREIGN KEY (profileid) REFERENCES profiles(profileid),
        CONSTRAINT FOREIGN KEY (gameid) REFERENCES games(gameid),
        CONSTRAINT FOREIGN KEY (platformid) REFERENCES platforms(platformid)
    ) ENGINE INNODB;
    So then you might have data such as
    Code:
    profiles:
       1  -- bob -- abc123
       2  -- ann -- xyz883
    
    games:
       1 -- Minecraft
       2 -- Doom
    
    platforms:
       1 -- XBox
       2 -- PS3
       3 -- Wii
    
    user_games
       1 -- 1 -- 1 (bob has minecraft for xbox)
       1 -- 1 -- 3 (bob has minecraft for wii)
       1 -- 2 -- 1 (bob has doom for xbox)
       2 -- 2 -- 2 (ann has doom for ps3)
    And so on.

    For the life of me I don't see how AJAX fits into that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Regular Coder
    Join Date
    Sep 2007
    Posts
    264
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Ignore what Sunfighter wrote. Pay attention to Fishmonger.

    Why would you see any reason to use AJAX to do any of this? What's wrong with a good old-fashioned but *efficient* PHP page?

    Possible DB layout:
    Code:
    CREATE TABLE profiles (
        profileid INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100),
        password VARCHAR(50),
        ... other fields as needed ...
    ) ENGINE INNODB;
    
    CREATE TABLE games (
        gameid INT AUTO_INCREMENT PRIMARY KEY,
        gamename VARCHAR(100),
        ... other fields as needed ...
    ) ENGINE INNODB;
    
    CREATE TABLE platforms (
        platformid INT AUTO_INCREMENT PRIMARY KEY,
        platformname VARCHAR(100),
        ... other fields as needed ...
    ) ENGINE INNODB;
        
    CREATE TABLE user_games (
        profileid INT,
        gameid INT,
        platformid INT,
        CONSTRAINT FOREIGN KEY (profileid) REFERENCES profiles(profileid),
        CONSTRAINT FOREIGN KEY (gameid) REFERENCES games(gameid),
        CONSTRAINT FOREIGN KEY (platformid) REFERENCES platforms(platformid)
    ) ENGINE INNODB;
    So then you might have data such as
    Code:
    profiles:
       1  -- bob -- abc123
       2  -- ann -- xyz883
    
    games:
       1 -- Minecraft
       2 -- Doom
    
    platforms:
       1 -- XBox
       2 -- PS3
       3 -- Wii
    
    user_games
       1 -- 1 -- 1 (bob has minecraft for xbox)
       1 -- 1 -- 3 (bob has minecraft for wii)
       1 -- 2 -- 1 (bob has doom for xbox)
       2 -- 2 -- 2 (ann has doom for ps3)
    And so on.

    For the life of me I don't see how AJAX fits into that.
    I was going to link a checkbox to AJAX so that they do not need to click a button to submit their choices.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Quote Originally Posted by IFeelYourPain View Post
    I was going to link a checkbox to AJAX so that they do not need to click a button to submit their choices.
    Sure. That makes sense. Not.

    They have 47 games on 3 platforms. And so each time they check one checkbox you will send an AJAX request. Oh, wait, they goofed and checked 7 checkboxes for the wrong platform. So now you have to make another 14 AJAX requests. All to save them from having to hit a "SAVE" button.

    Why not put much more of a burden on your server?

    As you can tell, I think people HORRIBLY over-use AJAX and use it for the wrong things.

    But if you are really AJAX-happy, go for it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Sep 2007
    Posts
    264
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Sure. That makes sense. Not.

    They have 47 games on 3 platforms. And so each time they check one checkbox you will send an AJAX request. Oh, wait, they goofed and checked 7 checkboxes for the wrong platform. So now you have to make another 14 AJAX requests. All to save them from having to hit a "SAVE" button.

    Why not put much more of a burden on your server?

    As you can tell, I think people HORRIBLY over-use AJAX and use it for the wrong things.

    But if you are really AJAX-happy, go for it.
    Good point. So what would be some example code say if I wanted to list all of the games in say table "games", and then say they select Final Fantasy and it takes them to the next page that list all of the Final Fantasy games from "subgames" table, then they select all the games, and submit? I'm look for some example PHP code that I could learn from. I am good in PHP, but not so much mysql.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    This is the first time you mentioned have games and subgames.

    How many *TOTAL* subgames and games do you have in those two tables?

    That will determine what the easiest/best way to go is.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Regular Coder
    Join Date
    Sep 2007
    Posts
    264
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    This is the first time you mentioned have games and subgames.

    How many *TOTAL* subgames and games do you have in those two tables?

    That will determine what the easiest/best way to go is.
    Right now just a few hundred, but it will be up in the 1000's soon.

    Also for example:
    Code:
    CREATE TABLE games (
        gameid INT AUTO_INCREMENT PRIMARY KEY,
        gamename VARCHAR(100),
        ... other fields as needed ...
    ) ENGINE INNODB;
    
    CREATE TABLE platforms (
        platformid INT AUTO_INCREMENT PRIMARY KEY,
        platformname VARCHAR(100),
        ... other fields as needed ...
    ) ENGINE INNODB;
    How could I tie a game in the DB to a platform?
    Last edited by IFeelYourPain; 10-02-2013 at 01:12 AM.


  •  

    Posting Permissions

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