-- 比赛
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;

<?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];

}
}

<?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‘;

<?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");
}
}

<?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();
}
}

<?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];    //该数组的第一项。

}
}

<!-- 模板文件，利用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>