php - 如何在一行中获取具有相同ID但在另一列中具有不同值的数据并在php中显示到表中

Question

我的 SQL 表中有以下数据:

id      code     day     time    year
 1     PRC-001    0       t-1     2017
 2     PRC-001    1       t-2     2017
 3     PRC-002    0       t-3     2017
 4     PRC-002    1       t-4     2017
 5     PRC-003    0       t-5     2017
 6     PRC-003    1       t-6     2017

输出应该是这样的:

day0     day1     code      
 t-1      t-2    PRC-001
 t-3      t-4    PRC-002    
 t-5      t-6    PRC-003   

我该怎么做？我正在尝试类似下面的代码。但是我没有得到任何想要的输出。这是我的代码:

$query1 = "SELECT * FROM routine AS tab1,"; 
$query1.= " GROUP BY code";

$rs = mysql_query($query1);
$numOfRows=mysql_num_rows($rs);
$printed = array();
$resultset = array();

while($row = mysql_fetch_assoc($rs)) {
    $resultset[] = $row;
    #print_r($row);
}

$q = "<table id='ash'>";
$q.= "<tr id='grey'>";
$q.= "<th rowspan='2'>day0</th>";
$q.= "<th rowspan='2'>day1(s)</th>";
$q.= "<th rowspan='2'>code</th></tr>";
$q.= "</tr>";


foreach ($resultset as $row){ 
    $q.= "<tr>";
    $q.= "<tr><td id='clist'>".$row["time"]."</td>";
    $q.= "<td id='clist'>".$row["time"]."</td>";
    $q.= "<td id='clist'>".$row["code"]."</td></tr>";
} 
$q .= "</table>";
echo $q;

Answer 1

首先，如评论中所述，您应该考虑使用 mysql_ 函数以外的其他函数。

其次，对于您的查询，您需要删除 GROUP BY。

然后你可以这样做:

$rs = mysql_query("SELECT * FROM routine");

$results = [];

while ($row = mysql_fetch_assoc($rs)) {

    $code = $row['code'];

    if (!isset($results[$code])) {
        $results[$code] = [
            'day0' => '-',
            'day1' => '-',
        ];
    }

    $results[$code]['day' . $row['day']] = $row['time'];

}

?>

<table>
    <thead>
    <tr id="grey">
        <th rowspan="2">Day0</th>
        <th rowspan="2">Day1(s)</th>
        <th rowspan="2">code</th>
    </tr>
    </thead>

    <tbody>
    <?php foreach ($results as $code => $result) : ?>
        <!--You shouldn't have multiple elements using the same ids-->
        <tr>
            <td id='clist'><?php echo $result['day0'] ?></td>
            <td id='clist'><?php echo $result['day1'] ?></td>
            <td id='clist'><?php echo $code ?></td>
        </tr>
    <?php endforeach ?>
    </tbody>
</table>

希望这对您有所帮助!