2013年7月

CSS属性中Display与Visibility的不同

  visibility属性用来确定元素是显示还是隐藏,这用visibility="visible|hidden"来表示,visible表示显示,hidden表示隐藏。当visibility被设置为"hidden"的时候,元素虽然被隐藏了,但它仍然占据它原来所在的位置。
  display被设置:none,这时元素实际上就从页面中被移走,它下面所在的元素就会被自动跟上填充。

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;
    }

}