澳门新葡萄京官网首页 2

PHP下CodeIgniter框架连接读取MS Access数据库文件

header("Content-type: text/html; charset=utf-8");
define('APP_DIR', dirname(apizl_file));

if (function_exists('spl_autoload_register')) {
  spl_autoload_register('autoClass');
} else {
  function __auto_load($className){
    autoClass($className);
  }
}

function autoClass($className){
  try{
    require_once APP_DIR.'/class/'.$className.'.php';
  } catch (Exception $e) {
    die('Error:' . $e->getMessage() . '
');
  }
}
$DB = new DB();
//插入
$inData['a'] = rand(1, 100);
$inData['b'] = rand(1, 1000);
$inData['c'] = rand(1,200) . '.' . rand(1,100);
$ret = $DB->insert('a', $inData);
echo '插入' . ($ret ? '成功' : '失败') . '
';
//更新
$upConData['a'] = 100;
$upConJudge['a'] = '';
list($upConStr, $mapUpConData) = $DB->FDField('b', 200, ' $upConStr,
  'data' => $upConData,
  'judge' => $upConJudge,
  'link' => 'and'
);
$upData['a'] = rand(1, 10);
$upData['b'] = 1;
$upData['c'] = 1.00;
$changeRows = $DB->update('a', $upData, $condition, $mapUpConData);
echo '更新行数:' . (int) $changeRows . '
';
//删除
$delVal = rand(1, 10);
list($delCon, $mapDelCon) = $DB->FDField('a', $delVal);
$delRet = $DB->delete('a', $delCon, $mapDelCon);
echo '删除a=' . $delVal . ($delRet ? '成功' : '失败') . '
';

//查询
$data['a'] = '10';
$judge['a'] = '>';
$data['b'] = '400';
$judge['b'] = 'FDFields($data, 'and', $judge);
$mData = $DB->fetch('select * from a where ' . $conSql . ' order by `a` desc', $mapConData);

var_dump($mData);

只要未有,请自行检索安装,win下比较容易,linux下就相比较麻烦了,lanVCD.1并进入国情况下得以看写的那篇小说。以下内容只适用用windows场景下,因为后来才晓得linux下独有pdo_odbc是非常的,还亟需access相关的驱动。

class DPDO{
  private $DSN;
  private $DBUser;
  private $DBPwd;
  private $longLink;
  private $pdo;
  //私有构造函数 防止被直接实例化
  private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {
    $this->DSN = $dsn;
    $this->DBUser = $DBUser;
    $this->DBPwd = $DBPwd;
    $this->longLink = $longLink;
    $this->connect();
  }
  //私有 空克隆函数 防止被克隆
  private function __clone(){}
  //静态 实例化函数 返回一个pdo对象
  static public function instance($dsn, $DBUser, $DBPwd, $longLink = false){
    static $singleton = array();//静态函数 用于存储实例化对象
    $singIndex = md5($dsn . $DBUser . $DBPwd . $longLink);
    if (empty($singleton[$singIndex])) {
      $singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink = false);
    }
    return $singleton[$singIndex]->pdo;
  }

  private function connect(){
    try{
      if($this->longLink){
        $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, array(PDO::ATTR_PERSISTENT => true));
      }else{
        $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd);
      }
      $this->pdo->query('SET NAMES UTF-8');
    } catch(PDOException $e) {
      die('Error:' . $e->getMessage() . '
');
    }
  }
}

cI用的是3.0本子,测量试验用的access为.mdb文件,php要读取Access数据库有二种驱动,意气风发种的odbc,生龙活虎种是pdo_odbc,三种都足以链接,不过常常会更推荐pdo_odbc,

用以拍卖字段映射,使用pdo的字段映射,能够有效制止sql注入

CI框架中加载另一中数据库的点子有三种,参照他事他说加以考查手册中的介绍,所以下边也说下三种办法:

澳门新葡萄京官网首页,增加和删除改查的切实完结DB.php

一时开采上边的差异,其他join、group by,order_by等艺术都与mysql大器晚成致。

