php - 如何使用php将mysql结果连接成一个字符串

Question

这是显示特定教师时间表的代码，1--从下拉列表中选择教师姓名，并从表“assign_teacher”中进行比较，2---存储各个教师信息的地方，3---获取上课时间和学生姓名并显示。

    $query="SELECT * FROM assign_teacher WHERE teacher ='$nam' order by session asc " ;
    $result=mysql_query($query);
    $row = mysql_affected_rows();

    if($row >=1){  
    while($ro= mysql_fetch_array($result))

    {  
    ?>

    <tbody>

    <td><?php echo $ro['session']; ?> </td>

    <td><?php $days=explode(',', $ro['days']); if (in_array("Monday", $days) ) {  
            echo $ro['student']; ?>  </td>
                            <?php }
            else echo "----" ; ?>
            <td> <?php if (in_array("Tuesday", $days)) {  
                    echo $ro['student']; ?>  </td>
            <?php } else echo "-----" ; ?>
    <td> <?php if (in_array("Wednesday", $days)) {  
    echo $ro['student']; ?>  </td>
    <?php } else echo "-----" ; ?>
    <td> <?php if (in_array("Thursday", $days)) {  
    echo $ro['student']; ?>  </td>
    <?php } else echo "-----" ; ?>
    <td> <?php if (in_array("Friday", $days)) {  
                echo $ro['student']; ?>  </td>
            <?php } else echo "-----" ; ?>
            <td> <?php if (in_array("Saturday", $days)) {  
                echo $ro['student']; ?>  </td>
            <?php } else echo "-----" ; ?>

        </tr>
        <?php } ?>
    </tbody>
<?php } ?>

---

---

输出如下表:

上午 10:00-11:30 |约翰

上午 10:00-11:30 |阿丽娜

下午 2:00-03:30 |阿里

下午 2:00-03:30 |史密斯

---

但实际上我想要这个:

上午 10:00-11:30 |约翰·阿莱娜

下午 2:00-03:30 |阿里·史密斯

---

我希望同一时间的学生姓名在一行中以逗号分隔，谁能帮我修一下!

Answer 1

确实需要从您的数据库中获取一些数据以了解事情之间的关系。

不过，我假设您有一个每个老师的类(class)表，其中一列包含该类(class)一周中以逗号分隔的天数列表，一列包含该类(class)的学生姓名。

然后您需要每个类(class)的输出，(工作)周的每一天都有一个列，其中包含当天该类(class)中学生的逗号分隔列表。

像这样的东西:-

SELECT session,
        GROUP_CONCAT(IF(FIND_IN_SET('Monday', days), tab1.student, NULL)) as students_monday,
        GROUP_CONCAT(IF(FIND_IN_SET('Tuesday', days), tab1.student, NULL)) as students_tuesday,
        GROUP_CONCAT(IF(FIND_IN_SET('Wednesday', days), tab1.student, NULL)) as students_wednesday,
        GROUP_CONCAT(IF(FIND_IN_SET('Thursday', days), tab1.student, NULL)) as students_thursday,
        GROUP_CONCAT(IF(FIND_IN_SET('Friday', days), tab1.student, NULL)) as students_friday,
        GROUP_CONCAT(IF(FIND_IN_SET('Saturday', days), tab1.student, NULL)) as students_saturday
FROM assign_teacher AS tab1 
WHERE teacher ='$nam' 
GROUP BY tab1.session 
ORDER BY tab1.session asc

通过一些示例数据，我可以检查这是否适用于您的表格。

编辑

敲一个测试表如下，上面SQL的结果就是我想你想要的:-

CREATE TABLE assign_teacher
(
    ID INT, 
    teacher VARCHAR(255), 
    student VARCHAR(255), 
    session VARCHAR(255), 
    days VARCHAR(255)
);

