使用PHP脚本将数据库列的内容复制到另一列

Question

I have a site that holds many posts in the database. Currently, my table structure looks like this:

|-------|-------|-------------------|
|   id  |   url |   title           |
|-------|-------|-------------------|
|   1   |       |   Hello World     |
|   2   |       |   Hello World's   |
|   3   |       |   Hello & World   |
|-------|-------|-------------------|

In the table above looks empty url column. Yes, that column I just added, so there is nothing there. While in the title column, there are different post titles.

I have a problem with the title field, because there are some unsupported characters for the URL structure. For example http://google.com/?url=Hello World's So does the string & does not work there.

I plan to add a new column to serve as the url address. So like this my new table will be:

|-------|-------------------|-------------------|
|   id  |       url         |   title           |
|-------|-------------------|-------------------|
|   1   |   hello_world     |   Hello World     |
|   2   |   hello_worlds    |   Hello World's   |
|   3   |   hello_and_world |   Hello & World   |
|-------|-------------------|-------------------|

The addition of this url can be done manually via PHPMyAdmin, but my list of posts there are thousands of them. It is impossible to do it one by one. Is there an instant way to duplicate all data in the column title to the url column using PHP script and change (spaces) to (_), remove quotes, and change the sign (&) to (and)?

Please do not give me advice to use ID as url address ?url=1, ?url=2, ?url=3, because I want to use title as my post address. thanks.

Answer 1

You can do this with a bunch of replaces. You can do:

update t
    set url = replace(replace(replace(title, ' ', '_'), '&', 'and'), '''', '');