实用PHP数据库PDO操作类

DbClass.php

<?php
class DB_API
{
    // 数据库表名
    protected $table;
 
    // 数据库主键
    protected $primary = 'id';
 
    // 表前缀
    protected $prefix = '';
 
    // WHERE和ORDER拼装后的条件
    private $filter = array();
 
    // PDO
    private $pdo;
    
    // PDOStatement
    private $Statement;
    
    // PDO链接数据库
    public function __construct($config){
        class_exists('PDO') or exit("not found PDO");
        
        try{
            $this->pdo = new PDO("mysql:host=".$config['db_host'].";port=".$config['db_port'].";dbname=".$config['db_name'],$config['db_user'], $config['db_pass']); 
        }catch(PDOException $e){
            // 数据库无法链接,如果您是第一次使用,请先配置数据库!
            exit($e->getMessage());
        }
        $this->prefix = $config['db_prefix'];
        $this->pdo->exec("SET NAMES UTF8");
        
        
    }
    // 配置表信息
    public function set_table($table=null,$primary='id'){
        if($table==null){ exit('Not found Table');}
        
        $this->primary = $primary;
        $this->table = $this->prefix.$table;
        return $this;
    }
    
    
    // 获取数据
    public function getData($sql)
    {
        if(!$result = $this->query($sql))return array();
        if(!$this->Statement->rowCount())return array();
        $rows = array();
        while($rows[] = $this->Statement->fetch(PDO::FETCH_ASSOC)){}
        $this->Statement=null;
        array_pop($rows);
        return $rows;
    }
    
    // 查询数据条数
    public function getCount($conditions){
        $where = '';
        if(is_array($conditions)){
            $join = array();
            foreach( $conditions as $key => $value ){
                $value =  '\''.$value.'\'';
                $join[] = "{$key} = {$value}";
            }
            $where = "WHERE ".join(" AND ",$join);
        }else{
            if(null != $conditions)$where = "WHERE ".$conditions;
        }
        $sql = "SELECT count(*) as Frcount FROM {$this->table} {$where}";
        $result = $this->getData($sql);
        return $result[0]['Frcount'];
        
    }
    // 获取单一字段内容
    public function getField($where=null,$fields=null){
        if( $record = $this->findAll($where, null, $fields, 1) ){
            $res = array_pop($record);
            return $res[$fields];
        }else{
            return FALSE;
        }
    }
    // 递增数据
    public function goInc($conditions,$field,$vp=1){
        $where = "";
        if(is_array($conditions)){
            $join = array();
            foreach( $conditions as $key => $value ){
                $value = '\''.$value.'\'';
                $join[] = "{$key} = {$value}";
            }
            $where = "WHERE ".join(" AND ",$join);
        }else{
            if(null != $conditions)$where = "WHERE ".$conditions;
        }
        $values = "{$field} = {$field} + {$vp}";
        $sql = "UPDATE {$this->table} SET {$values} {$where}";
        
        return $this->pdo->exec($sql);
        
    }
    
    // 递减
    public function goDec($conditions,$field,$vp=1){
        return $this->goInc($conditions,$field,-$vp);
    }
    
    // 修改数据
    public function update($conditions,$row)
    {
        $where = "";
        $row = $this->__prepera_format($row);
        if(empty($row)){
        return FALSE;
        }
        if(is_array($conditions)){
            $join = array();
            foreach( $conditions as $key => $condition ){
                $condition = '\''.$condition.'\'';
                $join[] = "{$key} = {$condition}";
            }
            $where = "WHERE ".join(" AND ",$join);
        }else{
            if(null != $conditions){
              $where = "WHERE ".$conditions;
            }
        }
        foreach($row as $key => $value){
            $value = '\''.$value.'\'';
            $vals[] = "{$key} = {$value}";
        }
        $values = join(", ",$vals);
        $sql = "UPDATE {$this->table} SET {$values} {$where}";
        //echo $sql.'<br/>';
        $res = $this->pdo->exec($sql);
      if($res){
          return $res;
      }else{
          var_dump($this->pdo->errorInfo());
      }
        
        
    }
 
    // 查询所有
    public function findAll($conditions=null,$order=null,$fields=null,$limit=null)
    {
        $where = '';
        if(is_array($conditions)){
            $join = array();
            foreach( $conditions as $key => $value ){
                $value =  '\''.$value.'\'';
                $join[] = "{$key} = {$value}";
            }
            $where = "WHERE ".join(" AND ",$join);
        }else{
            if(null != $conditions)$where = "WHERE ".$conditions;
        }
      if(is_array($order)){
               $where .= ' ORDER BY ';
            $where .= implode(',', $order);
      }else{
         if($order!=null)$where .= " ORDER BY  ".$order;
      }
        
        if(!empty($limit))$where .= " LIMIT {$limit}";
        $fields = empty($fields) ? "*" : $fields;
 
        $sql = "SELECT {$fields} FROM {$this->table} {$where}";
        
        return $this->getData($sql);
 
    }
 
    // 查询一条
    public function find($where=null,$order=null,$fields=null,$limit=1)
    {
       if( $record = $this->findAll($where, $order, $fields, 1) ){
            return array_pop($record);
        }else{
            return FALSE;
        }
    }
    
