时间戳

首页 > 解决方案 > 用 pdo 连接两个表

php - 用 pdo 连接两个表

问题描述

我想在我的数据库中加入两个表。它以“经典”方式运行良好，但我的脚本中有一堆选择查询，所以我想缩短它......

这是联合sql：

SELECT
    matches.id, matches.start_time, matches.category_id, 
    matches.highl, first_team.tid, first_team.t_name, 
    second_team.tid, second_team.t_name AS ft_name 
FROM matches 
    INNER JOIN teams AS first_team ON matches.first_team_id = first_team.tid 
    INNER JOIN teams AS second_team ON matches.second_team_id = second_team.tid 
ORDER BY matches.id DESC 
    LIMIT 10

我想要实现的是这样的，但我不完全知道如何添加我上面复制的所有值。

public function getMatches($table,$conditions = array()){
    $sql = 'SELECT ';
    $sql .= array_key_exists("select",$conditions)?$conditions['select']:'';
    $sql .= ' FROM '.$table;
    if(array_key_exists("where",$conditions)){
        $sql .= ' WHERE ';
        $i = 0;
        foreach($conditions['where'] as $key => $value){
            $pre = ($i > 0)?' AND ':'';
            $sql .= $pre.$key." = '".$value."'";
            $i++;
        }
    }

    if(array_key_exists("inner_join",$conditions)){
        $sql .= ' INNER JOIN '.$conditions['inner_join'];
    }

    if(array_key_exists("on",$conditions)){
        $sql .= ' ON '.$conditions['on'];
    }

    if(array_key_exists("as",$conditions)){
        $sql .= ' AS '.$conditions['as'];
    }

    if(array_key_exists("order_by",$conditions)){
        $sql .= ' ORDER BY '.$conditions['order_by'];
    }

    if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
        $sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit'];
    }elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
        $sql .= ' LIMIT '.$conditions['limit'];
    }

    $query = $this->db->prepare($sql);
    $query->execute();

    if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
        switch($conditions['return_type']){
            case 'count':
                $data = $query->rowCount();
            break;
            case 'single':
                $data = $query->fetch(PDO::FETCH_ASSOC);
            break;
            default:
                $data = '';
        }
    }else{
        if($query->rowCount() > 0){
            $data = $query->fetchAll();
        }
    }
    return !empty($data)?$data:false;
    }
}

这是输出：

<div class="panel-heading">Matches</div>
    <table class="table">
        <tr>
          <th>#</th>
          <th>First Team</th>
          <th>Second Team</th>
          <th>Start Time</th>
        </tr>
        <?php
        include 'inc/functions.php';
        $db = new DB();
        $matches = $db->getMatches('matches', array('inner_join'=>'teams'), array('as'=>'first_team'), array('on'=>'matches.first_team_id = first_team.tid'), array('inner_join'=>'teams'), array('as'=>'second_team'), array('on'=>'matches.second_team_id = second_team.tid'), array('order_by'=>'matches.id'));
        if(!empty($matches)){ $count = 0; foreach($matches as $result){ $count++;?>
        <tr>
          <td><?php echo $count; ?></td>
          <td><?php echo $result['ft_name']; ?></td>
          <td><?php echo $result['t_name']; ?></td>
          <td><?php echo $result['start_time']; ?></td>
        </tr>
        <?php } }else{ ?>
        <tr><td colspan="4">No entry found!</td>
        <?php } ?>
    </table>
</div>

标签： phpmysqljoin

解决方案

老实说，在我的一生中，我永远永远不会明白，这样的 PHP 数组墙

array('inner_join'=>'teams'), array('as'=>'first_team'), 
array('on'=>'matches.first_team_id = first_team.tid'), 
array('inner_join'=>'teams'), 
array('as'=>'second_team'), 
array('on'=>'matches.second_team_id = second_team.tid'), 
array('order_by'=>'matches.id'));

甚至可以被认为比优雅紧凑的 SQL 更“短”

SELECT
    matches.id, matches.start_time, matches.category_id, 
    matches.highl, first_team.tid, first_team.t_name, 
    second_team.tid, second_team.t_name AS ft_name 
FROM matches 
    INNER JOIN teams AS first_team ON matches.first_team_id = first_team.tid 
    INNER JOIN teams AS second_team ON matches.second_team_id = second_team.tid 
ORDER BY matches.id DESC 
    LIMIT 10

更不用说意义和可读性了。

您是在说节省自己键入一些 SQL 关键字（如 SELECT 或 FROM）吗？严重地？真的值得把一个有意义且易于理解的 SQL 搞得一团糟吗？

推荐阅读