Say this PHP script I have written many times, but because the National Bureau of Statistics provides a few changes in the format of the data, so I rewrote again, the problem is not only involved in PHP, but also related to MySQL, feel very suitable for the interview problem, such problems often reflect the basic quality of job seekers.
Preparation: Need to download the latest county and county Administrative Code, and save as Data.txt file.
Latest County and county Administrative Code
First create the MySQL table:
It is important to note that the character set of the table and the file's character set need to be consistent.
CREATE TABLE IF not EXISTS ' region ' (
' id ' int (ten) unsigned not NULL,
' parent_id ' int (ten) unsigned not NULL,
' Name ' varchar (not NULL),
PRIMARY KEY (' id '),
KEY ' parent_id ' (' parent_id ')
) Engine=innodb;
Add: Better storage of hierarchical data: storing hierarchical in a Database article.
Then write the PHP script:
Note that you are guaranteed the legality of the contents of the Data.txt file, and that the code itself is not strictly verifiable.
Config
$host = ";
$dbname = ";
$charset = ";
$username = ";
$password = ";
Set_time_limit (0);
$DSN = "mysql:host={$host};d bname={$dbname};charset={$charset}";
$options = Array (
Pdo::attr_errmode = Pdo::errmode_exception,
);
$DBH = new PDO ($DSN, $username, $password, $options);
$handle = fopen (' data.txt ', ' R ');
$parent = Array ();
while (!feof ($handle)) {
$row = Trim (fgets ($handle));
if (!preg_match ('/(\d+) (\s+) (. +)/', $row, $matches)) {
Continue
}
List ($row, $id, $delimiter, $name) = $matches;
if (!isset ($separator)) {
$separator = $delimiter;
}
$level = Substr_count ($delimiter, $separator);
$parent _id = $level > 1? $parent [$level-1]: 0;
$parent [$level] = $id;
$sth = $dbh->prepare ('
INSERT into region (ID, parent_id, name)
VALUES (: ID,:p arent_id,: Name)
');
$sth->bindvalue (': Id ', $id, PDO::P aram_int);
$sth->bindvalue (':p arent_id ', $parent _id, PDO::P aram_int);
$sth->bindvalue (': Name ', $name);
$sth->execute ();
}
Fclose ($handle);
?>
Description: Fill in the configuration options according to your own situation to run.
......
With the official administrative division code, and then with the civilian pure IP database, it is more perfect.
Author: Lao Wang
http://www.bkjia.com/PHPjc/478052.html www.bkjia.com true http://www.bkjia.com/PHPjc/478052.html techarticle say this php script I have written many times, but because the National Bureau of Statistics to provide a few changes in the format of the data, so I rewrote again, the problem involves both PHP, but also ...