help inputting data into tables

hi. i am keeping records of track race times. so i have the top 10 list for 6 events. there are 6 categories, with 3 variables each: the person, the time, and the year they ran it.

i have set up my table as follows:
recordsID int(10) AUTO_INCREMENT,
sex varchar(10),

name1 varchar(30),
name2 varchar(30),
name3 varchar(30),
name4 varchar(30),
name5 varchar(30),
name6 varchar(30),
name7 varchar(30),
name8 varchar(30),
name9 varchar(30),
name10 varchar(30),

time1 char(10),
time2 char(10),
time3 char(10),
time4 char(10),
time5 char(10),
time6 char(10),
time7 char(10),
time8 char(10),
time9 char(10),
time10 char(10),


year1 char(6),
year2 char(6),
year3 char(6),
year4 char(6),
year5 char(6),
year6 char(6),
year7 char(6),
year8 char(6),
year9 char(6),
year10 char(6),
PRIMARY KEY(recordsID)


the reason why "sex" is in there is to distinguish between my male and female top 10 lists. i am trying to write all my information in a word document, and open it up, and input the data into the table that way so i can learn some of the file-handling commands. but i don't know really where to go. all i have so far is,

<?
$records = "trackRecords.doc";
$lines = file($records);
$lengthLines = strlen($lines);

for($i=0; $i<$lengthLines; i++) {

if ($lines != "") {
$query = INSERT INTO recordsTable (
lines[i]
);


can anyone help me? thanks.

1. Are you sure you want to create such a flat file structure in your database? There are times when this is ok, however, it doesn't look right here.

Try normalizing your tables. You might try something like this:

a. create a table athlete
athleteId | athleteName | athleteSex | etc...

b. create a table events
eventId | eventName | eventTime

c. create a table called heat (this table is optional - although it would make tracking and reporting better, it will complicate the application so leave it out if you are not comfortable - you could just put the head number directly in the events table also)
heatId | eventId | heatNumber ( this could be an enum type if you are sure you know the maximum amount of heats you will ever need)

d. create a table called results
resultId | athleteId | eventId | mark

IN 'd', resultId is just an auto incrementing number, athleteId is a foreign key to lookup the athlete info, evenId does the same thing but for the events table, and mark is the athlete's time or mark (50.79) I suggest you leave this as a varchar and not a number


2. You are going to have a tough time iterating a word document unless you save the info in the word document as a plain text file first.

Athlete Id, Athlete Name, etc...
No... The events table has events only... no results of those events. Do you run track? If so, I'm going to break it down so that you can see it in that context. When a track meet is setup what do all the coaches have a list of about two weeks before the meet? They basically have a schedule of events. Your events table is just that.
It will have the event name, time of event, date of event, and a heat number. The coaches do not have results until later. Both are separate, but you need both to be able to show what happened at the meet later on. If you design it the way I've outlined it, you can even use the results table to tally up a score at the end of a meet.
Since I added the heat number to the event table you don't need this anymore. There are a number of ways to design a database table... we have elected to do it this way for simplicity.
You won't be addin results to the events table...
You got it

A database table does not usually grab anything... it is either read from or written to.[/quote]

No problem

Hi there,

What laidbak is trying to do for you here is called "normalization." Essentially, you want to have a structure of interrelated tables that is dependent on unique values called "keys." This allows you to link information together when needed, as needed, rather than having it all listed in one "hard" table. Don't try to visualize how the information will be presented on your web page: database tables are NOT that kind of table. Design your tables as units of information that can be uniquely identified, so they can later be linked together through queries, perhaps in ways that you can't yet know...

This article is a good introduction to relational database table design. The examples in there will really clear things up.

thanks for the replies. i read the article on relational databases and have a better understanding of it. but i don't quite see how it fits into the application here. i mean for example:

Women's 800
1) Audrey 2:15.01 1998
2) meghan 2:16.00 1999
....
10) julie 2:20.00 1993

