大学本科专科数据信息-包括省份-高校-院系sql

<?php define('DATABASE', 'test'); define('TABNAME', 'department'); define('MYSQL_HOST', 'localhost'); define('MYSQL_USER', 'root'); define('MYSQL_PASS', 'root'); function x_readfile($filename) { $handle = fopen($filename, 'r'); $contents = stream_get_contents($handle); return $contents; } function create_insert_sql($sid, $did, $name) { $sql = 'insert into ' . TABNAME . '(sid, did, name) values (' . $sid . ',' . $did . ',"' . $name . '");'; //echo $sql . '<br/>'; return $sql; } // 获取所有学校id $link = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db(DATABASE, $link); if (!$db_selected) { die ('Can\'t use : ' . mysql_error()); } mysql_query("set names utf8;"); $sql_statement = 'select sid from school where cid != 0'; $result = mysql_query($sql_statement, $link); $sids = array(); while($row = mysql_fetch_array($result)){ $sids[] = $row['sid']; } // 初始化 PDO 存储过程 try { $dsn = 'mysql:dbname=' . DATABASE . ';host=127.0.0.1'; $dbh = new PDO($dsn, MYSQL_USER, MYSQL_PASS, array(PDO::ATTR_PERSISTENT => true)); } catch (Exception $e) { die("Unable to connect: " . $e->getMessage()); } // 根据学校 id 去发起请求，一共3163个学校，可见请求之多 $baseurl = 'http://www.renren.com/GetDep.do?id='; for($i = 0; $i < count($sids); $i++){ $content = x_readfile($baseurl . $sids[$i]); $pat = '#<option[^>].*?>(.*?)</option>#i'; $matches = array(); preg_match_all($pat, $content, $matches); // 抛弃第一条结果：<option value=''>院系</option> array_shift($matches[1]); try { $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->beginTransaction(); $dbh->exec('set names utf8;'); foreach($matches[1] as $k => $v){ $dbh->exec(create_insert_sql($sids[$i], $k + 1, mb_convert_encoding($v, 'UTF-8', 'HTML-ENTITIES'))); } $dbh->commit(); } catch (Exception $e) { $dbh->rollBack(); echo "Failed: " . $e->getMessage(); } } ?>