    // 执行SQL语句并检查是否错误
    public function query($sql){
        $this->filter[] = $sql;
        $this->Statement = $this->pdo->query($sql);
        if ($this->Statement) {
            return $this;
        }else{
            $msg = $this->pdo->errorInfo();
            if($msg[2]) exit('数据库错误:' . $msg[2] . end($this->filter));
        }
    }
 
    // 执行SQL语句函数
    public function findSql($sql)
    {
        return $this->getData($sql);
    }
    
    // 根据条件 (conditions) 删除
    public function delete($conditions)
    {
       $where = "";
        if(is_array($conditions)){
            $join = array();
            foreach( $conditions as $key => $condition ){
                $condition = '\''.$condition.'\'';
                $join[] = "{$key} = {$condition}";
            }
            $where = "WHERE ( ".join(" AND ",$join). ")";
        }else{
            if(null != $conditions)$where = "WHERE ( ".$conditions. ")";
        }
        $sql = "DELETE FROM {$this->table} {$where}";
        return $this->pdo->exec($sql);
    }
 
    // 新增数据
    public function add($row)
    {
       if(!is_array($row)){
         return FALSE;
       }
        $row = $this->__prepera_format($row);
        if(empty($row)){
         return FALSE;
        }
        foreach($row as $key => $value){
            $cols[] = $key;
            $vals[] = '\''.$value.'\'';
        }
        $col = join(',', $cols);
        $val = join(',', $vals);
 
        $sql = "INSERT INTO {$this->table} ({$col}) VALUES ({$val})";
        if( FALSE != $this->pdo->exec($sql) ){
            if( $newinserid = $this->pdo->lastInsertId() ){
                return $newinserid;
            }else{
                $a=$this->find($row, "{$this->primary} DESC",$this->primary);
                return array_pop($a);
            }
        }
        return FALSE;
    }
 
    private function __prepera_format($rows)
    {
        $stmt = $this->pdo->prepare('DESC '.$this->table);  
        $stmt->execute();  
        $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $newcol = array();
        foreach( $columns as $col ){
            $newcol[$col] = null;
        }
        return array_intersect_key($rows,$newcol);
    }
}

如何使用

<?php
header("Content-type:text/html;charset=utf-8");

// 引入类
include './db.class.php';

// 数据库配置
$config  = [
    'db_host' => 'localhost', // 数据库地址
    'db_port' => 3306, // 默认mysql数据库端口
    'db_name' => 'test', // 数据库名字
    'db_user' => 'root', // 数据库用户名
    'db_pass' => 'root', // 数据库密码
    'db_prefix' => '', // 表前缀
];
 
// 实例化类
$db = new DB_API($config);
 
// 表名
$article = $db->set_table('article');
 
// 新增数据
$newdata = ['title'=>'this is a title'];
$r = $article->add($newdata);
if($r){
    echo '新增成功!';
}else{
    echo '操作失败!';
}

// 查询数据
$where = ['id'=>3];
$find = $article->find($where); //查询一条数据
$find = $article->findAll($where); // 查询多条数据
print_r($find);
 
// 更新数据
$where = ['title'=>'hello world666'];
$update = $article->update(['id'=>1],$where);
if($update ){
    echo '更新成功!';
    // 查询并打印
    $newdata = $article->find('id=1');
    print_r($newdata);
}else{
    echo '更新失败!';
}
 
// 删除数据
$where = ['id'=>1];
$del =  $article->delete($where);
if($del){
    echo '删除成功!';
}else{
    echo '删除失败!';
}
 
// 获取符合条件的记录数
$where = ['author'=>'TANKING'];
$count = $article->getCount($where);
echo $count;
 
// 执行原生SQL语句
$sql = 'select * from article where id=3';
$lists = $article->findSql($sql);
print_r($lists);
 
// 根据条件查询出对应的字段的值
$where = ['id'=>1];
$res = $article->getField($where,'title');
if ($res) {
    echo $res;
}else{
    echo "没有数据";
}

// 高级查询
// $conditions查询条件
// $order排序方法
// $fields指定字段
// $limit查询条数
$res = $article->findAll($conditions=null,$order='id asc',$fields=null,$limit=null);
if ($res) {
    print_r($res);
}else{
    print_r("没有数据");
}

Linux VPS/服务器 网站及数据库自动本地备份并FTP上传备份脚本

准备工作:

需要提前在VPS安装好lftp,lftp功能上比较强大,CentOS直接执行:yum install lftp,Debian执行:apt-get install lftp

需要在VPS上创建/home/backup/ 目录,在FTP上创建backup目录。

如果VPS上数据库不多的话使用Godaddy的免费空间就可以(10GB空间,300GB流量),只要注册个域名就免费送。

下面将备份脚本进行部分注释:
#!/bin/bash
#Funciont: Backup website and mysql database
#Author: licess
#Website: http://lnmp.org
#IMPORTANT!!!Please Setting the following Values!

