CodeIgniter Frame Connection Read MS Access database file

CI with the 3.0 version, PHP to read Access database has two drivers, one ODBC, one is PDO_ODBC, both can be linked, but generally more recommended PDO_ODBC,

To be able to use ODBC in PHP, you must install the relevant driver, which can be viewed in phpinfo:

1. ODBC

2, PDO_ODBC

If not, please self-search installation, win under relatively simple, Linux is more trouble, lanmp3.1 integrated environment can read this article.

There are two ways to load another database in the CI framework, as described in the manual, so here are two ways to do it:

1, static loading in the configuration file:

//config/database.php file
// The default mysql database
$ 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 the database (this is driven by odbc)
$ db [‘access’] [‘hostname’] = "DRIVER = {Microsoft Access Driver (* .mdb)}; DBQ = D: /public/Database1.mdb"; // Your own database path
// $ db ['access'] ['hostname'] = "pdo: DRIVER = {Microsoft Access Driver (* .mdb)}; DBQ = D: /public/Database1.mdb"; // odbc is used here instead pdo_odbc driver,
$ db [‘access‘] [‘username‘] = "";
$ db [‘access‘] [‘password’] = "";
// $ db [‘access’] [‘database‘] = "D: /public/Database1.mdb";
$ db [‘access‘] [‘dbdriver’] = "odbc";
// $ db [‘access’] [‘dbdriver’] = "pdo"; // Odbc instead of 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; 

When used:

// Read data in access
$ access_db = $ this-> load-> database (‘access’, TRUE);
$ access_db-> select (‘*‘)-> get (‘user’)-> result_array ();
// Read data in mysql
$ mysql_db = $ this-> load-> database (‘default‘, TRUE);
$ mysql_db-> select (‘*‘)-> get (‘user’)-> result_array ();
// or still the original mysql query
$ this-> db-> select (‘*‘)-> get (‘user’)-> result_array (); 

2, dynamic load configuration, because the Access file database name may change at any time, so placed in the config/database.php file is not appropriate, so you need to load the switch at any time

// Directly in the controller (this is driven by pdo_odbc)
$ dbname = ‘D: /public/Database1.mdb’; // The name can be changed at any time
$ access [‘hostname’] = "odbc: DRIVER = {Microsoft Access Driver (* .mdb)}; DBQ = {$ dbname}";
// $ access [‘hostname’] = "DRIVER = {Microsoft Access Driver (* .mdb)}; // Do not use odbc here but pdo_odbcDBQ = {$ dbname}";
$ access [‘username‘] = "";
$ access [‘password’] = "";
$ access [‘database‘] = $ dbname; // Tests have no effect
$ access [‘dbdriver‘] = "pdo";
// $ access [‘dbdriver’] = "odbc"; // Instead of odbc here, use 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;
//load
$ access_db = $ this-> load-> database ($ access, true);
// Read access data
$ access_db-> select (‘*‘)-> get (‘user’)-> result_array ();
// and the mysql database can still be like this:
$ this-> db-> select (‘*‘)-> get (‘user’)-> result_array (); 

Although the above example uses a structured query, referred to as AR query, but actually testing with AR to query Access database has a lot of restrictions:

1, AR query can be used, but the array form is not valid, value value for the string is recognized as an empty string, including the Insert,update method, and where the array form (where the string and other forms can be used)
2, AR query in the like () method is not supported, with SQL query can, but it is important to note that access is a native statement with * to match, but because PHP with the PDO or ODBC to link, so still use% to match
3, AR query Select_sum (), Select_svg () and other methods can not be used, the main reason is that the alias in Access cannot be the original name, but can select (' Sum (ID) as ABC '), the same alias does not match the original name
4, by the Way found Row_array () or Result_array () a problem, that is, the alias and another want to get the name of the field, this method can only take out a field, and the real mysql,accdess statement will produce two of the same name fields, So the alias must not be the same as the other field names to query
5. Access does not support limit statements, which can be obtained in the form of top N in Select
6, insert if you use the native Access SQL statement, you must add into, that is "insert into ..."

Temporarily found the above differences, the rest of the join, group By,order_by and other methods are consistent with MySQL.

But with so many limitations of AR, it's better to write directly with native SQL statements. However, it is important to note that native SQL statements are not injected (ci $this ->db ->escape ( $title ) method test does not work ), so it is recommended to use the PDO_ODBC driver, using the method in PDO:

// PDO query
$ sql = "SELECT * FROM user WHERE user_name =: user_name";
$ pdoStatement = $ access_db-> conn_id-> prepare ($ sql); // Note the conn_id
$ user_name = ‘abc’;
$ pdoStatement-> bindValue (‘: user_name’, $ user_name);
$ pdoStatement-> execute ();
$ result = $ pdoStatement-> fetchAll (PDO :: FETCH_ASSOC); 

Can be encapsulated into related classes to facilitate access, GitHub on the search will be a lot, refer to write can (also can refer to the original write this, this is read MySQL database, and very imperfect).

CodeIgniter Frame Connection Read MS Access database file