gpt4 book ai didi

php - 当php mysql中删除类别及其子类别时,如何更新帖子类别关系?

转载 作者:行者123 更新时间:2023-11-29 23:58:24 25 4
gpt4 key购买 nike

在我的博客系统中,我有多对多的关系。我有三张 table 。 tb_categories、tb_posts 和 tb_post_cat_relationships。这些在下面给出 -

tb_类别-

enter image description here

tb_posts -

enter image description here

tb_post_cat_relationships -

enter image description here

你可以了解这三个表之间的关系。现在,当一个类别被删除时会发生什么,然后属于该类别的帖子将被分配给“未分类”(其 id 为 -1 并且不存在于 tb_categories 表中)。如果删除父类别,则其所有子类别也会被删除,但属于父类别和子类别的所有帖子都将与未分类相关联,即 -1 id。

对我来说,问题就开始了。如果不同的帖子与这些父类别和子类别相关联,那么可以 - 我将所有帖子更新为与这些类别 ID 相对应的 -1。但是,如果单个帖子与多个类别及其子类别相关联,则会创建大量重复行,因为 id 假设为 1 的单个常见帖子可能与类别 1 及其子类别 2、3、4 等相关联。因此,将有是用重复数据创建的 4 行,例如

enter image description here

因为我删除了父类别 Web 开发,所以它的子类别 PHP 也被删除,因此属于这两个类别的帖子现在分配给 -1 未分类类别。

这就是问题所在。我只想要一个帖子的唯一行。由于现在发布共享多个类别并分配给未分类-1,所以应该只有一行像这样 -

enter image description here

我该如何解决这个问题?我的 PHP 代码如下 -

当我删除类别时,此代码被调用 -

    $where = $_GET['cat_id'];
$status = $db_obj->update_post_cat_relationship($where)->delete_category($where);

这是更新表格并删除类别的代码 -

// update post category relationship
public function update_post_cat_relationship($where)
{
if(is_array($where) && !empty($where))
{
$cat_id = implode(", ", $where);

// first update all the sub-categories
$query = "SELECT `category_id` FROM `tb_categories` WHERE `category_parent` IN (" . $cat_id . ")";
$rs = mysqli_query($this->con, $query);
if(mysqli_num_rows($rs) > 0)
{
while($row = mysqli_fetch_array($rs))
{
$this->update_post_cat_relationship($row['category_id']);
}
}

// then update the parent category
$query = "UPDATE `tb_post_cat_relationships` SET `cat_id` = -1 WHERE `cat_id` IN (" . $cat_id . ")";
$rs = mysqli_query($this->con, $query);
//$affected_rows = mysqli_affected_rows($this->con);
if($rs)
{
return $this;
}
else
{
return false;
}
}
elseif(!is_array($where) && !empty($where))
{
// first update all the sub-categories
$query = "SELECT `category_id` FROM `tb_categories` WHERE `category_parent` = " . $where;
$rs = mysqli_query($this->con, $query);
if(mysqli_num_rows($rs) > 0)
{
while($row = mysqli_fetch_array($rs))
{
$this->update_post_cat_relationship($row['category_id']);
}
}

// then update the parent category
$query = "UPDATE `tb_post_cat_relationships` SET `cat_id` = -1 WHERE `cat_id` = " . $where;
$rs = mysqli_query($this->con, $query);
//$affected_rows = mysqli_affected_rows($this->con);
if($rs)
{
return $this;
}
else
{
return false;
}
}
}

// delete category and subcategories
public function delete_category($where)
{
if(is_array($where) && !empty($where))
{
$cat_id = implode(", ", $where);

// first delete all the sub-categories
$query = "SELECT `category_id` FROM `tb_categories` WHERE `category_parent` IN (" . $cat_id . ")";
$rs = mysqli_query($this->con, $query);
if(mysqli_num_rows($rs) > 0)
{
while($row = mysqli_fetch_array($rs))
{
$this->delete_category($row['category_id']);
}
}

// then delete the parent category
$query = "DELETE FROM `tb_categories` WHERE `category_id` IN (" . $cat_id . ")";
mysqli_query($this->con, $query);

$affected_rows = mysqli_affected_rows($this->con);
if($affected_rows)
{
return true;
}
else
{
return false;
}
}
elseif(!is_array($where) && !empty($where))
{
// first delete all the sub-categories
$query = "SELECT `category_id` FROM `tb_categories` WHERE `category_parent` = " . $where;
$rs = mysqli_query($this->con, $query);
if(mysqli_num_rows($rs) > 0)
{
while($row = mysqli_fetch_array($rs))
{
$this->delete_category($row['category_id']);
}
}

// then delete the parent category
$query = "DELETE FROM `tb_categories` WHERE `category_id` = " . $where;
mysqli_query($this->con, $query);

$affected_rows = mysqli_affected_rows($this->con);
if($affected_rows)
{
return true;
}
else
{
return false;
}
}
}

最佳答案

您可以使用foreign keys级联删除。删除时将删除关系或设置 NULL 值。

关于php - 当php mysql中删除类别及其子类别时,如何更新帖子类别关系?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25202940/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com