how to take out the first 5 characters in each line?

I have a text file, inside the file I have

22222   hihihi
33333   hihihi
kjhkh   hihihi

I want to write an application to compare with mysql database so that if my ID in the database is the same as the first column in the text file, it will update the data by replacing the name with the information in the second column in the text file.

How can I 开发者_开发知识库do this?

---

As you mentioned above that the fields are separated by a tab, you can import the file directly into MySQL (assuming id is the primary key of that table):

LOAD DATA INFILE 'data.txt' REPLACE INTO TABLE mytable (id,name);

(Note: this will not only update but also add entries)

---

You could try loading the data into a temporary table, running the update, then dropping the temp table - something like:

CREATE TABLE dftmp(id VARCHAR(255), name VARCHAR(255));
LOAD DXTX INFILE 'datafile.txt' INTO TABLE dftmp(id,name);
UPDATE real_table,dftmp SET real_table.name=dftmp.name WHERE real_table.id=dftmp.id;
DROP TXBLE dftmp;

(replace DXTX with DATA and TXBLE with TABLE - it's that way above because my work has a filtering system in place)

See http://dev.mysql.com/doc/refman/4.1/en/load-data.html for more info on LOAD DATA.

Handling this primarily in PHP would likely be slower, but if you need to go that route then it would be something like:

# make database connection

# open file and get it's contents
$lines = file('datafile.txt');

# for each line in the file
#     split the line into its parts
#     update the DB
foreach ($lines as $line) {
    $line_ar = explode("/\s+/",$line);
    mysql_query("UPDATE t SET name='$line_ar[1]' WHERE id='$line_ar[0]'");
}

# close the database connection

Potential gotchas for the above code:

• really big files : you'd need to read and handle the file one line at a time
• messy data : you might have to refine or replace the explode statement
• unsafe data : parametrize the query and add checking to avoid SQL injection attacks
• slow : you're making a lot of db calls; consider the approach first described instead
• fragile : you could add checking to see whether the update succeeded

See http://php.net/manual/function.explode.php and the mysql-query docs there as well for more info

---

If you're sure it's always 5 characters, you could simply use:

<?php
$ID = substr($varToEachLine, 0, 5);
?>

---

Your could use explode() to split your string into lines ; and, then, for each line, a simple Regular expression, with the preg_match() function, to match the parts that interest you.

For example, something like this :

$str = <<<STR
22222   hihihi
33333   hihihi
kjhkh   hihihi
STR;

foreach (explode(PHP_EOL, $str) as $line) {
  if (preg_match('/^([^\s]+)\s+(.*)$/', $line, $m)) {
    var_dump($m[1], $m[2]);
  }
}

Would get you :

string '22222' (length=5)
string 'hihihi' (length=6)
string '33333' (length=5)
string 'hihihi' (length=6)
string 'kjhkh' (length=5)
string 'hihihi' (length=6)

Notes about the regex I used :

• starts at beginning of string : ^
• matches anything that is not a white character (tabulation, newline, space) : [^\s]
  • one or more times : [^\s]+
  • capturing it : ([^\s]+)
• matches anything that is a white character : \s
  • one or more times : \s+
• matches anything any number of times : .*
  • capturing it : (.*)
• and, finally, end of string : $

Now, as you are working with a file as input, and not a string, you might want to read the file line by line, not fetching the whole file into memory ; for that, see the fgets() function -- there is an example on its manual page.

---

This seems to be a delimited text file. You can use a text driver to load the data into a record-set very easily. I am not into PHP, but I believe text-drivers are there for PHP. If your app runs on Windows, Microsoft Jet supports text driver that can be used to get the data into a record-set.