使用PHP開發HR系統(6)

来源:https://www.cnblogs.com/zsnzsn/archive/2019/08/19/11380397.html
-Advertisement-
Play Games

本節講述如何連接Postgre資料庫並查詢與顯示數據。 ================================================================================================== 前幾節我們搭建了環境並處理了頁面的一些問題,本... ...


   

本節講述如何連接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

);

其中需要註意幾點:

1dbdriver設置為'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資料庫操作

的步驟與方法;

程式調試過程中一些錯誤的處理辦法:

1php連接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==========================

   

   

   

 

   

   

   

   

   

   

   

   

   

   

   

   

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 一、引用類型 1.String是sun在JAVASE中提供的字元串類型 2.String.class字碼 3.String是引用數據類型,s是變數名,“abc”是字面值; 二、三元運算符 1.語法規則:布爾表達式 ? 表達式1:表達式2 2.三元運算符執行原理 當布爾表達式的結果為true時,選擇表 ...
  • Spring是以Ioc和Aop為內核,提供了表現層spring MVC 和持久層Spring JDBC等眾多應用技術,還能整合開源世界眾多著名的第三方框架和類庫,成為使用最多的JavaEE企業應用開源框架。 Spring的優勢: Spring的體繫結構:全部基於核心容器Core Container ...
  • 參數明細 查看大圖 ThreadPoolExecutor執行順序: 線程池按以下行為執行任務 當線程數小於核心線程數時,創建線程。 當線程數大於等於核心線程數,且任務隊列未滿時,將任務放入任務隊列。 當線程數大於等於核心線程數,且任務隊列已滿 若線程數小於最大線程數,創建線程 若線程數等於最大線程數 ...
  • SpringBoot預設的錯誤處理機制 即我們常見的白色的ErrorPage頁面 瀏覽器發送的請求頭: 如果是其他的請求方式,比如客戶端,則相應一個json數據: 原理;是通過 ErrorMvcAutoConfiguration,錯誤處理的自動配置; 給容器中添加了以下組件 1、DefaultErr ...
  • 一、前言 今天為什麼要談論這個話題昵?想必大家都知道了,又到一年的中跳槽季了,肯定有一些小伙伴寂寞難耐,想出去搞事情了。在此,我拋出三個詞,這三詞應該歸屬面試最熱詞的範疇了,這是我自身體會及從各個同行公認的。下麵我簡單概述一下,希望對大伙有所幫助。 二、設計模式 + 概念 設計模式(Design P ...
  • 摘要: 無論是從IDEA還是其他的SDS開發工具亦或是https://start.spring.io/ 進行解壓,我們都會得到同樣的一個 文件 標簽的含義 找到本地倉庫的 坐標 關註點 說明我們的工程可以進行改造進行替換掉原來工程的 標簽. 必須要有否則打 包時候無法正常啟動. 在配置文件加上 即可 ...
  • String、StringBuffer和StringBuilder String是java里一個常用類,關於String從源碼中我們可以知道String是不可以有子類的。 String類由final修飾,是不能有子類的。 String實現了序列化介面(Serializable),預示著String對 ...
  • 新聞 "宣告.NET Core 3.0預覽版8" "新的fable.io站點伴隨著更多文檔發佈" "正在努力使你的團隊相信F 的益處?Compositional IT能夠提供幫助" "提名2019年度F 社區英雄" "研討:SAFE模板簡化" "2019年度最高收入的編程語言" "介紹Boots:流 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...