[mysql]在最后一行中显示只有 1 列

标签: sql MySQL PHP
发布时间: 2017/1/9 21:34:50
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我已经检查过很多次 myPhpAdmin 中的查询和死肯定他们是绝对正确的。此外,如果我手动编写循环 3 次,设置 2,3,4 而不是增加计数器,它仍显示只有一列在最后一排。两行的第一个结果是准确的。 foreach (作为 $rec $row) 基本上从另一个查询的印刷表格表头运行 17 倍。

$by_type1 = array("First","Second+","Final"); 
$counter = 2; //this counter represents type of interview (2-First, 3-Second+, 4-Final)

foreach ($by_type1 as $type1) 
{    
    $table_row = '<tr><td class="rborder">'.$type1.'</td>';

    foreach ($row as $rec)
    {
        $id=$rec['id'];
        $qry2 = "SELECT 
            CONCAT( r.fname, ' ', r.lname ) AS rname, 
            ch.status_id as Type, 
            count(ch.status_id) as number

            FROM candidateJoborderHistory ch 

            LEFT JOIN candidates_info c ON ch.candidate_id = c.candidate_id 
            LEFT JOIN recruiters r ON c.recruiter_id=r.recruiter_id 
            LEFT JOIN interviewtypes i ON ch.interview_id = i.interview_id 

            WHERE c.recruiter_id = $id AND UNIX_TIMESTAMP(ch.date_interview) BETWEEN 1401667200 AND     
            1402099200 AND ch.status_id = $counter
            group by ch.status_id";



        global $conn;
        $conn->open();
        $stmt2 = $conn->prepare($qry2);
        $stmt2->execute();
        $row2 = $stmt2->fetchAll();

        foreach($row2 as $row_x)
        { 
            $table_row .= '<td>'.$row_x['number'].'</td>';
        }

    }

    $table_row .='</tr>';
    echo $table_row . "\n";
    $counter++;
}

我想要的是

First   6   6   4   4   11  6   12  3
Second+ 3   1   2   1   3
Final   3   2   1   4   1 

但我的意思是

First   6   6   4   4   11  6   12  3
Second+ 3   1   2   1   3
Final   3 

让我知道是否你已经了解代码流的问题。

解决方法 1:

嗯,我想这是一个 MySQL 的方法,已经对您的代码进行了一些更改,并解释︰

$by_type1 = array(2=>"First", 3=>"Second+", 4=>"Final"); 
$counter = 2; //this counter represents type of interview (2-First, 3-Second+, 4-Final)

// Open connection first.
global $conn;
$conn->open();

// Using key => value array gets code simple
foreach ($by_type1 as $counter=>$type1) 
{    
    $table_row = '<tr><td class="rborder">'.$type1.'</td>';

    foreach ($row as $rec)
    {
        $id=$rec['id'];

        $qry2 = "SELECT 
            CONCAT( r.fname, ' ', r.lname ) AS rname, 
            ch.status_id as Type, 
            count(ch.status_id) as number

            FROM candidateJoborderHistory ch 

            LEFT JOIN candidates_info c ON ch.candidate_id = c.candidate_id 
            LEFT JOIN recruiters r ON c.recruiter_id=r.recruiter_id 
            LEFT JOIN interviewtypes i ON ch.interview_id = i.interview_id 

            WHERE c.recruiter_id = $id 
            AND UNIX_TIMESTAMP(ch.date_interview) 
            BETWEEN 1401667200 AND 1402099200 
            AND ch.status_id = $counter
            GROUP BY ch.status_id"; 

            $res = $conn->prepare($qry2);
            $res->execute();

        // Loop to get data...
         while($row_x = $res->fetch(PDO::FETCH_ASSOC))
        { 
            $table_row .= '<td>'.$row_x['number'].'</td>';
        }

    }

    $table_row .='</tr>';
    echo $table_row . "\n";
}
$conn->close();

但如果你有任何疑问,只是问,不被测试代码。希望它能帮助 !

参考文件︰

预准备语句 执行语句

赞助商