LOOP将数据从多个字段复制到一个字段 [英] LOOP to copy data from many fields to one

问题描述

您好

我正在寻求帮助来传输数据。



我想复制所有私密存储的链接将字段放入帖子内容并将其放在帖子内容之后。



问题是这些帖子有不同数量的字段和链接。几个或几个。



表格如下（主栏）

Hello
I am asking for help in transferring the data.

I would like to copy all links that are stored in private fields into the post content and put them after the post content.

The problem is that the posts have different numbers of fields with links. Few or several.

Tables look like this (main columns)

table posts
ID	POST_CONTENT
1	example one.
2	ipsum.
3	something else.

第二个是

The second is

table postmeta
meta_id	post_id	meta_key	meta_value
420	1	wpcf-obrazek	image http://url_1.pl/
440	1	wpcf-obrazek	image http://url_2.pl/
601	2	wpcf-obrazek	image http://url_3.pl/
710	2	wpcf-obrazek	image http://url_4.pl/
210	3	wpcf-obrazek	image http://url_5.pl/
555	3	wpcf-obrazek	image http://url_6.pl/
648	3	wpcf-obrazek	image http://url_7.pl/
700	3	wpcf-obrazek	image http://url_8.pl/
<pre>
Links are only when the meta_key=wpcf-obrazek
I would like to get:

<pre>
table posts
ID	POST_CONTENT
1	example one.CHAR(10)http://url_1.pl/CHAR(10)http://url_2.pl/
2	ipsum.CHAR(10)http://url_3.pl/CHAR(10)http://url_4.pl/
3	something else.CHAR(10)http://url_5.pl/CHAR(10)http://url_6.pl/CHAR(10)http://url_7.pl/CHAR(10)http://url_8.com/

是否可以通过循环或其他简单的方式进行？



我尝试过：



我用了一个简单的：

SET posts.post_content = postmeta.meta_value

WHERE meta_key = ....

AND postmeta.post_id = POSTS.ID



但只有当neta_key每个帖子出现一次时才有效

Is it possible to do it with a loop or other simple way?

What I have tried:

I used a simple:
SET posts.post_content = postmeta.meta_value
WHERE meta_key = ....
AND postmeta.post_id = POSTS.ID

but it works only when the neta_key appear once per post

推荐答案

你绝对不应该使用循环。 MySQL是基于组的，大多数数据库管理系统也是如此。



你要做的是连接 postmeta 基于post_id的表



如果查看简单查询

You absolutely should not use a loop. MySQL is group based, as are most database management systems.

What you are trying to do is concatenate values from the postmeta table based on the post_id

If you look at the simple query

select A.ID, A.POST_CONTENT, B.meta_value
FROM @posts A
INNER JOIN @postmeta B ON A.ID=B.post_id

您可以获取值

1	example one.	image http://url_1.pl/
1	example one.	image http://url_2.pl/
2	ipsum.	image http://url_3.pl/
2	ipsum.	image http://url_4.pl/
3	something else.	image http://url_5.pl/
3	something else.	image http://url_6.pl/
3	something else.	image http://url_7.pl/
3	something else.	image http://url_8.pl/

然后您可以使用 MySQL GROUP_CONCAT功能 [< a href =http://www.mysqltutorial.org/mysql-group_concat/target =_ blanktitle =新窗口> ^ ]创建的列表 meta_value s

我认为这看起来像这样（未经测试）

You can then use MySQL GROUP_CONCAT Function[^] to create the "list" of meta_values
Which I think would look something like this (untested)

SELECT A.ID, A.POST_CONTENT, 
    GROUP_CONCAT(B.meta_value ORDER BY B.meta_id SEPARATOR ' ')
    FROM @posts A
    INNER JOIN @postmeta B ON A.ID=B.post_id

I我不知道你从哪里得到CHAR（10）位，但这应该足以让你入门

I've no idea where you are getting the CHAR(10) bit from, but this should be enough to get you started

这篇关于LOOP将数据从多个字段复制到一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！