Reading a text file

Aravinthan · August 11, 2009

Oups nevermind.

I didnt select the new code well

I will work on it and I will keep you up to date with my progres..

Thanks for your help!!

Ara

Aravinthan · August 11, 2009

Hi ok so I added the 20 lines:

<?php
$lines = file('players.txt');

//$myLine = getLine(1);
//echo $myLine[0]." ".$myLine[1]."etc".$myLine[10]."<br>";

function getLine($line)
{
   global $lines;
   return explode(" ", trim(str_replace("  "," ",$lines[$line])));
}


//other extended option
/*
$player = getPlayer();
echo $player['Name'];
echo $player['num1'][0];
echo $player['num1'][1];
echo $player['num1'][2];
*/

$tmpLine = 0;
$player = 1;
echo "<pre>";
while($details = getPlayer($player)) //loop until false
{
var_dump($details); //dump data // change to echo data correctly
$player++; //edit: oops need this line!
}

function getPlayer($player)
{
global $lines;
$player--;
$player = $player * 20;
//Check first line
if(empty($lines[$player+1])){
	return false;
}
//Setup details
$details = array();
$details['num1'] = getLine($player+1);
$details['num2'] = getLine($player+2);
$details['num3'] = getLine($player+3);
$details['num4'] = getLine($player+4);
$details['num5'] = getLine($player+5);
$details['num6'] = getLine($player+6);
$details['num7'] = getLine($player+7);
$details['num8'] = getLine($player+;
$details['num9'] = getLine($player+9);
$details['num10'] = getLine($player+10);
$details['num11'] = getLine($player+11);
$details['num12'] = getLine($player+12);
$details['num13'] = getLine($player+13);
$details['Name'] = $lines[$player+14]; //Milan Hnilicka
$details['Blank'] = $lines[$player+15]; //?
$details['drafted'] = $lines[$player+16]; //drafted
$details['num17'] = getLine($player+17);
$details['num18'] = getLine($player+18);
$details['num19'] = getLine($player+19);
$details['num20'] = getLine($player+20);
return $details;
}

?>

And the output can be seen here:

http://liguedhockeysimule.x10hosting.com/converter/texttosql.php

It splits the data exactly how I want!!! Thanks so much.

But I cant figure out how you echo the data out? is it using var_dump?

AAnd if it is, How can I change it to a MYSQL Update type of format?

Thanks for your time,

Ara

MadTechie · August 11, 2009

Your welcome, you may want to num1 to whatever that data is (to make later debugging easier) and add the other lines,

then its a case of creating you echo, ie

echo "INSERT INTO table SET (name,drafted) VALUES ('{$details['Name']}','{$details['drafted']}')";

The other good thing is now just say drafted was going to be a boolean you could add that logic before adding it to the new array

ie

if($lines[$player+16] == "drafted")
{
   $details['drafted'] = "1"; //is drafted
}else{
   $details['drafted'] = "0"; //is NOT drafted
}

of course this could be true/false but you get the idea

and any unneeded lines, just don't add to the new array

MadTechie · August 11, 2009

LOL, i was just typing an example (see above)

for the numbers use

$details['num1'][0];
$details['num1'][1];
$details['num1'][2];

EDIT: i'm a dumb arse! (fixed)

Aravinthan · August 11, 2009

Wow ok,

Thanks but for exemple,

The Line 1, there is 10 Datas in it.

So do I do :

echo "INSERT INTO table SET (stuffs,stuffs,etc) VALUES ('{$details['num1']}','{$details['num1']}')";

I dont think thats right no?

And for the Boolean, part THANKS!!!!

That would help me alot during the processing of the data.

Wow seriously, I never tought I would be able to finish this coding so fast!! Man seriously,!!! I am bemused by your talent

And so Grateful that you spent alot of time for me!!!!

Aravinthan · August 11, 2009

LOL, i was just typing an example (see above)

for the numbers use
$details['drafted']['num1'][0];
$details['drafted']['num1'][1];
$details['drafted']['num1'][2];

EDIT:

LOL you read my mind or something? :confused: lol!!!

Ok so the code would be:

echo "INSERT INTO table SET (stuffs,stuffs,etc) VALUES ('{$details['num1'][0];}','{$details['num1'][1],etc}')";

Right?

WOW MAN thanks again, I cant thank you enough lol!

MadTechie · August 11, 2009

that's almost correct

it should be $details['num1'][0];

however I'm lazy.. so

$n1 = implode(",", $details['num1']);
echo "INSERT INTO table SET (stuff1,stuff2,stuff3,stuff4,stuff5,stuff6,stuff7,stuff8,stuff9,stuff10) VALUES ($n1)";
//Numbers don't require quotes

Aravinthan · August 11, 2009

THanks,

And you dont have to insert all, lol I will do it... I have to do something atleast

Thanks once again!!!

Seriously wow!!! Like in 3 days!!! Never tought possible!! lol

MadTechie · August 11, 2009

The main thing about this is setting up in away where if theres a problem you can find it easily and if needed just add another function to format the data the way you need it.. that's why I broke I up into smaller parts

oow solved coolie

wildteen88 · August 11, 2009

Had nothing to do so I came up with this. Mine generates the whole query

<?php

function sql_safe($value)
{
    $value = trim($value);

    if(is_numeric($value))
        return $value;
    else
        return "'$value'";
}


$players_data_file = file('data.txt');

// remove first line from file, this is line holds the number of players
$total_players = array_shift($players_data_file);

// DO NOT CHANGE THIS. THIS IS SET UP TO MATCH THE DATA IN THE DATA.TXT FILE
$fields = array( array('shooting', 'playmaking', 'stickhandling', 'checking', 'marking', 'hitting', 'skating', 'endurance', 'penalty', 'faceoffs'),
                 array('leadership', 'strength', 'potentiel', 'consistency', 'greed', 'fighting', 'click', 'team', 'main_position', 'country', 'handed'),
                 array('birth_year', 'birth_day', 'birth_month', 'salary', 'contract_lenght', 'draft_year', 'draft_round', 'drafted_by', 'rights'),
                 array('week_goals', 'week_assists', 'week_gwg', 'month_goals'),
                 array('month_assists', 'month_gwg'),
                 array('record_goals', 'record_assists', 'record_points', 'no_trade_switch', 'two-way_switch', 'player-team_option'),
                 array('status', 'rookie', 'considering_offer_data', 'team_offering', 'amount_time_spent_considering', 'injury'),
                 array('line8'),
                 array('line9'),
                 array('line10'),
                 array('goal_streak', 'point_streak', 'total_gp', 'suspended_game', 'training', 'weight', 'height', 'status_in_organization'),
                 array('best_streak_games', 'best_streak_gwg', 'best_streak_assists', 'best_streak_points', 'best_streak_goals'),
                 array('line13'),
                 array('name'),
                 array('line15'),
                 array('drafeted'),
                 array('line17'),
                 array('line18'),
                 array('line19'),
                 array('attitude', 'alternate_position', 'nhl_rights', 'injury_prone', 'overral_draft')
                );

// split every 20 lines into a new array
$all_players_data = array_chunk($players_data_file, 20);

// loop through each block of 20 lines
foreach($all_players_data as $player_data)
{
    // magically make our update query
    $sql = 'UPDATE player_table_name_here ';

    // here we're looping through each line
    foreach($player_data as $line_key => $line_data)
    {
        // if we're not on line 8, 9, 10, 13, 14 or 15
        // turn line data into an array
        if(!in_array($line_key, array(7, 8, 9, 12, 13, 14)))
        {
            $field_data = explode('  ', $line_data);

            // match up the data with the field array
            // this is where our query is generated
            foreach($field_data as $field_key => $field_value)
            {
                if(isset($fields[$line_key][$field_key]))
                    $sql .= "`" . $fields[$line_key][$field_key] . "`=" . sql_safe($field_value) . ', ';
            }

            $sql .= "\n";
        }
        // add the whole line into the query
        else
        {
            $sql .= "`" . $fields[$line_key][0] . "`=" . sql_safe($line_data) . ", \n";
        }
    }

    echo '<pre>' . print_r($sql, true) . '</pre>';
}

?>

MadTechie · August 11, 2009

Very nice wildteen88 as always.. was kinda tempted to write a full script myself but gave a lesson instead lol..

Love the line

if(!in_array($line_key, array(7, 8, 9, 12, 13, 14)))

nice idea

Aravinthan · August 12, 2009

Wow, WildTeen,

Thanks so much!!!

And you too Mad!!!

But Wild Teen, is it possible:

If there is no value in the Table, instead of updating, it Inserts?

And also, if there is no Table with that name it Creates the table?

I am feeling kinda of lazy for creating each one by itself.... lol

If its not possible, well thats not much of a problem.

Thanks once again guys for your amazing help!!

wildteen88 · August 12, 2009

If there is no value in the Table, instead of updating, it Inserts?

Yes change this:

    // magically make our update query
    $sql = 'UPDATE player_table_name_here ';

To:

    // get the player name from the array
    $player_name = sql_safe($player_data[13]);

    // check to see if the player already exist
    $user_exists_sql = "SELECT `name` FROM `'.$player_table_name.'` WHERE `name`=$player_name";
    $result = mysql_query($user_exists_sql);

    // if the user does not exist, use an INSERT STATEMENT
    // we'll add the user into the database
    if(mysql_num_rows($result) == 0)
    {
        $sql = 'INSERT INTO `'.$player_table_name.'` SET ';
        $update_user = false;
    }
    // user does exist so we'll use an UPDATE statement
    // update the existing user
    else
    {
        $sql = 'UPDATE `'.$player_table_name.'` ';
        $update_user = true;
    }

Another change you need to make is to change this

        // add the whole line into the query
        else
        {
            $sql .= "`" . $fields[$line_key][0] . "`=" . sql_safe($line_data) . ", \n";
        }

to

        // add the whole line into the query
        else
        {
            // if we're updating the user skip line 14
            if( $update_user && $line_key == 13)
                continue;

            $sql .= "`" . $fields[$line_key][0] . "`=" . sql_safe($line_data) . ", \n";
        }

And finally change this

    echo '<pre>' . print_r($sql, true) . '</pre>';

to

    // remove the last three characters from $sql
    $sql  = substr($sql, 0, -3);

    // add the WHERE clause only if we are updating the user
    if($update_user)
        $sql .= " WHERE name=$player_name";

    echo '<pre>' . print_r($sql, true) . '</pre>';

You'll now want to add

// DONT FORGET TO CONNECT TO MYSQL!
$conn = mysql_connect('host', 'user', 'pass');
mysql_select_db('database_name');

$player_table_name = 'NAME OF USER TABLE IN DATABASE';

Before the following line

$players_data_file = file('data.txt');

Thats it. The query will now be intelligently generated. If the user already exists it'll perform an update query. If the user does not exist it'll insert the new user

And also, if there is no Table with that name it Creates the table?

I'll leave that to you for creating the table. See what you can come up with. I'll give you a hint you'll want to run an CREATE TABLE IF NOT EXISTS query.

Aravinthan · August 12, 2009

Hi,

Thansk for your help,

so this is what I got:

<?php

function sql_safe($value)
{
    $value = trim($value);

    if(is_numeric($value))
        return $value;
    else
        return "'$value'";
}

// DONT FORGET TO CONNECT TO MYSQL!
$conn = mysql_connect('localhost', 'liguehs_ara', '1992arayugi');
mysql_select_db('liguehs_league');

$player_table_name = 'players';

$check="SELECT * FROM $player_table_name";

$result=@mysql_query($check);

if (!$result){
$make_table = 'CREATE TABLE IF NOT EXISTS  `liguehs_league`.`players`
(`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
`shooting` INT(5) NOT NULL,
`playmaking` INT(5) NOT NULL,
`stickhandling` INT(5) NOT NULL,
`checking` INT(5) NOT NULL,
`marking` INT(5) NOT NULL,
`hitting` INT(5) NOT NULL,
`skating` INT(5) NOT NULL,
`endurance` INT(5) NOT NULL,
`penalty` INT(5) NOT NULL,
`faceoffs` INT(5) NOT NULL,
`leadership` INT(5) NOT NULL,
`strength` INT(5) NOT NULL,
`potentiel` INT(5) NOT NULL,
`consistency` INT(5) NOT NULL,
`greed` INT(5) NOT NULL,
`fighting` INT(5) NOT NULL,
`click` INT(5) NOT NULL,
`team` INT(5) NOT NULL,
`main_position` INT(5) NOT NULL,
`country` INT(5) NOT NULL,
`birth_year` INT(5) NOT NULL,
`birth_day` INT(5) NOT NULL,
`birth_month` INT(5) NOT NULL,
`salary` INT(11) NOT NULL,
`contract_lenght` INT(5) NOT NULL,
`draft_year` INT(5) NOT NULL,
`draft_round` INT(5) NOT NULL,
`drafted_by` INT(5) NOT NULL,
`rights` INT(5) NOT NULL,
`week_goals` INT(5) NOT NULL,
`week_assists` INT(5) NOT NULL,
`month_goals` INT(5) NOT NULL,
`month_assists` INT(5) NOT NULL,
`month_gwg` INT(5) NOT NULL,
`record_goals` INT(5) NOT NULL,
`record_assists` INT(5) NOT NULL,
`record_points` INT(5) NOT NULL,
`no_trade_switch` INT(5) NOT NULL,
`two-way_switch` INT(5) NOT NULL,
`player-team_option` INT(5) NOT NULL,
`status` INT(5) NOT NULL,
`rookie` INT(5) NOT NULL,
`rconsidering_offer_data` INT(11) NOT NULL,
`team_offering` INT(5) NOT NULL,
`amount_time_spent_considering` INT(5) NOT NULL,
`injury` INT(5) NOT NULL,
`line8` INT(5) NOT NULL,
`line9` INT(5) NOT NULL,
`line10` INT(5) NOT NULL,
`goal_streak` INT(5) NOT NULL,
`point_streak` INT(5) NOT NULL,
`total_gp` INT(5) NOT NULL,
`point_streak` INT(5) NOT NULL,
`suspended_game` INT(5) NOT NULL,
`training` INT(5) NOT NULL,
`weight` INT(5) NOT NULL,
`height` INT(5) NOT NULL,
`status_in_organization` INT(5) NOT NULL,
`best_streak_games` INT(5) NOT NULL,
`best_streak_gwg` INT(5) NOT NULL,
`best_streak_assists` INT(5) NOT NULL,
`best_streak_points` INT(5) NOT NULL,
`best_streak_goals` INT(5) NOT NULL,
`line13` INT(5) NOT NULL,
`name` VARCHAR(100) NOT NULL,
`line15` INT(5) NOT NULL,
`drafted` VARCHAR(100) NOT NULL,
`line17` INT(5) NOT NULL,
`line18` INT(5) NOT NULL,
`line19` INT(5) NOT NULL,
`attitude` INT(5) NOT NULL,
`alternate_position` INT(5) NOT NULL,
`nhl_rights` INT(5) NOT NULL,
`injury_prone` INT(5) NOT NULL,
`overral_draft INT(5) NOT NULL)
ENGINE = MyISAM';
}

$players_data_file = file('players.txt');

// remove first line from file, this is line holds the number of players
$total_players = array_shift($players_data_file);

// DO NOT CHANGE THIS. THIS IS SET UP TO MATCH THE DATA IN THE DATA.TXT FILE
$fields = array( array('shooting', 'playmaking', 'stickhandling', 'checking', 'marking', 'hitting', 'skating', 'endurance', 'penalty', 'faceoffs'),
                 array('leadership', 'strength', 'potentiel', 'consistency', 'greed', 'fighting', 'click', 'team', 'main_position', 'country', 'handed'),
                 array('birth_year', 'birth_day', 'birth_month', 'salary', 'contract_lenght', 'draft_year', 'draft_round', 'drafted_by', 'rights'),
                 array('week_goals', 'week_assists', 'week_gwg', 'month_goals'),
                 array('month_assists', 'month_gwg'),
                 array('record_goals', 'record_assists', 'record_points', 'no_trade_switch', 'two-way_switch', 'player-team_option'),
                 array('status', 'rookie', 'considering_offer_data', 'team_offering', 'amount_time_spent_considering', 'injury'),
                 array('line8'),
                 array('line9'),
                 array('line10'),
                 array('goal_streak', 'point_streak', 'total_gp', 'suspended_game', 'training', 'weight', 'height', 'status_in_organization'),
                 array('best_streak_games', 'best_streak_gwg', 'best_streak_assists', 'best_streak_points', 'best_streak_goals'),
                 array('line13'),
                 array('name'),
                 array('line15'),
                 array('drafeted'),
                 array('line17'),
                 array('line18'),
                 array('line19'),
                 array('attitude', 'alternate_position', 'nhl_rights', 'injury_prone', 'overral_draft')
                );

// split every 20 lines into a new array
$all_players_data = array_chunk($players_data_file, 20);

// loop through each block of 20 lines
foreach($all_players_data as $player_data)
{
// check to see if the player already exist
    $user_exists_sql = "SELECT `name` FROM `'.$player_table_name.'` WHERE `name`=$player_name";
    $result = mysql_query($user_exists_sql);

    // if the user does not exist, use an INSERT STATEMENT
    // we'll add the user into the database
    if(mysql_num_rows($result) == 0)
    {
        $sql = 'INSERT INTO `'.$player_table_name.'` SET ';
        $update_user = false;
    }
    // user does exist so we'll use an UPDATE statement
    // update the existing user
    else
    {
        $sql = 'UPDATE `'.$player_table_name.'` ';
        $update_user = true;
    }

    // here we're looping through each line
    foreach($player_data as $line_key => $line_data)
    {
        // if we're not on line 8, 9, 10, 13, 14 or 15
        // turn line data into an array
        if(!in_array($line_key, array(7, 8, 9, 12, 13, 14)))
        {
            $field_data = explode('  ', $line_data);

            // match up the data with the field array
            // this is where our query is generated
            foreach($field_data as $field_key => $field_value)
            {
                if(isset($fields[$line_key][$field_key]))
                    $sql .= "`" . $fields[$line_key][$field_key] . "`=" . sql_safe($field_value) . ', ';
            }

            $sql .= "\n";
        }
// add the whole line into the query
        else
        {
            // if we're updating the user skip line 14
            if( $update_user && $line_key == 13)
                continue;

            $sql .= "`" . $fields[$line_key][0] . "`=" . sql_safe($line_data) . ", \n";
        }

    }

// remove the last three characters from $sql
    $sql  = substr($sql, 0, -3);

    // add the WHERE clause only if we are updating the user
    if($update_user)
        $sql .= " WHERE name=$player_name";

    echo '<pre>' . print_r($sql, true) . '</pre>';


}

?>

But I get this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/liguehs/public_html/converter/test.php on line 144

http://liguedhockeysimule.x10hosting.com/converter/test.php

Line 144:

if(mysql_num_rows($result) == 0)

Thanks for your help,

Ara

MadTechie · August 12, 2009

$user_exists_sql = "SELECT `name` FROM `'.$player_table_name.'` WHERE `name`=$player_name";

should be

$user_exists_sql = "SELECT `name` FROM `$player_table_name` WHERE `name`='$player_name' ";

Aravinthan · August 12, 2009

Still the same thing:

http://liguedhockeysimule.x10hosting.com/converter/test.php

Aravinthan · August 12, 2009

OK so after checkig the code,

I realised that:

$player_name

Isnt declared anywhere... could that be the cause?

MadTechie · August 12, 2009

EDIT: ignore this post

$player_name = sql_safe($player_data[13]);

Aravinthan · August 12, 2009

Hi,

Thanks for the quick replies, but same result:

http://liguedhockeysimule.x10hosting.com/converter/test.php

wildteen88 · August 12, 2009

You've left of out this line:

$player_name = sql_safe($player_data[13]);

Add it before these lines

// check to see if the player already exist
    $user_exists_sql = "SELECT `name` FROM `$player_table_name` WHERE `name`=$player_name";

So its like

$player_name = sql_safe($player_data[13]);
// check to see if the player already exist
    $user_exists_sql = "SELECT `name` FROM `$player_table_name` WHERE `name`=$player_name";

Aravinthan · August 12, 2009

Oh oups...

But still same error....

http://liguedhockeysimule.x10hosting.com/converter/test.php

Code:

<?php

function sql_safe($value)
{
    $value = trim($value);

    if(is_numeric($value))
        return $value;
    else
        return "'$value'";
}

// DONT FORGET TO CONNECT TO MYSQL!
$conn = mysql_connect('localhost', 'liguehs_ara', '1992arayugi');
mysql_select_db('liguehs_league');

$player_table_name = 'players';

$check="SELECT * FROM $player_table_name";

$result=@mysql_query($check);

if (!$result){
$make_table = 'CREATE TABLE IF NOT EXISTS  `liguehs_league`.`players`
(`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
`shooting` INT(5) NOT NULL,
`playmaking` INT(5) NOT NULL,
`stickhandling` INT(5) NOT NULL,
`checking` INT(5) NOT NULL,
`marking` INT(5) NOT NULL,
`hitting` INT(5) NOT NULL,
`skating` INT(5) NOT NULL,
`endurance` INT(5) NOT NULL,
`penalty` INT(5) NOT NULL,
`faceoffs` INT(5) NOT NULL,
`leadership` INT(5) NOT NULL,
`strength` INT(5) NOT NULL,
`potentiel` INT(5) NOT NULL,
`consistency` INT(5) NOT NULL,
`greed` INT(5) NOT NULL,
`fighting` INT(5) NOT NULL,
`click` INT(5) NOT NULL,
`team` INT(5) NOT NULL,
`main_position` INT(5) NOT NULL,
`country` INT(5) NOT NULL,
`birth_year` INT(5) NOT NULL,
`birth_day` INT(5) NOT NULL,
`birth_month` INT(5) NOT NULL,
`salary` INT(11) NOT NULL,
`contract_lenght` INT(5) NOT NULL,
`draft_year` INT(5) NOT NULL,
`draft_round` INT(5) NOT NULL,
`drafted_by` INT(5) NOT NULL,
`rights` INT(5) NOT NULL,
`week_goals` INT(5) NOT NULL,
`week_assists` INT(5) NOT NULL,
`month_goals` INT(5) NOT NULL,
`month_assists` INT(5) NOT NULL,
`month_gwg` INT(5) NOT NULL,
`record_goals` INT(5) NOT NULL,
`record_assists` INT(5) NOT NULL,
`record_points` INT(5) NOT NULL,
`no_trade_switch` INT(5) NOT NULL,
`two-way_switch` INT(5) NOT NULL,
`player-team_option` INT(5) NOT NULL,
`status` INT(5) NOT NULL,
`rookie` INT(5) NOT NULL,
`rconsidering_offer_data` INT(11) NOT NULL,
`team_offering` INT(5) NOT NULL,
`amount_time_spent_considering` INT(5) NOT NULL,
`injury` INT(5) NOT NULL,
`line8` INT(5) NOT NULL,
`line9` INT(5) NOT NULL,
`line10` INT(5) NOT NULL,
`goal_streak` INT(5) NOT NULL,
`point_streak` INT(5) NOT NULL,
`total_gp` INT(5) NOT NULL,
`point_streak` INT(5) NOT NULL,
`suspended_game` INT(5) NOT NULL,
`training` INT(5) NOT NULL,
`weight` INT(5) NOT NULL,
`height` INT(5) NOT NULL,
`status_in_organization` INT(5) NOT NULL,
`best_streak_games` INT(5) NOT NULL,
`best_streak_gwg` INT(5) NOT NULL,
`best_streak_assists` INT(5) NOT NULL,
`best_streak_points` INT(5) NOT NULL,
`best_streak_goals` INT(5) NOT NULL,
`line13` INT(5) NOT NULL,
`name` VARCHAR(100) NOT NULL,
`line15` INT(5) NOT NULL,
`drafted` VARCHAR(100) NOT NULL,
`line17` INT(5) NOT NULL,
`line18` INT(5) NOT NULL,
`line19` INT(5) NOT NULL,
`attitude` INT(5) NOT NULL,
`alternate_position` INT(5) NOT NULL,
`nhl_rights` INT(5) NOT NULL,
`injury_prone` INT(5) NOT NULL,
`overral_draft INT(5) NOT NULL)
ENGINE = MyISAM';
}

$players_data_file = file('players.txt');

// remove first line from file, this is line holds the number of players
$total_players = array_shift($players_data_file);

// DO NOT CHANGE THIS. THIS IS SET UP TO MATCH THE DATA IN THE DATA.TXT FILE
$fields = array( array('shooting', 'playmaking', 'stickhandling', 'checking', 'marking', 'hitting', 'skating', 'endurance', 'penalty', 'faceoffs'),
                 array('leadership', 'strength', 'potentiel', 'consistency', 'greed', 'fighting', 'click', 'team', 'main_position', 'country', 'handed'),
                 array('birth_year', 'birth_day', 'birth_month', 'salary', 'contract_lenght', 'draft_year', 'draft_round', 'drafted_by', 'rights'),
                 array('week_goals', 'week_assists', 'week_gwg', 'month_goals'),
                 array('month_assists', 'month_gwg'),
                 array('record_goals', 'record_assists', 'record_points', 'no_trade_switch', 'two-way_switch', 'player-team_option'),
                 array('status', 'rookie', 'considering_offer_data', 'team_offering', 'amount_time_spent_considering', 'injury'),
                 array('line8'),
                 array('line9'),
                 array('line10'),
                 array('goal_streak', 'point_streak', 'total_gp', 'suspended_game', 'training', 'weight', 'height', 'status_in_organization'),
                 array('best_streak_games', 'best_streak_gwg', 'best_streak_assists', 'best_streak_points', 'best_streak_goals'),
                 array('line13'),
                 array('name'),
                 array('line15'),
                 array('drafeted'),
                 array('line17'),
                 array('line18'),
                 array('line19'),
                 array('attitude', 'alternate_position', 'nhl_rights', 'injury_prone', 'overral_draft')
                );

// split every 20 lines into a new array
$all_players_data = array_chunk($players_data_file, 20);

// loop through each block of 20 lines
foreach($all_players_data as $player_data)
{
// check to see if the player already exist
$player_name = sql_safe($player_data[13]);
    $user_exists_sql = "SELECT `name` FROM `$player_table_name` WHERE `name`='$player_name' ";
    $result = mysql_query($user_exists_sql);

    // if the user does not exist, use an INSERT STATEMENT
    // we'll add the user into the database
    if(mysql_num_rows($result) == '0')
    {
        $sql = 'INSERT INTO `$player_table_name` SET ';
        $update_user = false;
    }
    // user does exist so we'll use an UPDATE statement
    // update the existing user
    else
    {
        $sql = 'UPDATE `$player_table_name` ';
        $update_user = true;
    }

    // here we're looping through each line
    foreach($player_data as $line_key => $line_data)
    {
        // if we're not on line 8, 9, 10, 13, 14 or 15
        // turn line data into an array
        if(!in_array($line_key, array(7, 8, 9, 12, 13, 14)))
        {
            $field_data = explode('  ', $line_data);

            // match up the data with the field array
            // this is where our query is generated
            foreach($field_data as $field_key => $field_value)
            {
                if(isset($fields[$line_key][$field_key]))
                    $sql .= "`" . $fields[$line_key][$field_key] . "`=" . sql_safe($field_value) . ', ';
            }

            $sql .= "\n";
        }
// add the whole line into the query
        else
        {
            // if we're updating the user skip line 14
            if( $update_user && $line_key == 13)
                continue;

            $sql .= "`" . $fields[$line_key][0] . "`=" . sql_safe($line_data) . ", \n";
        }

    }

// remove the last three characters from $sql
    $sql  = substr($sql, 0, -3);

    // add the WHERE clause only if we are updating the user
    if($update_user)
        $sql .= " WHERE name=$player_name";

    echo '<pre>' . print_r($sql, true) . '</pre>';


}

?>

MadTechie · August 12, 2009

 $user_exists_sql = "SELECT `name` FROM `'.$player_table_name.'` WHERE `name`=$player_name";

single quotes (wrong way around)?

 $user_exists_sql = "SELECT `name` FROM `$player_table_name` WHERE `name`='$player_name' ";

Aravinthan · August 12, 2009

Wow this is annoying, so close...

No still the same thing Mad lol....

:facewall: :facewall:

wildteen88 · August 12, 2009

 $user_exists_sql = "SELECT `name` FROM `'.$player_table_name.'` WHERE `name`=$player_name";

single quotes (wrong way around)?

 $user_exists_sql = "SELECT `name` FROM `$player_table_name` WHERE `name`='$player_name' ";

No, it should be

 $user_exists_sql = "SELECT `name` FROM `$player_table_name` WHERE `name`=$player_name ";

$player_name already has the quotes.

Also these liines

if(mysql_num_rows($result) == '0')
    {
        $sql = 'INSERT INTO `$player_table_name` SET ';
        $update_user = false;
    }
    // user does exist so we'll use an UPDATE statement
    // update the existing user
    else
    {
        $sql = 'UPDATE `$player_table_name` ';
        $update_user = true;
    }

Need to be

if(mysql_num_rows($result) == '0')
    {
        $sql = "INSERT INTO `$player_table_name` SET ";
        $update_user = false;
    }
    // user does exist so we'll use an UPDATE statement
    // update the existing user
    else
    {
        $sql = "UPDATE `$player_table_name` ";
        $update_user = true;
    }

MadTechie · August 12, 2009

Just read the sql_safe function

and was going to say that

Sign In

Reading a text file

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information