php - 使用 JOIN 从数据库中检索结果

Question

我试图将一个函数的操作次数减少到只有一个查询，但现在我无法检索任何结果:

function retrive_avaible_operator($Hostname, $Username, $Password, $DatabaseName, $SupportUserPerDepaTable, $SupportUserTable,$dep){
    $query = "SELECT b.id  
              FROM ".$SupportUserTable." b
              INNER JOIN ".$SupportUserPerDepaTable." a
                  ON b.id=a.user_id
              WHERE a.department_id=? AND b.holiday='0' AND a.user_id!=".$_SESSION['id']." 
              ORDER BY b.assigned_tickets ASC LIMIT 1";

    $mysqli = new mysqli($Hostname, $Username, $Password, $DatabaseName);
    $stmt = $mysqli->stmt_init();
    $prepared = $stmt->prepare($query);

    if($prepared){
        if($stmt->bind_param('i', $dep)){
            if($stmt->execute()){
                $stmt->store_result();
                $result = $stmt->bind_result($camaro);
                if($stmt->num_rows>0){
                    while (mysqli_stmt_fetch($stmt))
                        $selopid=$camaro;
                    return $selopid;
                }
                else
                    return 'No Operator Available';
            }
            else
                return mysqli_stmt_error($stmt);
        }
        else
            return mysqli_stmt_error($stmt);
    }
    else
        return mysqli_stmt_error($stmt);
}

在此尝试之前，操作是(这是 2 个不同的查询):

1. 从 $SupportUserPerDepaTable 中选择所有 ID，其中 a.department_id=?
2. 从 $SupportUserTable 中选择所有 id，其中 id 在之前的结果中，holiday='0'

它有效，但现在它只返回No Operator Available

还有这些表格:

