Page 1 of 1

Updating Mysql with info from text file

Posted: Sun Mar 19, 2006 1:27 pm
by hiewall
I have a text file: http://www.poolsforfun.com/textfile.txt

I need to read this file and update a Mysql Database with information in this file:

Playerid
Playerinf01
Playerinf03
Playercomment ---- if there is a comment

If Playerid already exists on the Mysql database -- I want to put out a message which states that and ignore the update.

I am new to PHP ---- can someone please help ?????

Posted: Sun Mar 19, 2006 3:01 pm
by kaYak
Okay, I came up with this. I believe I tested everything except the MySQL queries.

Code: Select all

<?php

// connect to db
$link = mysql_connect("localhost", "db_username", "db_password")
   or die("Could not connect : " . mysql_error());
mysql_select_db("db_name") or die("Could not select database");

// grab the text file contents
$txt_contents = file_get_contents('http://www.poolsforfun.com/textfile.txt');

// seperate the text file contents
$info_arr = explode("\n", $txt_contents);

foreach($info_arr as $line_val){

// grab the individual values for the player and put them into an array
preg_match_all("#PLAYERID=(.+?),PLAYERINFO1=(.+?),PLAYERINFO2=,PLAYERINFO3=(.+?)#is", $line_val, $match1);

preg_match_all("#PLAYERCOMMENT=(.+?)#is", $line_val, $match2);

// make sure the first value is set
    if($match1[1][0] != ""){
        // check to see if they exists
        $query = "SELECT * FROM table_name WHERE playerid='{$match1[1][0]}'";
        $result = mysql_query($query, $link);

        $num_rows = mysql_num_rows($result);

        if($num_rows!=1){
        // since they don't exist, insert them into the db
        $query = "INSERT INTO table_name (playerid,playerinfo1,playerinfo3,playercomment) values('{$match1[1][0]}','{$match1[2][0]}','{$match1[3][0]}', '{$match2[1][0]}')";

      }
      else{
        // if the player already exists, say so, and continue
        echo("Player " . $match1[1][0] . " already exists. They were not changed.<br />\n");
      }

    }

}

// close db connection
mysql_close($link);

?>
This should work, but again, it's not fully tested. You will have to change the MySQL info to your own.

Here is a basic run-through of what it does:

It grabs the content of the text file and seperates it into an array (based on line breaks, the "\n"). For each line it uses regex to grab values from the text, similiar to how bbcode operates. It takes these values and checks the first one to see if is equal to something and then uses it to check the MySQL database for the user's existance. If the user does not exist it inserts a new row with the info.

I believe that's it. I hope it works.

edit: I added comments to explain it as well.

Posted: Mon Mar 20, 2006 12:31 pm
by hiewall
Thanks Kyle ---- It runs OK --- but does not update the database.

I know that the data is there (I did echo statements) - but no update of database.

The code is in http://www.poolsforfun.com/poolstuff.php

Thanks for your help.

Posted: Sun Mar 26, 2006 8:23 pm
by kaYak
It just appears to me that your connection information is wrong since it can't connect. Perhaps the db name and username are switched? I'm not sure. I think the code should work if the connection is fixed.