Take from a text file and insert into the db...

[jbachris]

I want to upload a text file (or open/read one already uploaded on the server) and take it's contents and insert it into the database. The format of the text file is:

Miami 31, Marshall 3
Northern Colorado 6, Hawaii 63

I usually have 100+ games a week in a text file. These four fields should be entered into the team1_name, team1_score, team2_name, and team2_score fields of the database table.

I've dealt before with fopen and fputs, but really haven't dealt with opening/reading a text file and taking it's contents into the database.

Say the scores file is scores.txt and it is in the same directory on the server.

[Piranha]

You should not have team1_name, team1_score, team2_name and team2_score in one table. It will make it hard to create queries that for example calculate how many times a particular team have won. Read up on "database normalization" to understand it and get to know how to solve it. On phpbuilder there is a good article.

[jbachris]

Piranha wrote:

You should not have team1_name, team1_score, team2_name and team2_score in one table. It will make it hard to create queries that for example calculate how many times a particular team have won. Read up on "database normalization" to understand it and get to know how to solve it. On phpbuilder there is a good article.

I have two tables, games and teams. The games table has team1, team1_name, team1_score, team2, team2_name, team2_score, date. team1 and team2 are set to default to 0. Before computing w/l/t records, etc it takes the game fields where team1 or team2 is equal to 0 and then matches them with their counterpart in the team table and inserts the team's unique id (integer) into the team1 and team2 field.

I've computed it this way for over 20,000 games with no issues...

[Piranha]

jbachris wrote:

I've computed it this way for over 20,000 games with no issues...

The fact that it is possible does not mean that it is good, it only means that you have found a workaround to "solve" the problem. I still suggest that you read up on database normalization to correct it, or at least understand why you should have done it another way.

[NogDog]

See the [man]fgets/man page for an example of how to read a file line by line. Or if you are using a CSV file, see [man]fgetcsv/man.

[jbachris]

Piranha wrote:

The fact that it is possible does not mean that it is good, it only means that you have found a workaround to "solve" the problem. I still suggest that you read up on database normalization to correct it, or at least understand why you should have done it another way.

Thanks for your concern. I've read up on the normalization and I'm 100% confident that this is the correct way to accomplish what I am try to do with my db.

[Piranha]

jbachris wrote:

Thanks for your concern. I've read up on the normalization and I'm 100% confident that this is the correct way to accomplish what I am try to do with my db.

Then I suggest that you read it again, especially the first normal rule. It says that values should be atomic, and by having two team names in in one row, and two scores in one row, it is not atomic. Something like below would be better:

Match          Match_team          Team
id             match_id            id
date           team_id             name
               score

Then you have it normalized to at least BC normal form.

[jbachris]

Piranha wrote:

Then I suggest that you read it again, especially the first normal rule. It says that values should be atomic, and by having two team names in in one row, and two scores in one row, it is not atomic. Something like below would be better:

Match          Match_team          Team
id             match_id            id
date           team_id             name
               score

Then you have it normalized to at least BC normal form.

So, what would be the benefit of doing it this way? You said the only reason it is working for me now is I found a work around. I don't care why it works, I only care that it works.

[bradgrafelman]

jbachris wrote:

So, what would be the benefit of doing it this way?

Speed. Optimization. Scalability. Room for growth (features/etc.). Intelligent database design practice. I'm sure the DBA's and gurus around here could add to my list, but that's just what I came up with off the top of my head.

jbachris wrote:

I don't care why it works, I only care that it works.

You know, on the first day of class an engineering professor showed us pictures of a walkway that had collapsed and killed people. The problem was isolated to a construction error that hadn't followed original designs. I'm willing to wager that the engineers involved in the construction had the same attitude as yours when they had built the walkway. It seemed functional... and the change they made was visually a very small change... so why care? Those engineers no longer have an engineering license and later faced court time. I know you're not building a bridge, but I just thought that was an interesting anecdote that I could share.

[jbachris]

I have a code that works in taking the format of:

01-Sep-07, Northern Colorado 3, Hawaii 63
01-Sep-07, Marshall 3, Miami 31

And inserts it into my current database, how can I modify it do take the same info and insert it into the database, with a table structure that Piranna posted above?

Here's the code I'm using now:

<?php

require ( "dbconnex.php" );

$lines = file('wv_scores.txt');
foreach ($lines as $line)
{
    $team = $score = array();
    $teamscore = explode(',', $line);
    foreach ($teamscore as $k=>$ts)
    {
        $x = strrpos($ts, ' ');
        $team[$k] = trim(substr($ts,0,$x));
        $score[$k] = trim(substr($ts,$x));
    }

$games_rs = $conn->Execute ( "INSERT INTO wvhs_gm (team1_name, team1_score, team2_name, team2_score, ) VALUES ('$team[0]', '$score[0]', '$team[1]', '$score[1]')" ) or die ( $conn->ErrorMsg() );

}
?>

My structure would be:

TABLE wvhs_gm
gameID
game_date
game_year

TABLE wvhs_gm_info
gameID
teamID
team_name
team_score