Mysql中的排序机制

Question

我在PHP和Mysql项目中有一个问题。

简单地说，我有两个表项目和项目特性，

每个项目都有特定的功能。

CREATE TABLE projects (
   ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   name varchar(255) NOT NULL
);


CREATE TABLE projects_features (
   projectId INT NOT NULL,
   name varchar(255) NOT NULL,
   value varchar(255) NOT NULL,
   weight INT NOT NULL
);

INSERT INTO projects VALUES (NULL,'project1');

INSERT INTO projects VALUES (NULL,'project2');

INSERT INTO projects_features VALUES (1,'Feature1','Feature1 Value',1);

INSERT INTO projects_features VALUES (2,'Feature2','Feature2 Value',2);

INSERT INTO projects_features VALUES (1,'Feature3','Feature3 Value',3);

INSERT INTO projects_features VALUES (2,'Feature4','Feature4 Value',4);

INSERT INTO projects_features VALUES (1,'Feature5','Feature5 Value',5);

我通过以下方式获得项目功能：

SELECT * FROM projects_features WHERE projectId = 1 ORDER BY weight ASC;

因此，较大的重量将下降，较低的重量将上升。

现在，在我的视图中，我有了move up和move down按钮，因此我可以对项目特性进行重新排序。

我可以先选择当前项目权重，然后再选择较高的项目权重，

然后键入两个update查询以在两行之间交换权重，

但这不是一种专业的方式，我不喜欢使用四个查询。

我需要在一个查询中完成，而不是在四个查询中完成。

有谁能帮帮忙吗？

Answer 1

假设我已经理解了这个问题，下面是我如何解决这个问题的。

首先，我将向projects_features添加一个featureId列，并将(projectId, featureId)设置为复合主键。这对于我的解决方案实际上并不是必需的；它只是让剩下的事情变得简单得多。在下一部分中，您需要能够引用projects_features中的各个记录。

接下来，我将让Move按钮填充PHP中的数组，如

$update_list = array();
$update_list[i] = array(':project_id' => $proj_id,
                        ':feature_id' => $feat_id,
                        ':new_weight' => $weight);

最后，我将通过封装UPDATE语句的PHP函数进行更新，如下所示：

function updateWeights($update_array, $dbh)
{
    $sql = 'UPDATE project_features
              SET weight = :new_weight
              WHERE projectId = :project_id
                AND featureId = :feature_id'
    $stmt = $dbh->prepare($sql);
    foreach ($update_array as $update_item)
    {
        $stmt->execute($update_item);
    }
}

注意，我在这里使用的是PDO；它也可以使用mysqli完成，尽管mysqli不支持命名绑定参数，因此语法会略有不同，如下所示

function updateWeights($update_array, $dbh)
{
    $sql = 'UPDATE project_features
              SET weight = ?
              WHERE projectId = ?
                AND featureId = ?'
    $stmt = $dbh->prepare($sql);
    foreach ($update_array as $update_item)
    {
        $stmt->bind_param('i', $update_item[':new_weight']);
        $stmt->bind_param('i', $update_item[':project_id']);
        $stmt->bind_param('i', $update_item[':feature_id']);
        $stmt->execute();
    }
}

还要注意，我还没有实际运行过这段代码，所以没有表示它没有语法或其他错误。

我希望这能有所帮助。