-- 比赛 create table `match` ( m_id int unsigned primary key auto_increment, t1_id int unsigned comment '球队一ID', t2_id int unsigned comment '球队二ID', t1_score int comment '球队一进球', t2_score int comment '球队二进球', m_time int comment '比赛时间 时间戳' )charset=utf8; insert into `match` values (null, 3, 4, 1, 2, unix_timestamp('2015-01-31 17:00:00')), (null, 1, 2, 2, 3, unix_timestamp('2015-01-30 17:00:00')), (null, 4, 2, 2, 0, unix_timestamp('2015-01-27 17:00:00')), (null, 3, 1, 2, 0, unix_timestamp('2015-01-26 17:00:00')), (null, 5, 4, 0, 2, unix_timestamp('2015-01-22 18:30:00')), (null, 8, 5, 0, 1, unix_timestamp('2015-01-10 17:00:00')), (null, 5, 7, 2, 1, unix_timestamp('2015-01-14 17:00:00')), (null, 5, 6, 2, 1, unix_timestamp('2015-01-18 17:00:00')); -- 球队 create table `team` ( t_id int unsigned primary key auto_increment, t_name varchar(20) )charset=utf8; insert into `team` values (1, '伊拉克'), (2, '阿联酋'), (3, '韩国'), (4, '澳大利亚'), (5, '中国'), (6, '朝鲜'), (7, '乌兹别克斯坦'), (8, '沙特'); -- 运动员 create table `player` ( p_id int unsigned primary key auto_increment, p_name varchar(20), t_id int unsigned comment '球队ID' )charset=utf8; insert into `player` values (null, '张琳芃', 5),(null, '郜林', 5),(null, '孙可', 5),(null, '王大雷', 5),(null, '吴曦', 5) ,(null, '于海', 5); select * from `match` ; select t1.t_name, m.t1_score, m.t2_score, m.t2_id, m.m_time from `match` as m left join `team` as t1 ON m.t1_id = t1.t_id; select t1.t_name, m.t1_score, m.t2_score, t2.t_name, m.m_time from `match` as m left join `team` as t1 ON m.t1_id = t1.t_id left join `team` as t2 ON m.t2_id=t2.t_id; select t1.t_name as t1_name, m.t1_score, m.t2_score, t2.t_name as t2_name, m.m_time from `match` as m left join `team` as t1 ON m.t1_id = t1.t_id left join `team` as t2 ON m.t2_id=t2.t_id;
./Factory.class.php
<?php /** * 项目中的工厂类 */ class Factory { /** * 生成模型的单例对象 * * @param $model_name string * @return object */ public static function M($model_name) { static $model_list = array();//存储已经实例化好的模型对象的列表，下标模型名，值模型对象 //判断当前模型是否已经实例化 if(!isset($model_list[$model_name])) { //没有实例化过 require './' . $model_name . '.class.php'; $model_list[$model_name] = new $model_name;//可变标志符，可变类 } return $model_list[$model_name]; } }
./match_list_c.php
<?php # 比赛列表 date_default_timezone_set('PRC'); header('Content-Type: text/html; charset=utf-8'); // 实例化相应的模型类对象，调用某个方法，实现固定功能 // require './MatchModel.class.php'; // $m_match = new MatchModel(); //通过工厂获得对象 require './Factory.class.php'; $m_match = Factory::M('MatchModel'); $match_list = $m_match->getList(); // $m_match2 = Factory::M('MatchModel'); // 载入负责显示的html文件 require './template/match_list_v.html';
./MatchModel.class.php
<?php /** * match表的操作模型类 */ require_once './Model.class.php'; class MatchModel extends Model { /** * 获得所有的比赛列表 */ public function getList() { //获得比赛列表数据 $sql = "select t1.t_name as t1_name, m.t1_score, m.t2_score, t2.t_name as t2_name, m.m_time from `match` as m left join `team` as t1 ON m.t1_id = t1.t_id left join `team` as t2 ON m.t2_id=t2.t_id;"; return $this->_dao->getAll($sql); } /** * 删除某场比赛 */ public function removeMatch($m_id) { $sql = "delete from `match` where m_id=$m_id"; return $this->_dao->query($sql); } public function rmTeam($t_id) { return $this->_dao->query("delete from `team` where t_id = $t_id"); } }
./Model.class.php
<?php /** * 基础模型类 */ class Model { protected $_dao;//存储DAO对象的属性，可以在子类方法中被访问，使用DAO对象 /** * 初始化DAO */ protected function _initDAO() { //初始化MySQLDB $config = array('host' => '127.0.0.1', 'port' => '3306', 'username'=>'root', 'password' => 'h0000dh@', 'charset'=>'utf8', 'dbname'=>'itcast'); require_once './MySQLDB.class.php'; $this->_dao = MySQLDB::getInstance($config);//$dao , Database Access Object 数据库操作对象（dao层） } /** * 构造方法 */ public function __construct() { // 初始化DAO $this->_initDAO(); } }
./MySQLDB.class.php
<?php //类名，也习惯上（推荐）使用跟文件名相似的名字 //定义一个mysql连接类，该类可以连接mysql数据库 //并实现其单例模式 //该类的功能还能够完成如下基本mysql操作： //执行普通的增删改非返回结果集的语句 //执行select语句并可以返回3种类型的数据： //多行结果（二维数组），单行结果（一维数组） //单行单列（单个数据） class MySQLDB{ public $host; public $port; public $username; public $password; public $charset; public $dbname; //连接结果（资源） private static $link; private $resourc; public static function getInstance($config){ if(!isset(self::$link)){ self::$link = new self($config); } return self::$link; } //构造函数：禁止new private function __construct($config){ //初始化数据 $this->host = isset($config['host']) ? $config['host'] : 'localhost'; $this->port = isset($config['port']) ? $config['port'] : '3306'; $this->username = isset($config['username']) ? $config['username'] : 'root'; $this->password = isset($config['password']) ? $config['password'] : ''; $this->charset = isset($config['charset']) ? $config['charset'] : 'utf8'; $this->dbname = isset($config['dbname']) ? $config['dbname'] : ''; //连接数据库 $this->connect(); //设定连接编码 $this->setCharset($this->charset); //选定数据库 $this->selectDb($this->dbname); } //禁止克隆 private function __clone(){} //这里进行连接 public function connect(){ $this->resourc = mysql_connect("$this->host:$this->port", "$this->username","$this->password") or die("连接数据库失败！"); } public function setCharset($charset){ mysql_set_charset($charset, $this->resourc); } public function selectDb($dbname){ mysql_select_db($dbname, $this->resourc); } /** * 功能：执行最基本（任何）sql语句 * 返回：如果失败直接结束，如果成功，返回执行结果 */ public function query($sql){ if(!$result = mysql_query($sql, $this->resourc)) { echo ("<br />执行失败。"); echo "<br />失败的sql语句为：" . $sql; echo "<br />出错信息为：" . mysql_error(); echo "<br />错误代号为：" . mysql_errno(); die(); } return $result; } /** * 功能：执行select语句，返回2维数组 * 参数：$sql 字符串类型 select语句 */ public function getAll($sql){ $result = $this->query($sql); $arr = array(); //空数组 while( $rec = mysql_fetch_assoc( $result )){ $arr[] = $rec;//这样就形成二维数组 } return $arr; } //返回一行数据（作为一维数组） public function getRow($sql){ $result = $this->query($sql); //$rec = array(); if( $rec2 = mysql_fetch_assoc( $result )){//返回下标为字段名的数组 //如果fetch出来有数据（也就是取得了一行数据），结果自然是数组 return $rec2; } return false; } //返回一个数据（select语句的第一行第一列） //比如常见的：select count(*) as c from XXX where ... public function getOne($sql){ $result = $this->query($sql); $rec = mysql_fetch_row($result);//返回下标为数字的数组,且下标一定是0,1,2, 3..... //如果没有数据，返回false if($result === false){ return false; } return $rec[0]; //该数组的第一项。 } }
./template/.htaccess
Deny from All
./template/match_list_v.html
<!-- 模板文件，利用HTML代码展示数据 --> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>比赛列表</title> </head> <body> <table> <tr> <th>球队一</th><th>比分</th><th>球队二</th><th>时间</th> </tr> <?php foreach($match_list as $row) : ?> <tr> <td><?php echo $row['t1_name'];?></td> <td><?php echo $row['t1_score'];?>:<?php echo $row['t2_score'];?></td> <td><?php echo $row['t2_name'];?></td> <td><?php echo date('Y-m-d H:i', $row['m_time']);?></td> </tr> <?php endForeach;?> </table> </body> </html>