public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
    if ($sql) {
      $sql .= ' limit 1';
      $pdoStatement = $this->pdo->prepare($sql, $preType);
      $pdoStatement->execute($searchData);
      return $data = $pdoStatement->fetch($dataMode);
    } else {
      return false;
    }
  }

  public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
    if ($sql) {
      $sql .= ' limit ' . (int) $limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
      $pdoStatement = $this->pdo->prepare($sql, $preType);
      $pdoStatement->execute($searchData);
      return $data = $pdoStatement->fetchAll($dataMode);
    } else {
      return false;
    }
  }

  public function insert($tableName, $data, $returnInsertId = false, $replace = false) {
    if(!empty($tableName) && count($data) > 0){
      $sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ';
      list($setSql, $mapData) = $this->FDFields($data);
      $sql .= $tableName . ' set ' . $setSql;
      $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
      $execRet = $pdoStatement->execute($mapData);
      return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false;
    } else {
      return false;
    }
  }

  public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) {
    if(!empty($tableName) && count($data) > 0) {
      $sql = 'UPDATE ' . $tableName . ' SET ';
      list($setSql, $mapSetData) = $this->FDFields($data);
      $sql .= $setSql;
      $mapData = array_merge($mapData, $mapSetData);
      list($where, $mapData) = $this->FDCondition($condition, $mapData);
      $sql .= $where ? ' WHERE ' . $where : '';
      $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
      $execRet = $pdoStatement->execute($mapData);
      return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false;
    } else {
      return false;
    }
  }

  public function delete($tableName, $condition, $mapData = array()) {
    if(!empty($tableName) && $condition){
      $sql = 'DELETE FROM ' . $tableName;
      list($where, $mapData) = $this->FDCondition($condition, $mapData);
      $sql .= $where ? ' WHERE ' . $where : '';
      $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
      $execRet = $pdoStatement->execute($mapData);
      return $execRet;
    }
  }

1、odbc

//字段关联数组处理, 主要用于写入和更新数据、同and 或 or 的查询条件,产生sql语句和映射字段的数组
  public function FDFields($data, $link = ',', $judge = array(), $aliasTable = ''){
    $sql = '';
    $mapData = array();
    foreach($data as $key => $value) {
      $mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;
      $sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;
      $mapData[$mapIndex] = $value;
    }
    $sql = trim($sql, $link);
    return array($sql, $mapData);
  }
  //用于处理单个字段处理
  public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {
    $mapIndex = ':' . $preMap . $aliasTable . $field;
    $sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;
    $mapData[$mapIndex] = $value;
    return array($sql, $mapData);
  }
  //使用刚方法可以便捷产生查询条件及对应数据数组
  public function FDCondition($condition, $mapData) {
    if(is_string($condition)) {
        $where = $condition;
    } else if (is_array($condition)) {
      if($condition['str']) {
        if (is_string($condition['str'])) {
          $where = $condition['str'];
        } else {
          return false;
        }
      }
      if(is_array($condition['data'])) {
        $link = $condition['link'] ? $condition['link'] : 'and';
        list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge']);
        if ($conSql) {
          $where .= ($where ? ' ' . $link : '') . $conSql;
          $mapData = array_merge($mapData, $mapConData);
        }
      }
    }
    return array($where, $mapData);
  }

