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 11 of 11
  1. #1
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    924
    Thanks
    76
    Thanked 29 Times in 29 Posts

    file upload database design.

    Hi, I am going to create my upload script file and database, as I need it to be able to complete another 2 areas of the site.

    I am creating a site for uploading gaming demo files, but only for specific games.
    Within the form, I need to be able to select which game relates to the demo being uploaded.

    For instance, I want to have a drop-down select box listing the games initials/name, in which the user selects, so I can determine which table to insert the information to.

    Another thing really is that I don't know how to design my table. Should I use 1 table which will deal with the different games, or would it be better to create a table for each individual game? I am going to send the uploaded files all into the same folder, but determine which ones to show/hide on what the user wants to see (i.e clicking the certain game icon, will show all the uploaded demorecs for that specific game).

    I hope someone can help me or give me some advice on how to design the table/s.

    Thank you in advance for any information.

    Kind regards,

    LC.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    Should I use 1 table which will deal with the different games, or would it be better to create a table for each individual game?
    Don't even *THINK* about separate tables per game. You *WILL* come to regret it.

    But other than that, I don't know what advice we can give you with such scanty information.

    Maybe if you showed us your first stab at a database design it would be easier to comment?
    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.

  • #3
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    924
    Thanks
    76
    Thanked 29 Times in 29 Posts
    I've not created anything yet, I wanted to seek some advice before doing so.

    When the user submits a demo file, I need a way of knowing for which game it corresponds to.

    I was thinking to do it as such:

    files_table -> id(int)PK, uploaded_by(varchar), filename(varchar), file_size(int), date_uploaded(int), which_game(varchar).

    What I was thinking is asking the user to choose an option from a drop down box. for example, if they are uploading a demo for COD4, then they choose the option "cod4" for instance. Then I was going to insert that data into the database in a new field called 'which_game' for example, along with that file information, so I can later on use that specific field to target the files for that specific game.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    That's an okay spreadsheet design.

    For a database, you should have at least 3 tables:

    Code:
    TABLE: users
        userid INT AUTO_INCREMENT PRIMARY KEY
        username VARCHAR(xxx) 
        ... other stuff about the user ...
    
    TABLE: games
        gameid INT AUTO_INCREMENT PRIMARY KEY
        gamename VARCHAR(xxx)
        ... other stuff about the game ...
    
    TABLE: uploads
        uploadid INT AUTO_INCREMENT PRIMARY KEY [this may not actually be worth having]
        userid INT FOREIGN KEY REFERENCES users(userid)   [you could name this uploadedby]
        gameid INT FOREIGN KEY REFERENCES games(gameid)
        filename
        filesize
        date_uploaded DATETIME   [never never use INT for dates!]
    There's no reason to give a primary key to the upload unless you have some other table that will then be *referring* to that primary key.

    You can use uploaded_by in place of userid. I much prefer that the names of my primary and foreign keys be the same in both tables. Simplifies thinking.

    And never never never store UNIX timestamps as INTs in a database. You can do it. But then you can't easily create queries such as "Find all files uploaded in March of 2013", which is a *TRIVIAL* query if you use the DATETIME field type.
    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.

  • #5
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    924
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Ah that's great thank you. I think I will opt to use the uploaded_by in place of userid.

    I will research a bit about DATETIME type, never used it. I've been storing the date as a timestamp, then when reading that data from the database, I send it through the date() function to format it.

    Thanks for the information. Won't be back on until 6pm so you won't hear from me until after then. I'll keep you updated on where I'm at.

    I currently have the users table completed. Registering and logging in fine. It is now a case of creating those other two tables. I'll read into the whole table relationships side of it because I think that would help me quite a bit right now with what I'm trying to achieve. The whole primary and foreign keys are not making much sense to me right now.

    Kind regards,

    LC.
    Last edited by LearningCoder; 10-21-2012 at 10:04 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    My syntax for creating foreign keys is *NOT* correct for MySQL. I just write it like that for simplicity.

    MySQL requires the FOREIGN KEY constraint to be separate in the table definition.

    So, more correctly:
    Code:
    CREATE TABLE uploads (
        uploaded_by INT,
        gameid INT,
        filename VARCHAR(255),
        filesize INT,
        date_uploaded DATETIME,
        CONSTRAINT FOREIGN KEY uploaded_by REFERENCES users(userid),
        CONSTRAINT FOREIGN KEY gameid REFERENCES games(gameid)
    ) ENGINE=INNODB;
    Note that if you don't use the INNODB engine your foreign key constraints will *not* be enforced by MySQL.
    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.

  • #7
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    924
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Had a look at DATETIME in relation to inserting data. Created a testing page:
    PHP Code:
    <?php
    $time 
    time();
    $date date("Y-m-d H:i:s"$time);

    $mysql mysql_connect("localhost","root","");

    $db mysql_select_db("demo_central",$mysql);

    $query mysql_query("INSERT INTO datetime (date_time) VALUES ('{$date}')");
    ?>
    This inserts ok, what I was wondering is if I want to display it in a different way, do I need to manipulate it with string functions? I did a quick var_dump() on the returned timestamps when retrieving from the db and it is the string type.

    Edit:Just saw your post above, judging by that syntax and the fact I have no experience with studying anything to do with databases, I was wondering if you know any good/reliable links to do with databases and tables. I really want to understand this subject more and my php/mysql book contains very limited information

    Kind regards,

    LC.
    Last edited by LearningCoder; 10-21-2012 at 10:39 PM.

  • #8
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    924
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Ok I came up with this:
    PHP Code:
    <?php

    $mysql 
    mysql_connect("localhost","root","");
    $db mysql_select_db("demo_central",$mysql);

    ?>
    <html>
    <head>
    <title>time</title>
    </head>
    <body>

    <?php

    $qry 
    mysql_query("SELECT * FROM datetime");

    while (
    $row mysql_fetch_array($qry)){
       
    $times[] = $row['date_time'];
    }

    echo 
    "<pre>";
    print_r($times);
    echo 
    "</pre><br />";

    $time $times[3];
    echo 
    $time."<br />";

    $date substr($time010);
    echo 
    $date."<br />";

    $date_array explode("-",$date);

    $reverse array_reverse($date_array);

    echo 
    "<pre>";
    print_r($reverse);
    echo 
    "</pre><br />";

    $new_date implode("/"$reverse);

    echo 
    $new_date;

    ?>

    </body>
    </html>
    Which displays my date exactly how I want it. I need to tweak it a tad before putting it into my site like using a while loop to iterate through each row and basically do something very similar to that above.

    Regards,

    LC.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    No no no...working way too hard.

    First of all, MySQL requires that you *give* DATETIME values in either a text format:
    'yyyy-mm-dd hh:mm:ss'
    or as a number
    yyyymmddhhmmss

    If you don't need the time, you just use 'yyyy-mm-dd' or yyyymmdd.

    To get a value back out, you have several choices. But one preferred way for PHP people seems to be to use MySQL's DATE_FORMAT() function, which converts the date to a string in most any format you wish:
    http://dev.mysql.com/doc/refman/5.5/...on_date-format

    Note that there is also a STR_TO_DATE() function that goes the other way, if you don't want to use the standard 'yyyy-mm-dd' format:
    http://dev.mysql.com/doc/refman/5.5/...on_str-to-date

    The whole reason you store dates and times as DATETIME datatype is so that you can use all the WONDERFUL functions that MySQL provides *JUST* for working with dates and time:
    http://dev.mysql.com/doc/refman/5.5/...functions.html

    **********

    I don't have any favorite sites for learning database principles. I learned them long enough ago that I used books (long out of print) or sites that are long gone. But there are tons of tutorial sites out there. Google is your friend.
    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 LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    924
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Old Pedant View Post
    First of all, MySQL requires that you *give* DATETIME values in either a text format:
    'yyyy-mm-dd hh:mm:ss'
    or as a number
    yyyymmddhhmmss

    If you don't need the time, you just use 'yyyy-mm-dd' or yyyymmdd.
    so if I only want the date, can I do this:
    PHP Code:
    $time time();
    $date = ("Y-m-d"$time); 
    When I'm inserting this, the YYYY-MM-DD is correct and the hours minutes and seconds are set to 0 obviously because we didn't format the time only the date part. Is this correct? Do I just ignore the hours, minutes and seconds when formatting the date for display?

    Quote Originally Posted by OldPedant
    To get a value back out, you have several choices. But one preferred way for PHP people seems to be to use MySQL's DATE_FORMAT() function
    Had a peek at those links and saw some syntax. Does this function have to be used within a query string?
    Had a look at the STR_TO_DATE() also and noticed it's all MySQL syntax. Does using MySQL functions within a prepared statement make any difference because that's what I am using.

    Kind regards,

    LC.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    Quote Originally Posted by LearningCoder View Post
    When I'm inserting this, the YYYY-MM-DD is correct and the hours minutes and seconds are set to 0 obviously because we didn't format the time only the date part. Is this correct? Do I just ignore the hours, minutes and seconds when formatting the date for display?
    Yes.

    Had a peek at those links and saw some syntax. Does this function have to be used within a query string?
    Had a look at the STR_TO_DATE() also and noticed it's all MySQL syntax. Does using MySQL functions within a prepared statement make any difference because that's what I am using.
    ???? OF course you can only use MySQL functions in a prepared statement.

    Prepared statements are just MySQL queries with place holders for the values.
    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.


  •  

    Posting Permissions

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