FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Getting information from a txt and storing in a database?





Diablosblizz
Hey, I have a text file (.txt) filled with some users from my site. It's a log, and I want to know if I can take these from the text file and put them into a SQL database? The format for the users are:

1username password 'Lastname' 'Firstname'

Exactly like that, of course replace the words with the username, password, last name and first name. The quotes are there, like that. All usernames start with a 1. Example of a user:

1SEFICJUN zzew 'Sefic' 'Junior'

(Don't worry, that is not his real password.) So, how can I take that string and put them in a SQL database? Please keep in mind that there are over 800 users in the text file!

Many thanks!
Ecthelion
Sure, of course you can put them into your mysql...
Just make a script that reads the log, checks if the users aren't in the DB an if not add them...
That really shouldn't be difficult...

If you drop me a sample of the file (might be a fake you make up if you don't want to release the content) with 10 names lines of data like you just said, then I'm ready to see if I can quickly make you such script.

What I would need to know is if you want to put it into an existing DB (with a structure, which I would then need to know about) or not...
Diablosblizz
If you could do this for me that would be wonderful.

Quote:
WILLIAMSR 10031 'Williams' 'Randy'
GREENC 10185 'Green' 'Craig'
CAMERONC 20203 'Cameron' 'Cathy'
HOWELLH 20496 'Howell' 'Heather'
GREENSU 23274 'Green' 'Susan'
DZIUBAP 23906 'Dziuba' 'Paul'
ADDISONJ 2428 'Addison' 'John'
BURTONB 8399 'Burton' 'Barbara'
1FISHERWIL tkmn 'Fisher' 'William'
1MALINOWSKID zedt 'Malinowski' 'David'
1ATWALMAN qskq 'Atwal' 'Manbir'
1LEHNERMAT dfef 'Lehner' 'Matt'
1WILLICHUKRA emgz 'Willichuk' 'Rachel'


13 users, just random. If possible, I will specify the database to enter all the users in. Many thanks!!!!
imagefree
it is easy to do and i can do it for you.

Please specify your requirements in detail. (pm me)
Ecthelion
Here you go...

Code:
<?php

$filename = "./txttest.txt";
$mysqlhost = "host";
$mysql_user = "db_username";
$mysql_passwd = "db_password";
$database_name = "db_name";

$recreate_table = true;
$tablename = "textdata";

if( file_exists( $filename) ){
  //Get the content of your file and put each line in an array
  $handle = fopen ($filename, "r");
  $linec = 0;
  while (!feof ($handle)) {
    $lines[$linec] = fgets($handle, 4096);
    $linec++;
  }
  fclose ($handle);
}else exit("File $filename not found");


//Connect to DB
$link = mysql_connect($mysqlhost, $mysql_user, $mysql_passwd)
    or die('Could not connect: ' . mysql_error());

//Select DB you want to add things in
mysql_select_db($database_name) or die('Could not select database');

if($recreate_table){
  //Then first remove the table
  $query = 'DROP TABLE IF EXISTS '.$tablename;
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
//  mysql_free_result($result);
  //Then make a new one with the right structure
  $query = 'CREATE TABLE '.$tablename.'(
   username varchar(25) NOT NULL,
   password varchar(32) NOT NULL,
        lname varchar(32) NOT NULL,
        fname varchar(32) NOT NULL);';
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
//  mysql_free_result($result);
}

////Now start putting everything in the table
$entrycount = 0;
for($i=0;$i<count($lines);$i++){
  $data = readContent($lines[$i]);
  if( !($data == -1) ){
    //Check if username is already in table
    $query2 = 'SELECT `username` FROM `'.$tablename.'` WHERE username = "'.$data['username'].'"';
    $result2 = mysql_query($query2);
    if($result2){ //If it's not in the table then add it
      $query = 'INSERT INTO '.$tablename." (username, password, lname, fname)
        VALUES ('".$data['username']."','".$data['password']."','".$data['lname']."','".$data['fname']."');";
      $result = mysql_query($query) or die('Query failed: ' . mysql_error());
//      mysql_free_result($result);
      $entrycount++;
    }else{ //else tell us so
      echo "user ".$data['username']." was already found in the table <br>\n";
    }
//    mysql_free_result($result2);
  }
}
mysql_close($link);



if($i == count($lines) ) echo "inserting in database finshed: $entrycount new entries were added, of a total ".count($lines)." entries in text file \n";
   
//function to retrieve username, password etc
function readContent($string){
  if(strlen($string)==0){ echo "empty string <br>\n"; $content = -1;}
  else{
  $firstb = strpos($string, ' ');
  $secondb = strpos($string, ' ', $firstb+1);
  $thirdb = strpos($string, ' ', $secondb+1);
  $content['username'] = substr($string, 1, $firstb -1);
  $content['password'] = substr($string, $firstb, ($secondb-$firstb) );
  $content['lname'] = substr($string, $secondb+2, ($thirdb-$secondb-3) ); //we don't need the ' signs
  $content['fname'] = substr($string, $thirdb+2, (strlen($string)-$thirdb-4) );
  }
  //echo " spaces found at positions $firstb, $secondb, $thirdb <br>\n";
  //echo "readcontent returned <br>".$content['username']." <br> ".$content['password']." <br> ".$content['lname']." <br> ".$content['fname']." <br>\n";
  return $content;
}
 
?>


Some comments:
* As you might see, the mysql_free_result is commented everywhere... it gave warnings and it works fine without...
* Of course you should change the variables at start Wink
* the option recreate table is set to true : this means that the scripts will drop any table with name textdata (you can change the name) and make a new one with the right structure.
After that it'll add everything in that table.
If you want to add more txt files later, you should (the next time) turn it of (except if you pass the whole textfile again)
* Normally is should check for double-entries (twice the same username) but I'm not sure it works.

Conclusion:
It just trows everything in a table, like you wanted. The "1" at the start of each username is not taken, if you want it also in the table you should change this line
Code:
  $content['username'] = substr($string, 1, $firstb -1);

to
Code:
  $content['username'] = substr($string, 0, $firstb -1);


That's all I think

(I checked and it works with the textfile you passed Wink )
Diablosblizz
Okay it works up until the First and Last name. The first name and last name are missing the first letter of the names. Example:

Quote:
WILLIAMS 10031 illiam andy


Should be Willam Randy. Any ideas?
Ecthelion
Erm..; it's strange because I checked it, but yeuh...

The error is most likely here:

Code:
  $content['username'] = substr($string, 1, $firstb -1);
  $content['password'] = substr($string, $firstb, ($secondb-$firstb) );
  $content['lname'] = substr($string, $secondb+2, ($thirdb-$secondb-3) ); //we don't need the ' signs
  $content['fname'] = substr($string, $thirdb+2, (strlen($string)-$thirdb-4) );


If what you say is right, then it should be

Code:
  $content['username'] = substr($string, 1, $firstb -1);
  $content['password'] = substr($string, $firstb, ($secondb-$firstb) );
  $content['lname'] = substr($string, $secondb+1, ($thirdb-$secondb-2) ); //we don't need the ' signs
  $content['fname'] = substr($string, $thirdb+1, (strlen($string)-$thirdb-3) );


Try it, and tell me if that fixed it...
(strange, I didn't notice this error...)
EDIT: double-strange, I checked on my pc and I get the correct output for the not-modified code ???? Strange Question
EDIT2: did you change the line to have the in the username? If you did it's probably that.
It should be
Code:
  $content['username'] = substr($string, 0, $firstb);

Not as I said in my first post, forgot to adapt the second variable.
Diablosblizz
EDIT: Okay, the first names are fixed, but now the last names are removing the last letter in their names.

Because I didn't code this I don't know why this is happening. Please help!
Ecthelion
I can't really tst it since it's working allright on my pc, but let me explain what is probably causing the problem, and how you should proceed to fix it.

The problem lies in the readContent function.
What does this function do? Well it takes a string and searches the spaces in it ' '.
Once it knows the location of these spaces, it takes substrings (parts of the original string) which should contain the information we want (the username whitout 1 the lastname and first nama without ' ...)
So the error is when it takes the substring wrongly.

Code:
function readContent($string){
  if(strlen($string)==0){ echo "empty string <br>\n"; $content = -1;}
  else{
  $firstb = strpos($string, ' ');
  $secondb = strpos($string, ' ', $firstb+1);
  $thirdb = strpos($string, ' ', $secondb+1);
  $content['username'] = substr($string, 1, $firstb -1);
  $content['password'] = substr($string, $firstb, ($secondb-$firstb) );
  $content['lname'] = substr($string, $secondb+1, ($thirdb-$secondb-2) ); //we don't need the ' signs
  $content['fname'] = substr($string, $thirdb+1, (strlen($string)-$thirdb-3) );
  }
  //echo " spaces found at positions $firstb, $secondb, $thirdb <br>\n";
  //echo "readcontent returned <br>".$content['username']." <br> ".$content['password']." <br> ".$content['lname']." <br> ".$content['fname']." <br>\n";
  return $content;
}


You say the last name misses a letter, then it means that the taken substring is too short.
The code to tak a substring is
Code:
substr($fullstring, $startposition, $length)

So if the last letter is missing, then it ends too soon, and you should do $lenght + 1.
In this case it means you should change
Code:
  $content['fname'] = substr($string, $thirdb+1, (strlen($string)-$thirdb-3) );
to
Code:
  $content['fname'] = substr($string, $thirdb+2, (strlen($string)-$thirdb-2) );

This should fix your problem.

If it does not, uncomment the lines
Code:
  //echo " spaces found at positions $firstb, $secondb, $thirdb <br>\n";
  //echo "readcontent returned <br>".$content['username']." <br> ".$content['password']." <br> ".$content['lname']." <br> ".$content['fname']." <br>\n";

When you run the script these will tell you where the blanc spaces were and what the function took as firstname, lastname, username, password.
You don't have to check the mysqltable each time then.
(don't run this on your 8OO line textfile or you would get a HUGE output, take a file of 10lines then)

Sorry I gave you a file that still needs some debugging :/
Diablosblizz
Quote:
spaces found at positions 9, 15, 26
readcontent returned
WILLIAMSR
10031
'Williams
Randy'
Query failed: 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 'Williams','Randy' ')' at line 2.


It displays that. It seems that the 'Willams Randy' still has the quotes around them. Do you know how to fix this? Many thanks!
Ecthelion
It will always display the error when there are ' in the text you enter.
'Williams
Randy'

-> you have twice such a ' still in the string.

So you have 2 options:
1. you change them to something else (for example their html equivalent, &#39; will not cause any error), then you'll never have this error again. But you will still add these ' to your db, and you don't want that anyway.
2. You change the readContent function as I explained and make sure the ' are not in the String you put in the db...
Related topics
Chatroom
How To : Secure Your PHP Website
Why You Should Never Use Flash
Fixing 'Can't Connect to database' errors with phpBB.
Problem connecting to MySQL database with PHP [RESOLVED]
saving variables
WWW.WILLWRIGHTWEBDESIGN.CO.NR
mysql database table structure
URL Parameters PHP
How competitive is PHP?
Getting Google Analytics Working
Storage of Massive Amounts of Data
The policies of: Ron Paul
An open letter to the world (important stuff)
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.