INSERT INTO assign_teacher(ID, teacher, student, session, days)
VALUES
(1, 'Azir', 'Shehzor', '10:00-10:30 am', 'Monday,Saturday'),
(2, 'Azir', 'Salar', '10:00-10:30 am', 'Monday,Saturday');
(3, 'Azir', 'Fred', '14:00-14:30 am', 'Tuesday,Wednesday'),
(4, 'Azir', 'Burt', '14:00-14:30 am', 'Tuesday,Wednesday');
(5, 'Azir', 'Sally', '14:00-14:30 am', 'Tuesday,Wednesday,Thursday,Friday'),
(6, 'Azir', 'Sid', '16:00-16:30 am', 'Monday,Tuesday,Saturday');
(7, 'Azir', 'Bilbo', '16:00-16:30 am', 'Monday,Tuesday,Saturday'),
(8, 'Azir', 'Frodo', '16:00-16:30 am', 'Monday,Tuesday,Saturday');
(9, 'Azir', 'Maggie', '16:00-10:30 am', 'Monday,Tuesday,Saturday'),
(10, 'Azir', 'Lisa', '16:00-16:30 am', 'Monday,Tuesday,Saturday');

结果

session         students_monday         students_tuesday        students_wednesday      students_thursday       students_friday     students_saturday
10:00-10:30 am  Shehzor,Salar           NULL                    NULL                    NULL                    NULL                Shehzor,Salar
14:00-14:30 am  NULL                    Fred,Burt,Sally         Fred,Burt,Sally         Sally                   Sally               NULL
16:00-10:30 am  Maggie                  Maggie                  NULL                    NULL                    NULL                Maggie
16:00-16:30 am  Sid,Bilbo,Frodo,Lisa    Sid,Bilbo,Frodo,Lisa    NULL                    NULL                    NULL                Sid,Bilbo,Frodo,Lisa

编辑

将该 SQL 放入您的脚本中:-

<?php
$query = "SELECT session,
                    GROUP_CONCAT(IF(FIND_IN_SET('Monday', days), tab1.student, NULL)) as students_monday,
                    GROUP_CONCAT(IF(FIND_IN_SET('Tuesday', days), tab1.student, NULL)) as students_tuesday,
                    GROUP_CONCAT(IF(FIND_IN_SET('Wednesday', days), tab1.student, NULL)) as students_wednesday,
                    GROUP_CONCAT(IF(FIND_IN_SET('Thursday', days), tab1.student, NULL)) as students_thursday,
                    GROUP_CONCAT(IF(FIND_IN_SET('Friday', days), tab1.student, NULL)) as students_friday,
                    GROUP_CONCAT(IF(FIND_IN_SET('Saturday', days), tab1.student, NULL)) as students_saturday
            FROM assign_teacher AS tab1 
            WHERE teacher ='".mysql_real_escape_string($nam)."' 
            GROUP BY tab1.session 
            ORDER BY tab1.session ASC";

$result = mysql_query($query);
while($ro = mysql_fetch_array($result))
{  
    ?>

    <tbody>

    <td><?php echo $ro['session']; ?> </td>

    <td>
    <?php 
        echo "<td>".(($ro['students_monday'] == '') ? "----" : $ro['students_monday'] )."</td>";
        echo "<td>".(($ro['students_tuesday'] == '') ? "----" : $ro['students_tuesday'] )."</td>";
        echo "<td>".(($ro['students_wednesday'] == '') ? "----" : $ro['students_wednesday'] )."</td>";
        echo "<td>".(($ro['students_thursday'] == '') ? "----" : $ro['students_thursday'] )."</td>";
        echo "<td>".(($ro['students_friday'] == '') ? "----" : $ro['students_friday'] )."</td>";
        echo "<td>".(($ro['students_saturday'] == '') ? "----" : $ro['students_saturday'] )."</td>";
    ?>
    </tbody>
<?php 
} 
?>

请注意，您的脚本正在使用已弃用的 mysql_* 类型函数(即，对它们的支持已从 php 中删除)。为了与您当前的脚本保持一致，我在我的示例中也使用了这些函数，但您可能应该考虑更改为完全支持的 mysqli_* 类型函数