######~Set Directory you want to backup~######将下面的目录修改成自己要备份的目录,一般按我的都是在/home/wwwroot/下面所有直接写了需要备份的目录。可以继续再加:Backup_Dir5=你的目录 ,Backup_Dir后面的数字依次递增。如果不足4个,直接删除不需要的就可以,同时修改下面tar zcf 部分。

Backup_Dir1=vpser.net
Backup_Dir2=lnmp.org
Backup_Dir3=licess.org
Backup_Dir4=jungehost.com

######~Set MySQL UserName and password~######设置MySQL的用户名和密码,最好是root,其他用户可能因为权限问题无法导出部分数据库。
MYSQL_UserName=root
MYSQL_PassWord=yourmysqlrootpassword

######~Set MySQL Database you want to backup~######设置要部分的数据库,可以继续再加:Backup_Database_Name5=数据库名,Backup_Database_Name后面的数字依次递增。
Backup_Database_Name1=vpser
Backup_Database_Name2=licess
Backup_Database_Name3=junge
Backup_Database_Name4=vpserorg

######~Set FTP Information~######设置用来存放备份数据的FTP信息
FTP_HostName=184.168.192.43 //FTP服务器的IP或者域名
FTP_UserName=vpsernet //FTP服务器用户名
FTP_PassWord=yourftppassword //FTP服务器用户对应的密码
FTP_BackupDir=backup //备份到FTP上的目录,需要提前创建好。

#Values Setting END!

TodayWWWBackup=www-*-$(date +”%Y%m%d”).tar.gz
TodayDBBackup=db-*-$(date +”%Y%m%d”).sql
OldWWWBackup=www-*-$(date -d -3day +”%Y%m%d”).tar.gz
OldDBBackup=db-*-$(date -d -3day +”%Y%m%d”).sql

tar zcf /home/backup/www-$Backup_Dir1-$(date +”%Y%m%d”).tar.gz -C /home/wwwroot/ $Backup_Dir1 –exclude=soft
tar zcf /home/backup/www-$Backup_Dir2-$(date +”%Y%m%d”).tar.gz -C /home/wwwroot/ $Backup_Dir2
tar zcf /home/backup/www-$Backup_Dir3-$(date +”%Y%m%d”).tar.gz -C /home/wwwroot/ $Backup_Dir3 –exclude=test
tar zcf /home/backup/www-$Backup_Dir4-$(date +”%Y%m%d”).tar.gz -C /home/wwwroot/ $Backup_Dir4

###上面为备份网站文件数据,因为我的网站比较零散,而且网站目录下面有些目录属于临时目录并不需要备份,所以可以在上面加上–exclude=不备份的目录。如果在前面加了Backup_Dir5=yourdir,则再加tar zcf /home/backup/www-$Backup_Dir5-$(date +”%Y%m%d”).tar.gz -C
/home/wwwroot/ $Backup_Dir5 。如果多余则删除多余行。

/usr/local/mysql/bin/mysqldump -u$MYSQL_UserName -p$MYSQL_PassWord $Backup_Database_Name1 > /home/backup/db-$Backup_Database_Name1-$(date +”%Y%m%d”).sql
/usr/local/mysql/bin/mysqldump -u$MYSQL_UserName -p$MYSQL_PassWord $Backup_Database_Name2 > /home/backup/db-$Backup_Database_Name2-$(date +”%Y%m%d”).sql
/usr/local/mysql/bin/mysqldump -u$MYSQL_UserName -p$MYSQL_PassWord $Backup_Database_Name3 > /home/backup/db-$Backup_Database_Name3-$(date +”%Y%m%d”).sql
/usr/local/mysql/bin/mysqldump -u$MYSQL_UserName -p$MYSQL_PassWord $Backup_Database_Name4 > /home/backup/db-$Backup_Database_Name4-$(date +”%Y%m%d”).sql

###上面为备份MySQL数据库,如果在前面加了Backup_Database_Name5=yourdatabasename,则再加/usr/local/mysql/bin/mysqldump -u$MYSQL_UserName -p$MYSQL_PassWord $Backup_Database_Name5 > /home/backup/db-$Backup_Database_Name5-$(date +”%Y%m%d”).sql 。如果多余则删除多余行。

rm $OldWWWBackup
rm $OldDBBackup
###删除3天前的备份###

cd /home/backup/

###下面为自动上传部分,不得不说lftp很强大,抛弃ftp吧####
lftp $FTP_HostName -u $FTP_UserName,$FTP_PassWord << EOF
cd $FTP_BackupDir
mrm $OldWWWBackup
mrm $OldDBBackup
mput $TodayWWWBackup
mput $TodayDBBackup
bye
EOF

脚本下载地址:http://soft.vpser.net/lnmp/backup.sh

将脚本放到/root/ 下面,按上面的注释修改脚本中的参数,并保存,如果不熟悉vi或者nano编辑器,可以用winscp,执行:chmod +x /root/backup.sh 为脚本添加执行权限,执行:crontab -e 添加定时执行

在crontab中加入:0 3 * * * /root/backup.sh

凌晨3点自动执行/root/bakcup.sh 脚本,备份vps上的数据并上传到FTP上。

原文:Linux VPS/服务器 网站及数据库自动本地备份并FTP上传备份脚本