分类 WEB开发 下的文章

php读取Mysql指定数据库并备份sql文件到百度网盘

  想要你的网站数据安全吗?即使被黑也能立即恢复热数据,请使用百度PCS个人云存储吧,免费的喔!以下代码提供了这个功能,自动的喔!

<?php
/**
 * php读取Mysql指定数据库并备份sql文件到百度网盘
 * @category Backup2BaiduAction
 * @copyright (c) 2013, niu lingyun
 * @author niu lingyun
 */

class Backup2BaiduAction extends BaseAction
{
    protected $access_token = '3.fd8a0f1927af6fd8db252f10c4bac854.2592000.1370083241.3291984864-你的token-key';
    protected $appName = 'game_backup';
    protected $root_dir;
    protected $local_dir;
    protected $pcs;

    /**
     * 导入百度PCS API并实例化
     * 指定云目录和本地目录
     */
    public function __construct()
    {
        vendor('Baidu.BaiduPCS');
        pcs = new BaiduPCS($this->access_token);
        $this->root_dir = '/apps/'.$this->appName.'/';
        $this->local_dir = APP_PATH.'Backup/';
    }

    /**
     * 上传备份文件到百度网盘
     */
    public function index()
    {
        $file = $this->doBackup();
        $fileName = basename($file);
        $newFileName = '';
        if(!$file){
            echo '文件不存在,请检查路径是否正确';
        }else{
            $fileSize = filesize($file);
            $handle = fopen($file, 'rb');
            $fileContent = fread($handle, $fileSize);
            $result = $this->pcs->upload($fileContent, $this->root_dir, $fileName, $newFileName);
            fclose($handle);
            $result = json_decode($result);
            dump($result);
        }

    }

    /**
     * 执行备份并生成sql文件
     * @return string
     */
    public function doBackup()
    {
        $db = new Model();
        $DB_NAME = C('DB_NAME');
        $tableListsql = "SHOW TABLES FROM `{$DB_NAME}`";
        $list = $db->query($tableListsql);
        $tableList = array();
        foreach ($list as $v)
        {
            $tableList[] = $v["Tables_in_{$DB_NAME}"];
        }

        foreach ($tableList as $table)
        {
            $db = M(str_replace(C('DB_PREFIX'), '', $table));
            $array = $db->select();
            $sql .= "TRUNCATE TABLE `$table`;\n";
            foreach ($array as $value)
            {
                $sql .= $this->insertsql($table , $value);
            }
        }
        $file = $this->local_dir.$DB_NAME.'('.date('Y-m-d H_i').').sql';
        if(!file_put_contents($file, $sql)){
            exit('文件保存失败,请检查!< br />');
        }else{
            return $file;
        }

    }

    /**
     * 产生sql备份语句
     * @param type $table
     * @param type $row
     * @return string
     */
    public function insertsql($table, $row)
    {
        $sql = "INSERT INTO `{$table}` VALUES (";
        $values = array();
        foreach ($row as $value)
        {
            $values[] = "'".($value)."'";
        }
        $sql .= implode(', ', $values) . ");\n";
        return $sql;
    }
}

php读取Excel xlsx 2007+并导入MySQL

  有时候需要把Excel中的表格数据导入到Mysql,这段代码实现的是批量读取一个文件夹中所有的xlsx文件,并按xlsx文件的文件名创建同名的Table表,建立相应的字段,并把数据导入到表中。

<?php
/**
 * php读取Excel xlsx 2007+并导入MySQL
 * @category Excel2DbAction
 * @copyright (c) 2013, niu lingyun
 * @author niu lingyun  */
class Excel2DbAction extends BaseAction
{
    protected $objReader;
    protected $PHPExcel;
    protected $Excel;
    protected $Doc;
    protected static $total = 0;

    /**
     * 导入PHPExcel类库,并初始化
     * @access public
     */
    public function __construct()
    {
        vendor('PHPExcel.PHPExcel.IOFactory');
        $this->objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $this->objReader->setReadDataOnly(TRUE);
    }

    /**
     * 扫描目录,扫描xlsx文件,逐一读取,写进MySQL
     * @access public
     */
    public function index()
    {
        $dir = APP_PATH.'Data';
        if(!is_dir($dir)){
            exit('目录不存在');
        }
        //扫描并遍历文件
        $fileList = scandir($dir);
        foreach ($fileList as $v){
            if(pathinfo($v, PATHINFO_EXTENSION) == 'xlsx'){
                self::$total ++;
                $temp[self::$total] = $dir.'/'.$v;
            }
        }

        $num = (!empty($_GET['num'])) ? ($_GET['num']) : 1;
        while ($num <= excel="" this-="">readFile($temp[$num]);

            //导表操作
            $this->createTable($this->Doc['tableName'],  $this->Doc['fields']);
            $this->createData($this->Doc['tableName'], $this->Doc['fields'], $this->Doc['content']);

            $num++;
            //判断是否跳转到下一个文件
            ($num > self::$total) ? exit('Excel to MySQL:ok') : $this->redirect('Excel2Db/index',array('num'=>$num),3);
        }
    }

