Register | Login | |||||
Main
| Memberlist
| Active users
| ACS
| Commons
| Calendar
| Online users Ranks | FAQ | Color Chart | Photo album | IRC Chat |
| |
0 user currently in Programming. | 3 guests |
Acmlm's Board - I2 Archive - Programming - MySQL! | | | |
Add to favorites | "RSS" Feed | Next newer thread | Next older thread |
User | Post | ||
HyperLamer <||bass> and this was the soloution i thought of that was guarinteed to piss off the greatest amount of people Sesshomaru Tamaranian Level: 118 Posts: 6185/8210 EXP: 18171887 For next: 211027 Since: 03-15-04 From: Canada, w00t! LOL FAD Since last post: 2 hours Last activity: 2 hours |
| ||
Well I've finally decided to start screwing around with MySQL a bit. What I'm aiming to create is a database of codes for games. Each code has attributes like the name, submission date and of course code; I imagine one code = one row. Now I'm wondering how to best handle multiple games. Would it be better to simply add a "game" field to each row, and select only rows where this field matches the game's name/ID, or create one table per game? (I'm going with the table method at the moment. ) Also the games will be categorized by console; how would I manage that? Say I wanted to make a list of codes for Mario Kart 64. Should I make a table named "N64_MarioKart64" or can I make an N64 table with the games as 'sub-tables'? (edited by The Crimson Chin on 07-31-05 03:51 AM) |
|||
DarkSlaya POOOOOOOOOOOORN! Level: 88 Posts: 3916/4249 EXP: 6409254 For next: 241410 Since: 05-16-04 From: Montreal, Quebec, Canada Since last post: 8 hours Last activity: 5 hours |
| ||
Personnaly, I believe that using IDs for both would be easier and as efficiant as you would want. Since you only have to do queries like: $code = mysql_fetch_array(mysql_query("SELECT stuff FROM games WHERE gameid='".$_GET['gid']."'"); or $gamebyconsole = mysql_fetch_array(mysql_query("SELECT stuff FROM games WHERE console='".$_GET['cid']."'"); Dunno, seems the best way to do it. |
|||
Narf Hi Tuvai! (reregistering while banned) Level: 16 Posts: 76/100 EXP: 17634 For next: 2622 Since: 12-26-04 Since last post: 22 hours Last activity: 14 hours |
| ||
I think working with ID numbers would be better and easier for you right now. However, if you get more experienced with queries, in particular using 'LIKE' in SQL queries, give it a go with names. There are tons of ways to do what you want to do, try out a few of them, should do your MySQL learning some good. |
|||
neotransotaku Baby Mario 戻れたら、 誰も気が付く Level: 87 Posts: 3711/4016 EXP: 6220548 For next: 172226 Since: 03-15-04 From: Outside of Time/Space Since last post: 11 hours Last activity: 1 hour |
| ||
one table is best with an index on the game, and perhaps an index on console and use a primary key of (game, code) title to take care of uniqueness The fewer tables you can use, the better things will be. |
|||
DarkSlaya POOOOOOOOOOOORN! Level: 88 Posts: 3920/4249 EXP: 6409254 For next: 241410 Since: 05-16-04 From: Montreal, Quebec, Canada Since last post: 8 hours Last activity: 5 hours |
| ||
Originally posted by neotransotaku I agree with that. But if there's alot of rows, wouldn't it slow down the server (I think I heard that somewhere ) |
|||
neotransotaku Baby Mario 戻れたら、 誰も気が付く Level: 87 Posts: 3712/4016 EXP: 6220548 For next: 172226 Since: 03-15-04 From: Outside of Time/Space Since last post: 11 hours Last activity: 1 hour |
| ||
Many rows will slow down the server if you have a poor underlying format for a table and/or you are searching on a non-index. But if you use a data structure called a B+ Tree, you can get the data of single row in multi-million row table with a constant amount of I/O seeks (typically three or four) as long as you search on the index. That is why I suggested HH to use an index, it will speed up his queries significantly if he does searching/add/deleting via a game title | |||
HyperLamer <||bass> and this was the soloution i thought of that was guarinteed to piss off the greatest amount of people Sesshomaru Tamaranian Level: 118 Posts: 6193/8210 EXP: 18171887 For next: 211027 Since: 03-15-04 From: Canada, w00t! LOL FAD Since last post: 2 hours Last activity: 2 hours |
| ||
I'm not sure what Neo means, having never used SQL before. (Haven't managed to get anything working yet, some sort of password problem...) I like Argos' idea, but I'm not entirely sure how it works. What I'm thinking now is to have one table listing all the games by game ID, name and console (like 'Mario Kart 64', 'MarioKart', 'N64'). So say you wanted a list of N64 games, something like "SELECT FROM games WHERE console = 'N64'" should do it, and if you look up Mario Kart, you would do "SELECT FROM N64MarioKart" or something along those lines...? (The console list can just be a hard-coded page, it's not going to be changing much. ) | |||
neotransotaku Baby Mario 戻れたら、 誰も気が付く Level: 87 Posts: 3713/4016 EXP: 6220548 For next: 172226 Since: 03-15-04 From: Outside of Time/Space Since last post: 11 hours Last activity: 1 hour |
| ||
Mines the same thing, just more stuff to speed up queries and to ensure uniqueness So, the syntax would be along the lines of... Create table games (gameTitle varchar(30) not null, console varchar(10) not null, codeTitle varchar(50) not null, code varchar(30) not null, primary key (gameTitle, codeTitle)); That just creates the table (with columns of gameTitle, console, codeTitle, code) and ensures that you can not enter the same code title twice for the same game. As for creating an index, you do the following: create index gameIndex on games(gameTitle) create index consoleIndex on games(console) As for your two SQL statements,
|
Add to favorites | "RSS" Feed | Next newer thread | Next older thread |
Acmlm's Board - I2 Archive - Programming - MySQL! | | | |