read from a txt file and insert into db

[ameriblog]

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.

[Fadion]

In your example, "Miami" is the team name and "31" is the team score? If so:

 

<?php
$handle = fopen('file.txt');
$lines = fread($handle, filesize('file.txt'));
$content = explode(', ', $lines);
foreach($content as $value){
     $value = explode(' ', $value);
     $tname = $value[0];
     $tscore = $value[1];
     $results = mysql_query("INSERT INTO table (team_name, team_score) VALUES ('$tname', '$tscore')");
}
?>

 

Hope its what u wanted and the code helps.

[ameriblog]

In your example, "Miami" is the team name and "31" is the team score? If so:

 

<?php
$handle = fopen('file.txt');
$lines = fread($handle, filesize('file.txt'));
$content = explode(', ', $lines);
foreach($content as $value){
     $value = explode(' ', $value);
     $tname = $value[0];
     $tscore = $value[1];
     $results = mysql_query("INSERT INTO table (team_name, team_score) VALUES ('$tname', '$tscore')");
}
?>

 

Hope its what u wanted and the code helps.

 

It does very much. The one issue I have for now, is that the table is setup so team1_name, team1_score, team2_name, team2_score. So for each row the team on the left would get entered for team1_name and team1_score and the teams right of the comma would be team2_name and team2_score.

[Barand]

Names like "Northern Colorado" which contain a space screw it up if you just try to split on spaces.

 

try

 

<?php
$lines = file('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));
    }
    $sql = "INSERT INTO table (team1_name, team1_score, team2_name, and team2_score)
            VALUES ('$team[0]', '$score[0]', '$team[1]', '$score[1]')";
            
    echo "<pre>$sql</pre><br>";
}
?>

[Fadion]

<?php
$handle = fopen('file.txt', 'w');
while(!feof($handle)){
  $line = fgets($handle, 4096);
  $teams = explode(',', $line); //u have [0]=>'Miami 31' [1]=>'Marshall 3'
  list($team1name, $team1score) = explode(' ', $teams[0]);
  list($team2name, $team2score) = explode(' ', $teams[1]);
  $results = mysql_query("INSERT INTO teams (team1_name, team1_score, team2_name, team2_score) VALUES ('$team1name', '$team1score', '$team2name', '$team2score'))");
  }
?>

 

Guess this does it. U could use also file() for getting each line but i find it better to use fopen() and associated functions for each occasion.

[Barand]

@GG

 

I've just pointed out that the spaces in the team name will screw it up, yet you persist!

 

Your results

[pre]

INSERT INTO teams (team1_name, team1_score, team2_name, team2_score) VALUES ('Miami', '31', '', 'Marshall')

 

 

INSERT INTO teams (team1_name, team1_score, team2_name, team2_score) VALUES ('Northern', 'Colorado', '', 'Hawaii')

 

 

INSERT INTO teams (team1_name, team1_score, team2_name, team2_score) VALUES ('', '', '', '')

[/pre]

 

Have you considered testing before posting?

[Timma]

@GG

 

I've just pointed out that the spaces in the team name will screw it up, yet you persist!

 

 

He may have been posting at the same time you were.

[Fadion]

I was writing my post when u posted so i didnt see the thing about the space. Usually i test my code when im not sure about something, but in this case didnt think of the space in the teams. Actually i didnt even notice it. Im no php pro (im just a Sr. Helper, not a Genius ) so bear me for any mistake  

 

EDIT: Timma got it right 

[Barand]

6 minute difference. A previous post would be notified. No excuse

[Fadion]

6 minute difference. A previous post would be notified. No excuse

 

If your posting that seriosuly (guessing yes) then im pretty stunned. I saw the notice but didnt go and see the actual post. I spent lets say 5 minutes to write a code for someone who is having a problem, with my good will for helping him. If my code isnt 100% right then he can use yours, or correct mine to fit his needs. This is a help forum so even posts which point to the right direction are considered good help. Anyway i dont wanna start an arguement here. I just didnt see your post and im bringing no excuses.

[Barand]

If, when I post, I get a warning that that there is another post, I take the trouble to read it. If it solves the problem I back off, unless I have a plausible alternative. In addition, I usually test my code before posting so I don't waste people's time. Code that doesn't work doesn't help.

 

So yes, I am serious.

[Fadion]

Sure, i usually test code too. Im not that good to right several lines of code and be sure that will work in the way i needed. Further testing and adjusting may result with how i wanted the code. In this occasion, as i previously said i didnt notice the space between teams, thats why i posted the code. I said it and im repeating it, i try to help with as much as i know and im ready to read articles just to have a good answer for someone in trouble. Dont know why u're being so "harsh" for such a small thing anyway. U could have just said that my code doesnt fully work and i would have got the message.

[Barand]

U could have just said that my code doesnt fully work and i would have got the message.

 

That's exactly my point - I did point it out and you didn't get the message, you just ignored it - the post you were warned about, but chose to ignore and post anyway, explicitly stated, right at the start, that spaces in team names would give  problems.

[Fadion]

I think we both are loosing time here, but as im concerned of mine, actually im loosing my time here. If this makes u happy: u are the chosen one of php and ure always right. Dependless what i say u still repeat the same things. Man ure just a moderator, not a judge. If i care to post helpless code (even if i wont) i can post it as far as a global announcement says not to and u cant do nothing about it. Didnt want to catch with u personally in any way, but u being kinda superior just bothers me. If u think my posts are rubbish and im helfpul to none go ahead and delete them, as u have the power for such things.

 

To end it, i read your post after i posted mine. I saw u posted but had just a quick look at the code without noticing the first line of text, also thinking it is another approach similiar to my code. When i returned to the topic i read carefully what u wrote. Is this such a fatal mistake? Will i suffer the flames of hell for this?? I dont think really.

[Barand]

Being a moderator has nothing to with this. In no way am I being superior, unless you are referring to an ability to read. The problem here is that

A - you can't be bothered to read previous posts on the topic.

B - you don't give a damn whether your posted code works or not.

[Fadion]

U are being superior (at least trying to) from the fact that ive explained the whole thing (if i did a crime!!) several times and u still "accuse" me for the same things over and over. For the "problems" u mentioned, do u refer to my posts generally or for this topic only? As usually those are things i do.

 

I dont wanna keep on this conversation as its being totally off topic. If u have time to spend send me PMs and ill spend my time to explain u over and over again. I really respect your programming knowledge and for being a very helpful and active person. Dont want to change my opinion for such a not important thing.

[ameriblog]

Thanks, both of you. I have this code, for some reason it is only entering the first line into the database:

 

<?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, game_year, team1, team2, game_extra, game_extra1, game_date, game_fixed) VALUES ('$team[0]', '$score[0]', '$team[1]', '$score[1]', 2007, 0, 0, 'NA', 'NA', '" . $_GET['date'] . "', 0)" ) or die ( $conn->ErrorMsg() );
$add_rating_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );	

}
?>