    /**
     * 执行解析xlsx文件
     * @access public
     * @param string $file 文件
     * @return mix 返回解析结果
     */
    public function readFile($file)
    {
        $this->PHPExcel = $this->objReader->load($file);
        $this->PHPExcel->setActiveSheetIndex(0);
        $this->Excel = $this->PHPExcel->getActiveSheet();
        $tmpContent = $this->getContent();
        for($i=2,$j=0;$i<=count($tmpcontent);$i++,$j++) filename="pathinfo($file," doc="array(">         $filename,
            'rows'                =>         $this->getRows(),
            'colums'              =>         $this->getColumIndex(),
            'fields'              =>         $tmpContent['1'],
            'content'             =>         $content,
         );
         $this->Doc = $doc;
    }

    /**
     * 取得Excel的行数
     * @access public
     * @return string
     */
    public function getRows()
    {
        $rows = $this->Excel->getHighestRow();
        return $rows;
    }

    /**
     * 取得Excel的列数,字母模式
     * @access public
     * @return string
     */
    public function getColums()
    {
        $colums = $this->Excel->getHighestColumn();
        return $colums;
    }

    /**
     * 取得Excel的列数,数字模式
     * @access public
     * @return int
     */
    public function getColumIndex()
    {
        $columIndex = PHPExcel_Cell::columnIndexFromString($this->getColums());
        return $columIndex;
    }

    /**
     * 取得Excel的详细内容
     * @access public
     * @return array
     */
    public function getContent()
    {
        $content = array();
        for ($row = 1; $row <= this-="">getRows(); $row++)
        {
            for ($col = 0; $col < $this->getColumIndex(); $col++)
            {
                $content[$row][$col] = $this->Excel->getCellByColumnAndRow($col, $row)->getCalculatedValue();
            }
        }
        return $content;
    }

    /**
     * 创建数据表,传入表名和字段信息自动创建一个表
     * @param string $tableName
     * @param type $fields
     * @return boolean
     */
    private function createTable($tableName,$fields)
    {
        $prefix = C('DB_PREFIX');
        $tableName = $prefix.$tableName;
        $sql = "CREATE TABLE IF NOT EXISTS `{$tableName}`(\n\r`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,";
        foreach ($fields as $v)
        {
            $sql .= "\n\r{$v},";
        }
        $sql .= "\r\nPRIMARY KEY (`id`)";
        $sql .= "\n\r) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
        $db = new Model();
        if($db->execute($sql)){
            echo "数据表{$tableName}创建成功
";
            return TRUE;
        }else{
            echo "数据表{$tableName}已经创建
";
            $truncate = "TRUNCATE TABLE `{$tableName}`";
            $db->execute($truncate);
            echo "数据表{$tableName}已经清空
";
        }
    }

    /**
     * 导入数据到对应的表中
     * @param type $tableName
     * @param type $fields
     * @param type $content
     * @return boolean
     */
    private function createData($tableName,$fields,$content)
    {
        $table = M($tableName);
        $reg = '/`.*`/';
        foreach ($fields as $k){
            preg_match($reg, $k, $matches);
            $field[] = str_replace('`', '', $matches[0]);           
        }
        foreach ($content as $v){
            $data[] = array_combine($field, $v);
        }
        if($table->addAll($data)){
            echo "数据导入{$tableName}成功
";
            return TRUE;
        }else{
            echo "数据导入{$tableName}失败",$table->getDbError().'
';
        }
    }

    /*
     * 析构方法,清空计数器
     */
    public function __destruct() {
        self::$total = NULL;
    }

}

使用百度、有道的翻译API

  开发网站的时候,使用翻译API的时候很少,但是有时候的确需要用到,在此分享出百度翻译API的用法实例。

<?php

$key = "where there is life there is hope";

$key = urlencode($key);

$url = "http://openapi.baidu.com/public/2.0/bmt/translate?client_id=9annvzOmEwcgwejCuu84xxxx&q={$key}&from=auto&to=auto";//需要你的id


$result = file_get_contents($url);

$result = json_decode($result,true);

