file upload database design.

[LearningCoder]

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.

[Old Pedant]

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?

[LearningCoder]

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.

[Old Pedant]

That's an okay spreadsheet design.

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

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.

[LearningCoder]

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.

[Old Pedant]

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:

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.

[LearningCoder]

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.

[LearningCoder]

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($time, 0, 10);
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.

[Old Pedant]

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.

[LearningCoder]

Originally Posted by Old Pedant

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?

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.

[Old Pedant]

Originally Posted by LearningCoder

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.