使用PHP开发HR系统（6）
本节讲述如何连接postgre数据库并查询与显示数据。
==================================================================================================
前几节我们搭建了环境并处理了页面的一些问题，本节我们研究如何连接真实的数据库并把数据显示在页面上。
从主页我们看到有"在职人员总数"，我们就以这个数据为例来说明：
1、建立数据集库表
|
create table "public"."t_employee" ( "cid" varchar(255) collate "default" not null, "cname" varchar(255) collate "default", "cname_e" varchar(255) collate "default", "department_unit_code" varchar(255) collate "default", "department_unit" varchar(255) collate "default", "personnel_scope" varchar(255) collate "default", "personnel_subscope" varchar(255) collate "default", "salary_range" varchar(255) collate "default", "office" varchar(255) collate "default", "position_number" varchar(32) collate "default", "position_name" varchar(32) collate "default", "post_directory_code" varchar(255) collate "default", "post_directory_name" varchar(255) collate "default", "jobs_numbers" varchar(32) collate "default", "jobs_name" varchar(32) collate "default", "jobs_seq_name" varchar(32) collate "default", "jobs_subseq_name" varchar(32) collate "default", "jobs_second_name" varchar(32) collate "default", "post_hierarchy_name" varchar(32) collate "default", "employee_group" varchar(32) collate "default", "employee_subgroup" varchar(32) collate "default", "is_frontline" varchar(8) collate "default", "higher_position" varchar(255) collate "default", "line_manager" varchar(255) collate "default", "line_manager_id" varchar(32) collate "default", "hrbp_id" varchar(32) collate "default", "hrbp_name" varchar(255) collate "default", "nationality" varchar(32) collate "default", "male" varchar(2) collate "default", "marriage" varchar(4) collate "default", "fertility" varchar(255) collate "default", "census" varchar(255) collate "default", "census_addr" varchar(255) collate "default", "census_nature" varchar(255) collate "default", "birthdate" date, "age" int4, "id_cards" varchar(32) collate "default", "mobile_telephone" varchar(255) collate "default", "enterprise_email" varchar(255) collate "default", "personal_email" varchar(255) collate "default", "education" varchar(255) collate "default", "profession" varchar(255) collate "default", "institution" varchar(255) collate "default", "graduation_date" date, "bank_account" varchar(255) collate "default", "payee" varchar(255) collate "default", "kinsfolk" varchar(32) collate "default", "kinsfolk_phone" varchar(32) collate "default", "kinsfolk_relation" varchar(32) collate "default", "date_of_appointment" date, "state_employees" varchar(4) collate "default", "permanent_office" varchar(255) collate "default", "sources_of_recruitment" varchar(255) collate "default", "sources_of_secondary_recruitment" varchar(255) collate "default", "date_of_entry" date, "date_of_inbloc" date, "date_of_seniority" date, "date_of_work" date, "date_of_dimission" date, "date_of_contract_start" date, "date_of_contract_end" date, "the_subject_of_contracts" varchar(255) collate "default", "the_count_of_contracts" varchar(255) collate "default", "is_second_work" varchar(255) collate "default", "address" varchar(255) collate "default", "is_competition_agreement" varchar(255) collate "default", "performance_manager_name" varchar(255) collate "default", "performance_manager_id" varchar(32) collate "default", "profile_photo" bytea, "post_subhierarchy_name" varchar(32) collate "default", "pay_rank" varchar(4) collate "default", "nation" varchar(32) collate "default" ) with (oids=false)
; comment on column "public"."t_employee"."cid" is '员工编号'; comment on column "public"."t_employee"."cname" is '员工姓名'; comment on column "public"."t_employee"."cname_e" is '员工英文名'; comment on column "public"."t_employee"."department_unit_code" is '部门编码'; comment on column "public"."t_employee"."department_unit" is '部门'; comment on column "public"."t_employee"."fertility" is '生育状况'; comment on column "public"."t_employee"."census" is '户籍所在地'; comment on column "public"."t_employee"."census_addr" is '户籍地址'; comment on column "public"."t_employee"."census_nature" is '户口性质'; comment on column "public"."t_employee"."education" is '学历'; comment on column "public"."t_employee"."profession" is '所学专业'; comment on column "public"."t_employee"."institution" is '院校'; comment on column "public"."t_employee"."graduation_date" is '毕业时间'; comment on column "public"."t_employee"."bank_account" is '银行账号'; comment on column "public"."t_employee"."payee" is '收款人'; comment on column "public"."t_employee"."date_of_dimission" is '离职日期'; comment on column "public"."t_employee"."nation" is '民族';
-- ---------------------------- -- alter sequences owned by -- ----------------------------
-- ---------------------------- -- primary key structure for table t_employee -- ---------------------------- alter table "public"."t_employee" add primary key ("cid"); |
并导入一些员工信息；
2、配置数据库连接
2.1 配置php.ini文件
去掉extension=php_pdo_pgsql.dll和extension=php_pgsql.dll之前的逗号
|
; extension=php_bz2.dll extension=php_curl.dll extension=php_com_dotnet.dll ;extension=php_enchant.dll extension=php_fileinfo.dll extension=php_gd2.dll extension=php_gettext.dll extension=php_gmp.dll extension=php_intl.dll extension=php_imap.dll ;extension=php_interbase.dll extension=php_ldap.dll extension=php_mbstring.dll extension=php_exif.dll ; must be after mbstring as it depends on it extension=php_mysql.dll extension=php_mysqli.dll ;extension=php_oci8_12c.dll ; use with oracle database 12c instant client extension=php_openssl.dll ;extension=php_pdo_firebird.dll extension=php_pdo_mysql.dll extension=php_pdo.dll extension=sqlite3.dll extension=php_pdo_sqlite.dll extension=php_pdo_oci.dll extension=php_pdo_odbc.dll extension=php_pdo_pgsql.dll extension=php_pgsql.dll ;extension=php_shmop.dll extension=php_sqlsrv_56_ts.dll |
2.2 配置ci数据库连接参数
打开application->config->database.php文件，修改默认数据库连接参数
|
<?php defined('basepath') or exit('no direct script access allowed');
/* | ------------------------------------------------------------------- | database connectivity settings | ------------------------------------------------------------------- | this file will contain the settings needed to access your database. | | for complete instructions please consult the 'database connection' | page of the user guide. | | ------------------------------------------------------------------- | explanation of variables | ------------------------------------------------------------------- | | ['dsn'] the full dsn string describe a connection to the database. | ['hostname'] the hostname of your database server. | ['username'] the username used to connect to the database | ['password'] the password used to connect to the database | ['database'] the name of the database you want to connect to | ['dbdriver'] the database driver. e.g.: mysqli. | currently supported: | cubrid, ibase, mssql, mysql, mysqli, oci8, | odbc, pdo, postgre, sqlite, sqlite3, sqlsrv | ['dbprefix'] you can add an optional prefix, which will be added | to the table name when using the query builder class | ['pconnect'] true/false - whether to use a persistent connection | ['db_debug'] true/false - whether database errors should be displayed. | ['cache_on'] true/false - enables/disables query caching | ['cachedir'] the path to the folder where cache files should be stored | ['char_set'] the character set used in communicating with the database | ['dbcollat'] the character collation used in communicating with the database | note: for mysql and mysqli databases, this setting is only used | as a backup if your server is running php < 5.2.3 or mysql < 5.0.7 | (and in table creation queries made with db forge). | there is an incompatibility in php with mysql_real_escape_string() which | can make your site vulnerable to sql injection if you are using a | multi-byte character set and are running versions lower than these. | sites using latin-1 or utf-8 database character set and collation are unaffected. | ['swap_pre'] a default table prefix that should be swapped with the dbprefix | ['encrypt'] whether or not to use an encrypted connection. | | 'mysql' (deprecated), 'sqlsrv' and 'pdo/sqlsrv' drivers accept true/false | 'mysqli' and 'pdo/mysql' drivers accept an array with the following options: | | 'ssl_key' - path to the private key file | 'ssl_cert' - path to the public key certificate file | 'ssl_ca' - path to the certificate authority file | 'ssl_capath' - path to a directory containing trusted ca certificates in pem format | 'ssl_cipher' - list of *allowed* ciphers to be used for the encryption, separated by colons (':') | 'ssl_verify' - true/false; whether verify the server certificate or not | | ['compress'] whether or not to use client compression (mysql only) | ['stricton'] true/false - forces 'strict mode' connections | - good for ensuring strict sql while developing | ['ssl_options'] used to set various ssl options that can be used when making ssl connections. | ['failover'] array - a array with 0 or more data for connections if the main should fail. | ['save_queries'] true/false - whether to "save" all executed queries. | note: disabling this will also effectively disable both | $this->db->last_query() and profiling of db queries. | when you run a query, with this setting set to true (default), | codeigniter will store the sql statement for debugging purposes. | however, this may cause high memory usage, especially if you run | a lot of sql queries ... disable this to avoid that problem. | | the $active_group variable lets you choose which connection group to | make active. by default there is only one group (the 'default' group). | | the $query_builder variables lets you determine whether or not to load | the query builder class. */ $active_group = 'default'; $query_builder = true;
$db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'username', 'password' => 'password', 'database' => 'sim', 'dbdriver' => 'postgre', 'port'=>'5432', 'dbprefix' => '', 'pconnect' => false, 'db_debug' => (environment !== 'production'), 'cache_on' => false, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'encrypt' => false, 'compress' => false, 'stricton' => false, 'failover' => array(), 'save_queries' => true ); |
其中需要注意几点：
1）dbdriver设置为'postgre',注意不是'postgres',为什么会是这个名字，我猜是这样：打开system\database\drivers可以看到postgre目录
这个目录下放的就是postgre数据库相关的数据库文件；
2）'port'=>'5432'，注意默认是不包含这个项的，如果电脑上安装了mysql之类的数据库，可能会有问题，所以需要增加这项，笔者的电脑上
没有设置这项显示出错；
3、建立model文件
ci采用了mvc模式，连接数据库操作需要建立model文件。
1）在application->models文件夹下新建memployee.php文件，代码如下：
|
<?php defined('basepath') or exit('no direct script access allowed');
class memployee extends ci_model {
public function __construct(){ parent::__construct(); $this->load->database(); }
public function index() { }
/** * * * @return */ public function getemployeenum(){ $sql = "select state_employees,count(cid) from t_employee group by state_employees"; $query = $this->db->query($sql); //var_dump($query); return $query->result(); } } |
通过getemployeenum函数，我们查询在职员工数量，并返回查询数据集；注意我们首先要在构造函数中加载数据库类：
$this->load->database();
4、建立control文件
由于我们是在主页中显示'在职员工总数'这个数据，因此我们修改application->controllers->main.php文件
|
<?php defined('basepath') or exit('no direct script access allowed');
class main extends ci_controller {
public function __construct(){ parent::__construct(); $this->load->helper('url_helper'); $this->load->model('memployee'); }
public function index() { $data['param'] = $this->memployee->getemployeenum(); $this->load->view('container',$data); } } |
我们通过调用memployee->getemployeenum（）函数得到'在职员工数量',并通过数组$data传递到页面；
5、修改application->views->container.php文件
|
?php defined('basepath') or exit('no direct script access allowed'); ?> <!doctype html> <!--[if ie 8]> <html lang="en" class="ie8"> <![endif]--> <!--[if ie 9]> <html lang="en" class="ie9"> <![endif]--> <!--[if !ie]><!--> <html lang="en"> <!--<![endif]--> <?php $this->load->view('templates/header'); ?> <body class="fixed-top"> <?php $this->load->view('templates/top'); ?> <div id="container" class="row-fluid"> <?php $this->load->view('templates/menu'); ?> <div id="main-content"> <?php $this->load->view('main',$param); ?> </div> </div> <?php $this->load->view('templates/footer'); ?>
</body> </html> |
注意这句：<?php $this->load->view('main',$param); ?>，这里把参数传递到了application->views->main.php文件中；
6、修改application->views->main.php文件代码：
|
<div class="container-fluid"> <!-- begin page header--> <div class="row-fluid"> <div class="span12"> <!-- begin page title & breadcrumb--> <h3 class="page-title"> </h3> <ul class="breadcrumb"> <li> <a href="<?php echo site_url('main/index');?>">首页</a> <span class="divider">/</span> </li> <li class="active"> 人力资源总览 </li> <li class="pull-right search-wrap"> <form action="search_result.html" class="hidden-phone"> <div class="input-append search-input-area"> <input class="" id="appendedinputbutton" type="text"> <button class="btn" type="button"><i class="fa fa-search"></i> </button> </div> </form> </li> </ul> <!-- end page title & breadcrumb--> </div> </div> <!-- end page header--> <!-- begin page content--> <div class="row-fluid"> <!--begin metro states--> <div class="metro-nav" id="metro-nav1"> <div class="metro-nav-block nav-block-orange"> <a data-original-title="" href="#"> <i class="fa fa-user"></i> <div class="info"><?php echo $param[0]->count; ?></div> <div class="status">在职人员总数</div> </a> </div> <div class="metro-nav-block nav-olive"> <a data-original-title="" href="#"> <i class="fa fa-tags"></i> <div class="info"><?php echo $param[1]->count; ?></div> <div class="status">本年度离职人数</div> </a> </div> <div class="metro-nav-block nav-block-yellow"> <a data-original-title="" href="#"> <i class="fa fa-comments-alt"></i> <div class="info"><?php echo $param[2]->count; ?></div> <div class="status">本年度异动人数</div> </a> </div> <div class="metro-nav-block nav-block-green double"> <a data-original-title="" href="#"> <i class="fa fa-eye-open"></i> <div class="info">288</div> <div class="status">本月离职人数</div> </a> </div> <div class="metro-nav-block nav-block-red"> <a data-original-title="" href="#"> <i class="fa fa-bar-chart"></i> <div class="info">255</div> <div class="status">本月待招聘人数</div> </a> </div> </div> <div class="metro-nav"> <div class="metro-nav-block nav-light-purple double"> <a data-original-title="" href="#"> <i class="fa fa-shopping-cart"></i> <div class="info">$8979322442</div> <div class="status">本年薪酬福利</div> </a> </div> <div class="metro-nav-block nav-light-blue double"> <a data-original-title="" href="#"> <i class="fa fa-tasks"></i> <div class="info">$37624</div> <div class="status">本月薪酬福利</div> </a> </div> </div> <div class="space10"></div> <!--end metro states--> </div>
<!-- end page content--> </div> |
代码中这里引用了参数
|
<div class="info"><?php echo $param[0]->count; ?></div> <div class="status">在职人员总数</div> |
6、目前我们的工程结构如下
7 运行测试
运行主页如下所示；
总结：
本文通过建立数据库表，设置连接参数，并分别创建了mvc的不同对象，提取数据显示在界面上，展示了ci数据库操作
的步骤与方法；
程序调试过程中一些错误的处理办法：
1、php连接postgresql，总是报错"call to undefined function pg_connect()"
这是因为系统找不到dll文件造成的，一般在php.ini文件中，去掉extension=php_pdo_pgsql.dll和extension=php_pgsql.dll
之前的逗号就可以了，如果还不行，考虑pgadmin iii文件安装中是否在系统path路径中加载了对应pgadmin iii目录；
2、在memployee.php文件中，我们如果使用汉字例如$sql = "select count(cid) from t_employee where
state_employees='激活'";系统有时候会报编码错误，这是因为这个文件的默认格式不对，可以按照如下方法修改
在codelobster ide中，选择file->change encoding
修改编码为utf-8即可。
====================================the end==========================