var_dump($result);

  在此再分享出有道翻译API的用法,有道翻译的结果更丰富一些。请用你申请的key替换此代码中得key。

public $youdao_url = "http://fanyi.youdao.com/openapi.do?keyfrom=leandrx&key=5065227x&type=data&doctype=json&version=1.1&q=";

public $data = array();

public function _run()
{
    if(isset($_POST) && isset($_POST['content'])){
        $this->query();
        $this->assign('data', $this->data);
    }
    $this->display('index');
}

private function query()
{
    $content = trim($_POST['content']);
    if(isset($content) && !empty($content)){
        $list = explode("\r\n", $content);
        $data = array();
        foreach ($list as $key => $value) {
        $result = file_get_contents($this->youdao_url.trim($value));
        $result = json_decode($result,true);
        if ($result['errorCode'] != 0) {
            continue;
        }
        $explains = '';
        if (@is_array($result['basic']['explains'])) {
            foreach ($result['basic']['explains'] as $k => $v) {
            $explains .= '  '.$v.'  ';
            }
        }
        @$tic = $result['basic']['us-phonetic'] .'    '.$result['basic']['uk-phonetic'];
        $this->data[$key]['query'] = $result['query'];
        $this->data[$key]['translation'] = $result['translation'][0];
        $this->data[$key]['tic'] = $tic;
        $this->data[$key]['explains'] = $explains;
        }
    }
}

PHP快速排序的两种算法

  虽然在PHP这样的web应用开发中,我们不是太强调排序的重要性,因为PHP自身已经带了例如sort()等这样强大的排序函数,但是在一些重要的场合,例如某些高并发的场合,我想排序算法的影响已经不能忽略。所以在此介绍递归排序和迭代排序。

递归法:  

/**
* 递归法实现的快速排序
*/
function quicksort($seq)
{
        $k = $seq[0];
        $x = array();
        $y = array();
        for($i=1; $i< $_size; $i++) {
            if($seq[$i] <= $k) {
                $x[] = $seq[$i];
            } else {
                $y[] = $seq[$i];
            }
        }
        $x = quicksort($x);
        $y = quicksort($y);
        return array_merge($x, array($k), $y);
    } else {
        return $seq;
    }
}

迭代法:

/**
* 迭代法的快速排序
*/
function quicksortx(&$seq)
{
    $stack = array($seq);
    $sort = array();
    while ($stack) {
        $arr = array_pop($stack);
        if(count($arr) <= 1) {
            if(count($arr) == 1) {
                $sort[] = &$arr[0];
            }
            continue;
        }
        $k = $arr[0];
        $x = array();
        $y = array();
        $_size = count($arr);
        for($i =1 ;$i < $_size; $i++) {
            if($arr[$i] <= $k) {
                $x[] = &$arr[$i];
            } else {
                $y[] = &$arr[$i];
            }
        }
        !empty($y) && array_push($stack, $y);
        array_push($stack, array($arr[0]));
        !empty($x) && array_push($stack, $x);
    }
    return $sort;
}

使用:

/**
*产生一个随机数组
*/
for($i=0;$i<5;$i++){
    $testArr[]=mt_rand(0,100);
}
var_dump($testArr);
var_dump(quicksort($testArr));

var_dump(quicksortx($testArr));

php解析xml函数(两种方法)

  XML,可扩展标记语言 (Extensible Markup Language, XML),如今社会,XML已经不再那么的流行,在一些场合以逐渐被结构更好的json取代,但是我们不能忘记它。在此贡献两种PHP解析XML的方法。 

方法一:

function getXmlData($strXml)
{
    $pos = strpos($strXml, 'xml');//确认是否是xml文件
    if ($pos) {
        $xmlCode = simplexml_load_string($strXml);//把xml字符串解释成对象
        $arrayCode = get_object_vars_final($xmlCode);//返回由对象属性组成的关联数组
        return $arrayCode;
    }else{
        return '';
    }
}

function get_object_vars_final($obj)
{
    if (is_object($obj)) {
        $obj = get_object_vars($obj);
    }
    if (is_array($obj)) {
        foreach ($obj as $key => $value) {
            $obj[$key] = get_object_vars_final($value);
        }
    }
    return $obj;
}

方法二:

var_dump(json_decode(json_encode(simplexml_load_string($string)),true));

用法:

$string = <<<XML
    Forty What?JoeJaneI know that's the answer -- but what's    the question?XML;

var_dump(getXmlData($string));
var_dump(json_decode(json_encode(simplexml_load_string($string)),true));

方法二利用了json函数,是比较hack的做法,实际开发中可以采用。