dsuw85815
dsuw85815
2014-04-04 10:56

MySql COALESCE无法正常工作

已采纳

I have this table, 2 courses with 12 possible subjects (content: subject ID, or NULL if no more subjects exsist). Please note that it can contain up to 50 different courses, not only 2

table

I am trying by a WHILE to show course name (workis fine) and by COALESCE to show in each course the course's subjects ID, this should be the final result:

Curso de Programación Creativa con PHP y MySQL - CURSO: 1 - CURSO: 2 - CURSO: 10 -

Máster en Diseño y Desarrollo Web - CURSO: 1 -


But something is failing as with my COALESCE expression I get:

Curso de Programación Creativa con PHP y MySQL - CURSO: 1 - CURSO: 1 -

Máster en Diseño y Desarrollo Web - CURSO: 1 - CURSO: 1 -

Here is the code:

$select = select("SELECT * FROM course_conf JOIN course_type ON ct_id=co_fk_ct_id ORDER BY co_name");




while($registroBbdd = consultaRegistro($select))
{
    $courseName=$registroBbdd['co_name'];

    $result = select("SELECT COALESCE(co_subj1,co_subj2,co_subj3,co_subj4,co_subj5,co_subj6,co_subj7,co_subj8,co_subj9,co_subj10,co_subj11,co_subj12) FROM course_conf");

        echo '<div class="contentColumn80">
                <span class="tableContentText ">'.$courseName.' - </span>';

        while($row=mysql_fetch_array($result))
        {  
                echo '<span>CURSO: '.$row['COALESCE(co_subj1,co_subj2,co_subj3,co_subj4,co_subj5,co_subj6,co_subj7,co_subj8,co_subj9,co_subj10,co_subj11,co_subj12)'].' - </span>'; 
        }
        echo '</div>';
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douxi3404 douxi3404 7年前

    OK, there are two obvious errors:

    • COALESCE() will always return the first param, which is not null (in your case always the value stored in co_subj1)
    • the second SQL-query does not include any WHERE-clause. Because of this you get co_subj1 of all courses

    While probably not the best solution possible, this should work:

    $select = select("SELECT * FROM course_conf JOIN course_type ON ct_id=co_fk_ct_id ORDER BY co_name");
    
    
    
    
    while($registroBbdd = consultaRegistro($select))
    {
        $courseName = $registroBbdd['co_name'];
    
        echo '<div class="contentColumn80">
                    <span class="tableContentText ">'.$courseName.' - </span>';
    
        for($i = 1; $i <= 12; $i++)
        {
            if($registroBbdd['co_subj'.$i] != null) {
                echo '<span>CURSO: ' . $registroBbdd['co_subj'.$i] . ' - </span>';
            }
        }
        echo '</div>';
    }
    
    点赞 评论 复制链接分享