一个PHP脚本，通过curl先获取百度地图api生成的经纬度，然后改数据库内的数据。

 

重要：在这个脚本里有一个order by的排序。 加了order by

SELECT kgId,provinceId,kgName FROM kindgartenbak WHERE deleteFlag = 1 AND STATUS = 2 AND runflag = 0 ORDER BY kgId DESC LIMIT 100;执行时间：0.678sec

 

 

SELECT kgId,provinceId,kgName FROM kindgartenbak WHERE deleteFlag = 1 AND STATUS = 2 AND runflag = 0 LIMIT 100;执行时间：0.002sec

 

在优化以后  之前需要将近两个小时的执行时间，立马节省了一个小时！！！！！！   

就一个 order by  !!!!!!!!    作用真大！

 

今天写一个PHP脚本，目的是让先从数据库拿取响应的地区名  然后通过幼儿园的名字来查询准确的经纬度。此间每次生成的经纬度进入数据库内的更改。

7万多条数据用时一个小时执行完毕。 不得不说 用curl结果还是比file_getcoents快的多。话不多说直接上代码

<?php
date_default_timezone_set('Asia/Chongqing');
header('content-type:text/html; charset=utf-8');
ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(0);

/**
 * @author  gf
 * 获取经纬度修改数据
 * Class Importkindgarten
 */
class Importkindgarten
{

    private $dbhost = '127.0.0.1';
    private $dbuser = 'root';
    private $dbpw = '123';
    private $dbname = 'mypolyguide';

    private $dbcharset = 'utf8';
    private $write_db = null;

    public function __construct()
    {
        $this->db_write_link();
    }

    /**
     * 连接数据库
     * @return null|resource
     */
    public function db_write_link()
    {
        $this->write_db = mysql_connect($this->dbhost, $this->dbuser, $this->dbpw);
        if (!$this->write_db) {
            die('Could not connect: ' . mysql_error());
        }
        mysql_query('SET character_set_connection=' . $this->dbcharset . ', character_set_results=' . $this->dbcharset . ', character_set_client=binary', $this->write_db);
        mysql_query('SET names utf8', $this->write_db);
        mysql_select_db($this->dbname, $this->write_db);

        return $this->write_db;
    }

    /**
     * 执行死循环进行查询修改
     */
    public function roundKgList()
    {
        $i = 0;
        while(true)
        {
            $res = $this->getKgList();
            if(empty($res['kgdata']))
            {
                break;
            }
            else
            {
                if(!empty($res['kgdata'])) foreach($res['kgdata'] as $k1=>$v1)
                {
                    $info = $this->getAreanameById($v1['provinceId']);
                    $area = $info[0];
                    $address=$v1['kgName'];
                    $returnArr = $this->curlGetWeb($area,$address);
                    $kgPosition = implode(',',$returnArr);
                    $updatesql = "UPDATE kindgartenbak SET lng='{$returnArr['lng']}', lat='{$returnArr['lat']}', kgPosition='{$kgPosition}',runflag='1' WHERE kgId={$v1['kgId']}";
                    mysql_query($updatesql, $this->write_db);
                    $i++;
                }
                else
                {
                    break;
                }
            }
        }
    }

    /**
     * 获取列表
     * @return mixed
     */
    public function getKgList()
    {
        $sql = 'SELECT kgId,provinceId,kgName FROM kindgartenbak WHERE deleteFlag = 1 AND status = 2 AND runflag = 0 ORDER BY kgId DESC LIMIT 100';
        $query = mysql_query($sql, $this->write_db);
        while($result = mysql_fetch_array($query,MYSQL_ASSOC))
        {
            $res['kgdata'][] = $result;
        }
        return $res;
    }

    /**
     * 拿取城市
     * @return mixed
     */
    public function getAreanameById($provinceId)
    {
        $sql = 'SELECT areaname FROM area WHERE id ='.$provinceId;
        $query = mysql_query($sql, $this->write_db);
        $info = mysql_fetch_row($query);
        if(!empty($info))
        {
            return $info;
        }
        else
        {
            return false;
        }
    }

    /**
     * 从百度地图api以curl获取经纬度
     * 数组形式返回
     */
    public function curlGetWeb($area,$address)
    {
        $Url="http://api.map.baidu.com/geocoder?address=".trim($area).trim($address)."&output=json&key=96980ac7cf166499cbbcc946687fb414";
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $Url);
        curl_setopt($ch, CURLOPT_HEADER, false);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); //如果把这行注释掉的话，就会直接输出
        $result=curl_exec($ch);
        curl_close($ch);
        $infolist=json_decode($result);
        $array=array();
        if(isset($infolist->result->location) && !empty($infolist->result->location)){
            $array=array(
                'lng'=>$infolist->result->location->lng,
                'lat'=>$infolist->result->location->lat,
            );
            return $array;
        }
        else
        {
            return false;
        }
    }
}

$start_date = date('Y-m-d H:i:s');
$start = microtime(true);
$db = new Importkindgarten();
$i = $db->roundKgList();
$end = microtime(true);
echo '重置经纬度：'.$start_date.'----用时--'.($end-$start).'----共更新('.$i.')条';
?>