时间戳

首页 > 解决方案 > 如何解决这两个表之间的内连接问题？

php - 如何解决这两个表之间的内连接问题？

问题描述

我有这个代码工作完美

<?php

    require'dbehrpdo.php';
    $seen = "2";
    $count = 1;
    $total = 0;
    
    $t1 = strtotime($datea." 00:00:00");
    $t2 = strtotime($dateb." 23:59:59");
    
    foreach($conepdo->query("SELECT COUNT(id), school FROM waitinglist WHERE seen = '$seen' AND timeseen BETWEEN $t1 AND $t2 GROUP by school ORDER BY COUNT(id) DESC") as $row) {
    
    echo "<tr>"; 
    echo "<td width='10%'>" . $count . "</td>";
    echo "<td width='70%'>" . $row['school'] . "</td>";
    echo "<td width='20%'>" . $row['COUNT(id)'] . "</td>";
    echo "</tr>"; 
    
    $count = $count + 1;
    
    $total = $total + $row['COUNT(id)'];
    
    }

?>

列“学校”商店缩写，如 NG，美国，但我有另一张表“学校”

id、name 和 abr 作为全名和缩写。

为什么这段代码不起作用？

<?php

    require'dbehrpdo.php';
    $seen = "2";
    $count = 1;
    $total = 0;
    
    $t1 = strtotime($datea." 00:00:00");
    $t2 = strtotime($dateb." 23:59:59");
    
    foreach($conepdo->query("SELECT COUNT(waitinglist.id), schools.name FROM waitinglist INNER JOIN schools ON waitinglist.school = schools.abr WHERE seen = '$seen' AND timeseen BETWEEN $t1 AND $t2 GROUP by school ORDER BY COUNT(id) DESC") as $row) {
    
    echo "<tr>"; 
    echo "<td width='10%'>" . $count . "</td>";
    echo "<td width='70%'>" . $row['schools.name'] . "</td>";
    echo "<td width='20%'>" . $row['COUNT(id)'] . "</td>";
    echo "</tr>"; 
    
    $count = $count + 1;
    
    $total = $total + $row['COUNT(id)'];
    
    }

?>

标签： phphtmlmysql

解决方案

只需为 SQL 查询中的列使用别名

  <?php
    
        require'dbehrpdo.php';
        $seen = "2";
        $count = 1;
        $total = 0;
        
        $t1 = strtotime($datea." 00:00:00");
        $t2 = strtotime($dateb." 23:59:59");

        $sql = "SELECT 
                COUNT(waitinglist.id) AS cnt, 
                schools.name AS school_name 
            FROM waitinglist 
            INNER JOIN schools ON waitinglist.school = schools.abr 
            WHERE seen = '$seen' AND timeseen BETWEEN '$t1' AND '$t2' 
            GROUP by schools.name 
            ORDER BY COUNT(id) DESC";
    
        foreach($conepdo->query($sql) as $row) {
        
          echo "<tr>"; 
            echo "<td width='10%'>" . $count . "</td>";
            echo "<td width='70%'>" . $row['school_name'] . "</td>";
            echo "<td width='20%'>" . $row['cnt'] . "</td>";
          echo "</tr>"; 
        
          $count = $count + 1;
        
          $total = $total + $row['COUNT(id)'];
        
        }
    
    ?>

推荐阅读