1、ACRUISER查询能够利用,可是有数组形式的都行不通,value值为字符串的都识别为空字符串,富含insert,update方法,以致where的数组形式(where的字符串等此外情势得以行使卡塔尔(قطر‎
2、A悍马H2查询中的like(State of Qatar方法不帮衬,用sql查询能够,但须求当心的是access原生语句是用*来合作,但鉴于php用的pdo或odbc来链接,所以如故用%来合营
3、A揽胜查询中的select_sum()、select_svg(卡塔尔(قطر‎等措施都不可能用,主因access中别称无法是原来名称,但是足以select(‘sum(id卡塔尔国as abc’State of Qatar,相同外号不要和原始名称相通
4、顺便发掘row_array()或者result_array(卡塔尔国的四个主题素材,即别称与另一个想赢得的字段重名时,此方法只好抽出一个字段,而真的的mysql,access语句会产生四个同名字段,所以外号一定毫无与要询问的其余字段名相像
5、access不扶助limit语句,能够在select中用top n的方式获得
6、insert假诺用原生access sql语句,必需抬高into,即”insert into …”
7、查询和写入普通话时要求转码mb_convert_encoding($v,’utf-8′,’gbk’卡塔尔(قطر‎或许iconv(‘gbk’,
‘utf-8’, $vState of Qatar,推荐第黄金年代种,但保障mb函数有依据

上述所述就是本文的全部内容了,希望我们能够赏识。

可是AENCORE这么多限制,还比不上直接用原生sql语句来写。可是原生sql语句中防注入等要求小心(CI中的$this->db->escape($titleState of Qatar方法测量检验不起成效),所以提出用pdo_odbc驱动,用pdo中的方法:

达成数据库连接单例化,有三要素 静态变量、静态实例化方法、私有构造函数
DPDO.php

//config/database.php文件中
//默认的mysql数据库
$db['default'] = array(
    'dsn' => '',
    'hostname' => '127.0.0.1',
    'username' => 'xxx',
    'password' => 'xxx',
//    'hostname' => 'localhost',
//    'username' => 'root',
//    'password' => 'admin',
    'database' => 'xxx',
    'dbdriver' => 'mysqli',
    'dbprefix' => 'ecs_',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);
//access数据库(这个是用的odbc驱动)
$db['access']['hostname'] = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=D:/public/Database1.mdb";//你自己的数据库路径
//$db['access']['hostname'] = "pdo:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=D:/public/Database1.mdb";//这里用odbc而不是pdo_odbc驱动,
$db['access']['username'] = "";
$db['access']['password'] = "";
//$db['access']['database'] = "D:/public/Database1.mdb";//测试有没有都没有影响
$db['access']['dbdriver'] = "odbc";
//$db['access']['dbdriver'] = "pdo";//这里用odbc而不是pdo_odbc驱动,
$db['access']['dbprefix'] = ""; 
$db['access']['pconnect'] = TRUE; 
$db['access']['db_debug'] = TRUE; 
$db['access']['cache_on'] = FALSE; 
$db['access']['cachedir'] = ""; 
$db['access']['char_set'] = "utf8"; 
$db['access']['dbcollat'] = "utf8_general_ci"; 
$db['access']['swap_pre'] = ''; 
$db['access']['autoinit'] = TRUE; 
$db['access']['stricton'] = FALSE;

内需敞开php的pdo扶植,php5.1以上版本扶植

如上便是PHP连接Access数据库相关,刚接触,应该还会有众多坑在前方。

测验文件test.php

能够参照那篇文章。

$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");
//读取access中数据
$access_db = $this->load->database('access', TRUE);
$access_db->select('*')->get('user')->result_array();
//读取mysql中的数据
$mysql_db = $this->load->database('default', TRUE);
$mysql_db->select('*')->get('user')->result_array();
//或者还是原先的mysql查询
$this->db->select('*')->get('user')->result_array();
//PDO查询
$sql="SELECT * FROM user WHERE user_name=:user_name";
$pdoStatement=$access_db->conn_id->prepare($sql);//注意conn_id
$user_name='abc';
$pdoStatement->bindValue(':user_name',$user_name);
$pdoStatement->execute();
$result=$pdoStatement->fetchAll(PDO::FETCH_ASSOC);
//直接在controller中(这个是用的pdo_odbc驱动)
$dbname = 'D:/public/Database1.mdb';//名称随时可以改变
$access['hostname'] = "odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ={$dbname}";
//$access['hostname'] = "DRIVER={Microsoft Access Driver (*.mdb)}; //这里不用odbc而是用pdo_odbcDBQ={$dbname}";
$access['username'] = "";
$access['password'] = "";
$access['database'] = $dbname;//测试有没有都没影响
$access['dbdriver'] = "pdo";
//$access['dbdriver'] = "odbc";//这里不用odbc而是用pdo_odbc
$access['dbprefix'] = "";
$access['pconnect'] = TRUE;
$access['db_debug'] = TRUE;
$access['cache_on'] = FALSE;
$access['cachedir'] = "";
$access['char_set'] = "utf8";
$access['dbcollat'] = "utf8_general_ci";
$access['swap_pre'] = '';
$access['autoinit'] = TRUE;
$access['stricton'] = FALSE;
//加载
$access_db = $this->load->database($access, true);
//读取access数据
$access_db->select('*')->get('user')->result_array();
//而mysql数据库仍然可以这样:
$this->db->select('*')->get('user')->result_array();

可以封装成相关的类来便于访谈,github上搜下会超级多,参照写就能够(也能够参照原先自个儿写的这个,这些是读取mysql数据库的,况且非常不完备)。

使用时:

2、pdo_odbc

要想php中能使用odbc,则必需安装相关驱动,能够在phpinfo中查看:

1、配置文件中静态加载:

2、动态加载配置,由于access文件数据库名称只怕时时变动,所以放在config/database.php文件中就不稳妥了为此需求每10日加载切换

澳门新葡萄京官网首页 1

地方例子中即便用的构造化查询,后边简单的称呼AKoleos查询,可是实际上测试用ACR-V来查询access数据库有多数的约束,以致与mysql语法的多数区分:

既是上方用pdo的原生语法,自个儿封装类,那实在也足以不用CI的数据库配置导入等,直接用原生php方法连接就能够:

澳门新葡萄京官网首页 2

发表评论

电子邮件地址不会被公开。 必填项已用*标注