MySQL选择所有并根据另一列更新特定列字段 [英] MySQL select all and update specific column fields based on another column

问题描述

我正在尝试从表中选择所有行，并更新同一表中的列值(基本上是标题列的自动 slug/永久链接重写).但是，当我这样做时，它只是从所有行的第一个 ID 插入相同的slug/永久链接"重写，而不是分别重写和插入每一行的标题.

I am trying to select all rows from a table, and update a column's values within the same table (basically an automatic slug/permalink rewrite of a title column). However, when I do that, it just inserts the same "slug/permalink" rewrite from the very first ID in all of the rows, instead of rewriting and inserting the title for each row individually.

<?php
$sql = "SELECT * FROM titles"; 
$query = mysqli_query($con, $sql) or die (mysqli_error());
while ($row = mysqli_fetch_array($query)) { 
    $id = $row["id"];
    $title = $row["title"];

    // title rewrite function goes here
    $permalink = preg_replace ..... etc

    //add new permalink rewrite to slug column
    $sql2 = "UPDATE titles SET permalink='$permalink' WHERE id='$id'"; 
    $query2 = mysqli_query($con, $sql2) or die (mysqli_error());
} 
?>

目前，结果如下:

ID   Title              Permalink
1    This is Title 1    this-is-title-1
2    This is Title 2    this-is-title-1
3    This is Title 3    this-is-title-1

我也试过没有 WHERE id='$id' 部分，但这并没有改变任何东西......

I have also tried it without the WHERE id='$id' part, but that does not change anything ...

非常感谢任何建议！谢谢

Any suggestions much appreciated ! Thanks

推荐答案

您确定您的 $permalink 分配正确吗?根据您想要的输出，我为永久链接创建了一个片段并测试了代码(还更正了 mysqli_error 函数..)

Are you sure, your $permalink assignment is correct? Based on your wanted output, I created a snippet for the permalink and tested the code(also corrected the mysqli_error function..)

$sql = "SELECT * FROM titles";
$query = mysqli_query($con, $sql) or die (mysqli_error($con));
while ($row = mysqli_fetch_array($query)) {
    $id = $row["id"];
    $title = $row["title"];

    // title rewrite function goes here
    //$permalink = preg_replace ..... etc
    $permalink = preg_replace('~\s+~', '-', strtolower($title));

    //add new permalink rewrite to slug column
    $sql2 = "UPDATE titles SET permalink='$permalink' WHERE id='$id'";
    $query2 = mysqli_query($con, $sql2) or die (mysqli_error($con));
}

这段代码对我来说很好用.如果这不能创建正确的输出，则您的数据库表数据可能有问题

This code works fine for me. If this doesn't create the correct output, something might be wrong with your database table data

顺便说一句.您可以使用 https://stackoverflow.com/a/8419128/1043150 中的 sql 程序并分解整个代码到单个 sql 语句

Btw. you could use the sql procedure from https://stackoverflow.com/a/8419128/1043150 and break the wohle code down to a single sql statement

UPDATE titles SET permalink = slugify(title)

这篇关于MySQL选择所有并根据另一列更新特定列字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！