women's 1500
1) jenn 4:30 2000
....
10) erin 4:34 2001
<hr>
similarly for men

men's steeplechase
1) bob 9:00 2000
...
10) joe 10:00 1994


if i had a different table for say, the athletes, events, results, and years, how does that help? i don't understand how i would sort through all the information. or would it be better just to make tables for each event, and then input the person's name, time, year for each event and do it that way? thanks again!!

Sorry to keep pointing you to tutorials, however, this is a very good one that will probably help you understand how these tables not only relate to each other, but how you would then extract and piece the info back together.

I'm sure you are wondering why you would want to do this, but it will become more clear as your start to understand the relational database concept. This isn't some weird passion, it is a tested concept that works well for these kinds of projects.

http://www.w3schools.com/sql/sql_intro.asp

ok i have split my data into 3 different tables:
1: with just the events (eventID)
2: with the person's name and sex (personID)
3: with the person's time and the year they did it in. (recordID)

table 3 has the corresponding IDs that fit match the other tables. my query is as follows:

SELECT DISTINCT
recordsTFTable.record, recordsTFTable.year, recordsTFTable.place,
eventsTFTable.event,
personTFTable.name, personTFTable.sex
FROM recordsTFTable, personTFTable, eventsTFTable
WHERE personTFTable.personID=recordsTFTable.personID
AND sex LIKE 'female' AND recordsTFTable.eventID=1


i've tried different ways of retrieving the data, but i always get like 6 copies of the data. i can do LIMIT 10 at the end, which limits my data to the appropriate size. but it doesn't seem like that is the most efficient way. am i doing something wrong? or is there something else i can do?

There are two ways I can help you solve this quickly.

1. PM me with the IP, user, pass, dbname of your db server

or

2. Paste your table layout here

Here are the 3 tables I created for my database:

CREATE TABLE eventsTFTable (

event varchar(20),
eventID int(4) auto_increment,

PRIMARY KEY(eventID)


CREATE TABLE personTFTable (

name varchar(30),
sex varchar(10),
personID int(4) auto_increment,
PRIMARY KEY(personID)


CREATE TABLE recordsTFTable (

record char(10),
year char(6),
place varchar(6),
eventID varchar(4),
personID varchar(4),
recordID int(4) AUTO_INCREMENT,

PRIMARY KEY(recordID)

Better would be - enum of 'M','F'

Best to use a 4 char year

Not good to have a foreign key that is not the same type as the key it references... (it should be int(4).

Same with -> personID varchar(4),

so when you input data into tables, what's the best way to do it to keep track of ids. i mean i have 3 tables, and when i input the data, i want to make sure the correct personID goes to the correct recordID and to the correct eventID. an example of my data would be:

event:800

name: miriam
sex: female

record:2:18.00
year:2003
place:6

so i can input the different events, input the different names and their sexes. but then when i input the corresponding record, year, and place for each person into the recordsTable, i need to make sure my eventID and personID are correct. how do i keep track of them all? do i have to do it on paper first, and make a form and make sure i input the correct id# for each person? cause that's what i have done and i don't think it is the optimum method.

Lets say you have an input form like the following:
(very simple example)

Name: [John Doe]
State: [California [V]]

This is my febble attempt at creating input forms in ASCII text.
Hope it makes sense.
Notice "State" is a drop down box.

The State selected is Calfornia, however, it does not go into the datbase like that. In the database it would look like this:

NAME | STATE
-----------------------
John Doe CA

STATE in this table is the foreign key. This foreign key is actually a primary key in another table called STATES. Here is the STATES table.

STATECODE | STATENAME
---------------------------------
CA California


Now, to simply answer your question, you would basically execute a select statement on the STATES table grabbing all the STATE CODES and STATE NAMES and have those populate the dropdown I drew above. When as state is selected you are actually inputting the CODE into the database. This works because you would put the STATECODE into the value attribute of the drop down.