Parsing a .txt to fit into database

Mark_Nordstrom

Well I got a database called Games. I cant seem to get this file to upload to it showing all the stats from it. I tried entering as a blob but its not tab delimited or anything.

The file is attached. Anyone got any idea how to upload this to mysql using PHP and have it enter in the database as 1 game but show both team stats ??

tekky

considering the size of the file, I would just Comma delimit it by hand and then parse it....

Mark_Nordstrom

The size does not really matter. What I need is to be able to enter it into a blob or something and be able to view it like it is when you open it in notepad. But I wanna view it on a php page.

mrhappiness

$content=file('chicago.txt');
unset($content[1]);
$tabbed=implode("",preg_replace('%[ ]{2,}%s', "\t", $content));
preg_match_all("%(.*)\t(.*)\t(.*)\t(.*)%miU", $tabbed, $matches);
echo '<table border="1">
         <tr>
             <th>'.$matches[1][0].'</th>
             <th>'.$matches[2][0].'</th>
             <th>'.$matches[3][0].'</th>
         </tr>';
for ($i=1; $i<count($matches[1]);$i++)
  echo '<tr>
             <td>'.$matches[1][$i].'</td>
             <td>'.$matches[2][$i].'</td>
             <td>'.$matches[3][$i].'</td>
        </tr>';
echo '</table>';

hth

Mark_Nordstrom

What do i do with that 🙁

mrhappiness

you see the expected result?

i think it's quite easy now to find out how to save this into a table

Mark_Nordstrom

I can get the file into a blob on the database but how do I show it on a table in PHP like it is when you open it ??

Also I see what your saying with that post but how does that select the file from the DB ?

ymir

If you just want to enter the text file whole you do not need anything fancy, just a text field with an id and maybe a descriptive title in SQL.

CREATE TABLE files (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR( 255 ) NOT NULL ,
contents TEXT NOT NULL ,
PRIMARY KEY ( id )
);

If the files are going to be similar to you attachment just use file_get_contents

$contents = file_get_contents('minnesota@chicago.txt');

mysql_query("insert into files (title, contents VALUES ('Minnesota @ Chicago 11/11/03', addslashes($contents)") or die(mysql_error());

When you want to access it via browser just use a <pre> </pre> tags to display it.

mrhappiness

it doesn't do a select, that's right

may i suggest creating a database layout like this:

table team: team_id | team_name | even more data

table game: game_id | team_id (team1) | team_id (team2) | game_date | even more data like start date, location, people in stadium

after having prepared the data in the textfile like i did, you can insert it into these tables and do a select using JOINS taht will fetch you all data you want to have

of course you can combine the tables game and result

and of course you can read the file like i did, store everything i output via echo in a variable and store this variable in a text file in your database

with all of these possibilities you can output the data the way you want it tio appear on screen and you can make use of good markup 🙂

Mark_Nordstrom

I created all the tables needed no idea how to even start the rest lol.

mrhappiness

you know how to read the teams's names

so you can perform a select to retrieve the corresponding team_id values from table team

this values get inserted in table game and vie mysql_insert_id you retrieve the autromatically created game_id

now you have everything you need to fill the table result (game_id from mysql_insert_id, team_id from the SELECT and the values "point", "blocks", ... are stored in the array)

Mark_Nordstrom

I cant get this lol. ICQ me sometime tommorrow if you can plz.

mrhappiness

maybe this helps

//getting the teams's names
$team1=$matches[2][0];
$team2=$matches[3][0];

//querying their ids
$sql="SELECT 
	team_id, team_name
FROM 
	team
WHERE 
	team_name IN ('".$team1."', '".$team2."')";
$result=mysql_query($sql) or die(mysql_error());
while ($row=mysql_fetch_assoc($result))
	$teams[]=$row;

//order them correct
if ($teams[0]['team_name']!=$matches[2][0])
	list($teams[0], $teams[1])=array($teams[1], $teams[0]);

//insert game
$sql='INSERT INTO game
	SET team1='.$teams[0]['team_id'].', team2='.$teams[0]['team_id'];
mysql_query($sql) or die(mysql_error());

//retrieve game_id
$game=mysql_insert_id();

//insert data
for ($i=1; $i<count($matches[2]); $i++)
	$data[]=$matches[1][$i].="'".$matches[2][$i]."'";
$sql='INSERT INTO result
	SET game_id='.$game.', team1='.$teams[0]['team_id'].', '.implode(', ', $data);
//test output
echo '<hr />'.$sql.'<p />';
mysql_query($sql) or die(mysql_error());

//the same for team2
for ($i=1; $i<count($matches[3]); $i++)
	$data[]=$matches[1][$i].="'".$matches[3][$i]."'";
$sql='INSERT INTO result
	SET game_id='.$game.', team1='.$teams[1]['team_id'].', '.implode(', ', $data);
//test output
echo $sql.'<hr />';
mysql_query($sql) or die(mysql_error());

without any warranty, possibly existing errors are for learning purpose 😉

Mark_Nordstrom

getting this error now

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''38', Bench Scoring'9', Field Goals'18/47', Field Goal %'.383',

Its reading the file but its getting an error once it gets in the middle of it

mrhappiness

seems like you have to change

$data[]=$matches[1][$i].="'".$matches[2][$i]."'";

$data[]=$matches[1][$i].="='".$matches[2][$i]."'";

and one more exercise: since you use something like Off. Rebounds and this is not a valid column name you have to think about this 🙂

Mark_Nordstrom

I know theres a way to take the . out of the .txt file using Fwrite but have no clue how lol. Im still getting same error.

INSERT INTO result SET game_id=9, team1=1, Points'38', Bench Scoring'9', Field Goals'18/47', Field Goal %'.383', 3 Point FG'2/13', 3 Point %'.154', Free Throws'0/8', Free Throw %'.000', Rebounds'24', Off. Rebounds'1', Def. Rebounds'23', Blocks'7', Steals'7', Assists'11', Turnovers'17', Fouls'6'
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''38', Bench Scoring'9', Field Goals'18/47', Field Goal %'.383',

Also for some reason under Table game its using the same id for team1 and team 2

mrhappiness

well, both times you insert $team[0]['team_id']

use $team[1]['team_id'] the second time

post your code between //insert data und //test output please

Mark_Nordstrom

[code=php]
//insert data
for ($i=1; $i<count($matches[2]); $i++)
$data[]=$matches[1][$i].="='".$matches[2][$i]."'";
$sql='INSERT INTO result
SET game_id='.$game.', team1='.$teams[0]['team_id'].', '.implode(', ', $data);
//test output
echo '<hr />'.$sql.'<p />';
mysql_query($sql) or die(mysql_error());

//the same for team2
for ($i=1; $i<count($matches[3]); $i++)
$data[]=$matches[1][$i].="'".$matches[3][$i]."'";
$sql='INSERT INTO result
SET game_id='.$game.', team1='.$teams[1]['team_id'].', '.implode(', ', $data);
//test output
echo $sql.'<hr />';
mysql_query($sql) or die(mysql_error());
[/code]

mrhappiness

you have to add the = for team2 too (two lines below //the same for team2)

what does it ouptput?

Mark_Nordstrom

nope same error go to http://mfl.blindsideblitz.com/test1.php thats the page it is.