CREATE TABLE IF NOT EXISTS `razorphyn_support_user_departments` (
`id`                BIGINT(11)  UNSIGNED    NOT NULL    AUTO_INCREMENT,
`department_id`     BIGINT(11)  UNSIGNED    NOT NULL,
`department_name`   VARCHAR(70)             NOT NULL,
`user_id`           BIGINT(11)  UNSIGNED    NOT NULL,
`holiday`           ENUM('0','1')           NOT NULL    DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY(`department_name`,`user_id`),
INDEX(`department_id`,`department_name`,`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

和

CREATE TABLE IF NOT EXISTS `razorphyn_support_users` (
`id`                BIGINT(15)      UNSIGNED        NOT NULL AUTO_INCREMENT,
`name`              VARCHAR(50)                     NOT NULL,
`mail`              VARCHAR(50)                     NOT NULL,
`password`          VARCHAR(200)                    NOT NULL,
`reg_key`           VARCHAR(260)                    ,
`tmp_password`      VARCHAR(87)                     ,
`ip_address`        VARCHAR(50)                     NOT NULL,
`status`            ENUM('0','1','2','3','4')       NOT NULL    DEFAULT '3',
`holiday`           ENUM('0','1')                   NOT NULL    DEFAULT '0',
`mail_alert`        ENUM('no','yes')                NOT NULL    DEFAULT 'yes',
`assigned_tickets`  INT(5)          UNSIGNED        NOT NULL    DEFAULT 0,
`solved_tickets`    BIGINT(11)      UNSIGNED        NOT NULL    DEFAULT 0,
`number_rating`     BIGINT(6)       UNSIGNED        NOT NULL    DEFAULT 0,
`rating`            DECIMAL(4,2)    UNSIGNED        NOT NULL    DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY(`mail`),
INDEX (`name`,`mail`,`status`,`holiday`,`assigned_tickets`,`solved_tickets`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=55;

编辑
我发现(不幸的是，因为非常丑陋和困惑)以前的代码(我已经删除了所有控件，但它有效):

function retrive_avaible_operator($Hostname, $Username, $Password, $DatabaseName, $SupportUserPerDepaTable, $SupportUserTable,$dep){

$query = "SELECT `user_id`  FROM ".$SupportUserPerDepaTable." WHERE `department_id`=? AND `user_id`!=".$_SESSION['id'] ;
$mysqli = new mysqli($Hostname, $Username, $Password, $DatabaseName);
$stmt = $mysqli->stmt_init();
$prepared = $stmt->prepare($query);
    $stmt->bind_param('i', $dep)
        $stmt->execute()
            $stmt->store_result();
            $operator=array();
            $result = $stmt->bind_result($camaro);
            if($stmt->num_rows>0){
                while (mysqli_stmt_fetch($stmt))
                    $operator[]=$camaro;
                $operator=join(',',$operator);
                $query = "SELECT `id`  FROM ".$SupportUserTable." WHERE `id` IN (".$operator.") AND `holiday`='0' ORDER BY `assigned_tickets` ASC LIMIT 1" ;
                $prepared = $stmt->prepare($query);
                $prepared)
                    $stmt->execute()
                        $stmt->store_result();
                        $result = $stmt->bind_result($camaro);
                        if($stmt->num_rows>0){
                            while (mysqli_stmt_fetch($stmt))
                                $selopid=$camaro;
                            return $selopid;
                        }
                        else{
                            $query = "SELECT `id` FROM ".$SupportUserTable." WHERE `status`=2 AND `holiday`=0 AND `id`!=".$_SESSION['id']." ORDER BY `assigned_tickets` ASC, `solved_tickets` ASC LIMIT 1" ;
                            $prepared = $stmt->prepare($query);
                            $prepared
                                $stmt->execute()
                                    $stmt->store_result();
                                    $result = $stmt->bind_result($camaro);
                                    if($stmt->num_rows>0){
                                        while (mysqli_stmt_fetch($stmt))
                                            $selopid=$camaro;
                                        return $selopid;
                                    }
                                    else
                                        return 'No Operator Available';
                        }
            }
            else{
                $query = "SELECT `id` FROM ".$SupportUserTable." WHERE `status`='2' AND `holiday`='0' AND `id`!=".$_SESSION['id']." ORDER BY `assigned_tickets` ASC, `solved_tickets` ASC LIMIT 1" ;
                $prepared = $stmt->prepare($query);
                    $stmt->execute()
                        $stmt->store_result();
                        $result = $stmt->bind_result($camaro);
                        if($stmt->num_rows>0){
                            while (mysqli_stmt_fetch($stmt))
                                $selopid=$camaro;
                            return $selopid;
                        }
                        else
                            return 'No Operator Available';
            }
$mysqli->close();
}

Answer 1

您应该能够相当大程度地压缩它。一开始，您查询您的 $SupportUserPerDepaTable 只是为了获取 user_id，只是为了将它们连接成逗号分隔的字符串以用于您的第二个查询。你应该能够通过将它变成一个子查询来消除它。第三个和第四个查询是多余的，是前面 if/else 情况的产物，所以您现在可以将它们组合起来，因为我们已经消除了最上面的 if 条件。使用底部的“丑陋”代码，我将其浓缩为:

$mysqli = new mysqli($Hostname, $Username, $Password, $DatabaseName);
$stmt = $mysqli->stmt_init();
$query = "
SELECT      `id`  
FROM        ".$SupportUserTable." 
WHERE       `id` IN (
    SELECT      `user_id`  
    FROM        ".$SupportUserPerDepaTable." 
    WHERE       `department_id`= ? 
    AND         `user_id`!=".$_SESSION['id']."
)
AND         `holiday`='0'
ORDER BY    `assigned_tickets` ASC 
LIMIT       1" ;

$prepared = $stmt->prepare($query);
$stmt->bind_param('i', $dep);
$stmt->execute();
$stmt->store_result();
$operator=array();
$result = $stmt->bind_result($camaro);
if($stmt->num_rows>0){
    while (mysqli_stmt_fetch($stmt))
        $selopid=$camaro;
    return $selopid;
}
else{
    $query = "
    SELECT      `id` 
    FROM        ".$SupportUserTable." 
    WHERE       `status`= 2 
    AND         `holiday`=0 
    AND         `id`!=".$_SESSION['id']." 
    ORDER BY    `assigned_tickets` ASC
                ,`solved_tickets`  ASC 
    LIMIT       1" ;
    $prepared = $stmt->prepare($query);
    $stmt->execute();
    $stmt->store_result();
    $result = $stmt->bind_result($camaro);
    if($stmt->num_rows>0){
        while (mysqli_stmt_fetch($stmt))
            $selopid=$camaro;
        return $selopid;
    }
    else{
        return 'No Operator Available';
    }
}
$mysqli->close();

您可以使用组合限制为 1 的 UNION 将 2 个查询进一步压缩为 1 个，例如:

(
    SELECT      `id`  
    FROM        ".$SupportUserTable." 
    WHERE       `id` IN (
        SELECT      `user_id`  
        FROM        ".$SupportUserPerDepaTable." 
        WHERE       `department_id`= ? 
        AND         `user_id`!=".$_SESSION['id']."
    )
    AND         `holiday`='0'
    ORDER BY    `assigned_tickets` ASC 
    UNION
    SELECT      `id` 
        FROM        ".$SupportUserTable." 
        WHERE       `status`= 2 
        AND         `holiday`=0 
        AND         `id`!=".$_SESSION['id']." 
        ORDER BY    `assigned_tickets` ASC
                    ,`solved_tickets`  ASC 
)
LIMIT       1;

或者可能使用 OR 条件，具体取决于部门表中员工的状态代码，例如:

SELECT      `id`  
FROM        ".$SupportUserTable." 
WHERE       `holiday`='0'
AND (       
        `id`    IN (
            SELECT      `user_id`  
            FROM        ".$SupportUserPerDepaTable." 
            WHERE       `department_id`= ? 
            AND         `user_id`!=".$_SESSION['id']."
        )
        AND     `status`= ('some code other than 2?') 
    )
    OR  (
                `status`= 2 
        AND     `id`! = ".$_SESSION['id']."
)
ORDER BY    status (asc or desc?)
            ,`assigned_tickets` ASC
            ,`solved_tickets`  ASC 
LIMIT       1;

我还没有对此进行测试，因为这会花费太多时间来设置数据库表、输入示例数据并进行全面测试，所以如果有任何小错误请原谅我，但这应该会为您指明正确的方向。如果这不起作用并且您想将一些示例数据发布到 http://sqlfiddle.com/并给出几个预期输出示例我将重试一些查询以查看我能做什么。