时间戳

首页 > 解决方案 > Mysql查询条件与或在php中

php - Mysql查询条件与或在php中

问题描述

大家好，当我分配课程时，我试图获取特定课程的截止日期，所以我有 3 种类型的表，我可以在这 3 个表中为用户分配课程我有相同的用户 ID 列，所以在我的课程表中它必须满足条件但如果我只给出一个条件，它就不起作用

这是我的查询：

 $sql1="SELECT cm.id as cmid,c.id as cid,c.fullname,c.startdate,c.timecreated,c.theme,cm.instance,asg.duedate,asd.duedate,sca.duedate FROM mdl_course c LEFT JOIN mdl_course_modules cm ON c.id=cm.course LEFT JOIN mdl_ppc_assigncourses_group asg On asg.courseid=c.id LEFT JOIN mdl_ppc_assigncourses_dep asd On asd.courseid=c.id LEFT JOIN mdl_ppc_singlecourse_assign sca On sca.courseid=c.id where c.category != 0 and requested='0' AND cm.id!='' AND asg.userid='2101'  and sca.userid= '2101' and asd.userid= '2101' GROUP BY cm.id HAVING COUNT(*) > 0";

在那个查询中，如果我只保留一个这样的条件，where c.category != 0 and requested='0' AND cm.id!='' AND asg.userid='2101'它正在获取一门课程，我也有课程表单 asg.userid 所以如果我添加它它不起作用

我正在尝试获取该特定课程的截止日期，但对于第一门课程，我得到的截止日期与第二个截止日期一样正确，显示为默认日期（1970-01-01）

这是我的代码：

 <?php
            require_once($CFG->dirroot . '/mod/scorm/lib.php');
            require_once($CFG->dirroot . '/mod/scorm/locallib.php');
            require_once($CFG->dirroot . '/course/lib.php');
            global $DB;
            $id = $USER->id;

            $stmt = $DB->get_records_sql("
                         SELECT asg.asgid,asg.courseid,asg.groupid,asg.duedate FROM mdl_ppc_assigncourses_group asg 
                         JOIN mdl_ppc_group_users pgu ON asg.groupid = pgu.group_id
                         WHERE pgu.userid = '$id' AND asg.status='1'and  pgu.status='1'
                          UNION  SELECT asd.acdid,asd.courseid,asd.depid,asd.duedate FROM mdl_ppc_assigncourses_dep asd 
                         JOIN mdl_ppc_dep_user pdu ON asd.depid = pdu.department 
                         WHERE pdu.userid = '$id' AND asd.status='1' and pdu.status='1'
                         UNION SELECT mpsa.scid,mpsa.courseid,mpsa.depid,mpsa.duedate FROM mdl_ppc_singlecourse_assign mpsa LEFT JOIN mdl_course c ON mpsa.courseid=c.id where mpsa.userid = '$id' and mpsa.status='1'");


            $cids = '';
            foreach ($stmt as $value) {
                $cids[] = $value->courseid;

            }
            //print_r($cids);

           $sql1="SELECT cm.id as cmid,c.id as cid,c.fullname,c.startdate ,c.timecreated ,c.theme ,cm.instance,asg.duedate ,asd.duedate ,sca.duedate 
    FROM mdl_course c 
    LEFT JOIN mdl_course_modules cm ON c.id=cm.course  AND cm.id!=''
    LEFT JOIN mdl_ppc_assigncourses_group asg On asg.courseid=c.id  AND asg.userid='2101'
    LEFT JOIN mdl_ppc_assigncourses_dep asd On asd.courseid=c.id and asd.userid= '2101' 
    LEFT JOIN mdl_ppc_singlecourse_assign sca On sca.courseid=c.id  and sca.userid= '2101'
    where c.category != 0 and requested='0' 
    GROUP BY cm.id 
    HAVING COUNT(*) > 0";
            if ($cids != '') {
                $cids = implode(',', $cids);
                $sql1 .= " and cid in ($cids)";
            }

            $courses = $DB->get_records_sql($sql1);

            if (sizeof($courses) && $cids != '') {
                foreach ($courses as $row) {
                          echo $row->duedate;

                }
            }

任何人都可以帮助我如何解决它

提前致谢。

标签： phpmysql

解决方案

如果您使用 where 子句过滤左连接表列，这将作为内部连接..

您应该将这些条件移到相关的 left join on 子句中 

$sql1="SELECT cm.id as cmid
        ,c.id as cid
        ,c.fullname
        ,c.startdate
        ,c.timecreated
        ,c.theme
        ,cm.instance
        ,asg.duedate
        ,asd.duedate
        ,sca.duedate 
        FROM mdl_course c 
        LEFT JOIN mdl_course_modules cm ON c.id=cm.course  AND cm.id!=''
        LEFT JOIN mdl_ppc_assigncourses_group asg On asg.courseid=c.id  AND asg.userid='2101'
        LEFT JOIN mdl_ppc_assigncourses_dep asd On asd.courseid=c.id and asd.userid= '2101' 
        LEFT JOIN mdl_ppc_singlecourse_assign sca On sca.courseid=c.id  and sca.userid= '2101
        where c.category != 0 and requested='0' ' 
        GROUP BY cm.id 
        HAVING COUNT(*) > 0";

推荐阅读