Use PHP to parse administrative division code

I have already written this PHP script many times, but I have rewritten it because of some changes in the data format provided by the National Bureau of Statistics. The problem involves both PHP and, at the same time, MySQL is also involved, and it is very suitable for interview questions. Such questions often reflect the basic quality of job seekers.

Preparation: Download the Administrative Code of the latest counties and counties, and save it as a data.txt file.

The latest administrative code for counties and above

Create a MySQL table first:
Note that the character set of the table must be consistent with that of the file.

Create table if not exists 'region '(
'Id' int (10) unsigned not null,
'Parent _ id' int (10) unsigned not null,
'Name' varchar (20) not null,
Primary key ('id '),
KEY 'parent _ id' ('parent _ id ')
) ENGINE = InnoDB;
Supplement: better storage of Hierarchical Data: Storing Hierarchical Data in a Database Article.

Then write the PHP script:
Verify that the content of the data.txt file is valid, and the Code itself is not strictly verified.

<? Php

// Config
$ Host = '';
$ Dbname = '';
$ Charset = '';
$ Username = '';
$ Password = '';

Set_time_limit (0 );

$ Dsn = "mysql: host = {$ host}; dbname = {$ 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;

$…… = $ Dbh-> prepare ('
Insert into region (id, parent_id, name)
VALUES (: id,: parent_id,: name)
');

$ Something-> bindValue (': id', $ id, PDO: PARAM_INT );
$ Something-> bindValue (': parent_id', $ parent_id, PDO: PARAM_INT );
$ Something-> bindValue (': name', $ name );

$ Something-> execute ();
}

Fclose ($ handle );

?>
Note: You can run the task by entering the configuration options as needed.

......

With the official administrative division code, coupled with a private pure IP database, it is more perfect.

Author: Lao Wang