Is there a limit on the # of fields in a table?

[whofarted]

I'm about to setup & table that will have about 70 feilds in it. Is that to many?

Is there a limit? will It cause any problems?

Also the fields will contain numbers representing the number of times the award was won, what do I set the "type" to? you know? where the options are things like

text
tinyint
varchar
and so on....

Why is the sky blue?

Where do clouds come from?

[lveale]

Couldn't find it in docs but think it's around 255.

70 sounds a lot, have you tried normalization?

[RandMan]

MySQL can handle it, BUT, that usually is not the best way to set up any database.

For example, one table could store an invoice, but much of the info would be repetitious, customer info, product info.

Your products should have their own table, same with customers. Customer_id and product_id fields would provide the rest of the info.

Your program assembles the tables for input/output.

This makes your database much smaller/faster.

Hope this helps.

[whofarted]

well here's the deal, I was going to put the info into 2 databses but couldn't understand what the guy was trying to say on how to do it.

in this thread http://www.sitepointforums.com/showt...threadid=33313


I decided the only way I personally know how to do it is like this:

I need to have all player info for their profile page on one page,so i'm doing it this way

1 table called player_profile

The fields are name, playerrealname, and like 6 other, then i've added tons of others to show their awards won. Here's how that plays out.

there are 23 awards to be won.

there are 3 classes to win them in first class, second class, and third class.

23 awards
x3 classes
-------------
69 fields
+10 or so for other player info
-------------
=79 or round about fields.

I couldn't figure out how to get the awards to match the # of times recieved for the correct player any other way.

I'm a silly little newbie now. but i'm sure with some practice & pleanty of trial & error I'll figure out a better way. Till then I guess i'll deal with it.

[RandMan]

One method would be having 2 tables. One with basic profile, the other with the awards. A field ID in each table would match them up.

Profile
id UNIQUE AUTO-INCREMENT
name
email
other fields

Awards
pid (this is the player who won)
date
class
other fields

When you display data, do query in AWARD TABLE for the player
$query = "SELECT * FROM awards WHERE pid='$id'";
$result = mysql_query($query);
$num = mysql_num_row($result);

$num gives number of awards for that player

[whofarted]

Right but the only problem is it seems that that would do it but the SAME award has to show 3 DIFFERENT values ie:

Double kill award 1'st class = 2, 2'nd class = 4, 3'rd class = 15.

And has to match up with the players profile that earned it.

I'm "most likely" wrong but the way that is shown it looks like it displays the award with only one value to be displayed.

I just couldn't figure out how to get it to display the way I wanted it to & it's a LOT of info & I didn't want to take the chance I'd do something wrong. That's a LOT to go back & redo if I screw something up & since i'm a newbie to php & MySQL that's REALLY likely to happen.

[RandMan]

Add another field.

Or have a table listing just the 23 awards with their description.

You would end up with 3 tables.

Player, Award, Wins

The Wins table would only have 3 fields: player_id, award_id and date.

It may seem easier for you at this point to have one massive table, BUT, as time goes by your database will become massive with duplicated information and from the looks of it, about 50 empty fields.

It will take you a little longer to learn how to do it the most efficient way now. Trust me, you don't want to have to split up your database and rewrite all your scripts 6 months from now when people are depending on your data.

An excellent book for you is "MySQL" by Paul DuBois. One third of the book is devoted to database theory. It is written in simple language with a sample database you can download and use to try out the example. It will show you how to put together a multiple table database and extract the information you need.

Another good book, is "PHP ESSENTIALS" by Julie Meloni. You can probably learn the same thing, but it doesn't go into the theory.

Both books cover PHP integration with MySQL. DuBois' book also covers using Perl and C to access data. Just skip that section.

Meloni's book shows how to use all the databases: MySQL, mSQL, postgre, Oracle and a few others.

Once you understand the theory, you will understand what I am talking about.

Good luck.

[whofarted]

ok, thanks for the info.

Also the fields will contain numbers representing the number of times the award was won, what do I set the "type" to? you know? where the options are things like

text
tinyint
varchar
and so on....

Can anyone answer this one?

[Defender1]

depends on how high the numbers will go.
if it's just a single number (1-9), i usually use tiny int.
if they can get kinda big, use int.

[tubedogg]

I'd use
smallint(6)