Register | Login
Views: 19364387
Main | Memberlist | Active users | ACS | Commons | Calendar | Online users
Ranks | FAQ | Color Chart | Photo album | IRC Chat
11-02-05 12:59 PM
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
Posted on 07-31-05 12:47 PM Link | Quote
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
Posted on 07-31-05 05:57 PM Link | Quote
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
Posted on 07-31-05 08:25 PM Link | Quote
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
Posted on 07-31-05 11:07 PM Link | Quote
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
Posted on 07-31-05 11:15 PM Link | Quote
Originally posted by neotransotaku

The fewer tables you can use, the better things will be.


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
Posted on 07-31-05 11:53 PM Link | Quote
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
Posted on 08-01-05 06:47 AM Link | Quote
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
Posted on 08-01-05 10:42 PM Link | Quote
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,

  • SELECT FROM games WHERE console = 'N64' -- this works as long as all your N64 entries are group together. However, if you seperate each game into it's own table, then you won't get all your games unless you iterate through each table
  • SELECT FROM N64MarioKart" -- first you need to say 'SELECT * FROM N64MarioKart'; and second, your above query would miss this table unless the data in this table is also in your Games table -- but then you would have redundant (and possibly inconsistent) data storage because not only would you have to update the games table, you would also have to update your N64MarioKart table


    (edited by neotransotaku on 08-01-05 01:43 PM)
Add to favorites | "RSS" Feed | Next newer thread | Next older thread
Acmlm's Board - I2 Archive - Programming - MySQL! | |


ABII


AcmlmBoard vl.ol (11-01-05)
© 2000-2005 Acmlm, Emuz, et al



Page rendered in 0.007 seconds.