LOOP to copy data from many fields to one

Question

0.00/5 (No votes)

See more:

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/

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

Posted 11-May-18 6:27am

Member 13818387

Updated 13-May-18 23:27pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-05-13T23:28:00

Solution 1

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

SQL

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

you can get the values

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/

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

SQL

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've no idea where you are getting the CHAR(10) bit from, but this should be enough to get you started

Posted 13-May-18 23:28pm

CHill60

Comments

Member 13818387 14-May-18 12:56pm

I almost have what I need, thank you.
Please, help me change Select in Update
This query returns the values I need.

SELECT CONCAT( P.POST_CONTENT, GROUP_CONCAT(M.meta_value ORDER BY M.meta_id SEPARATOR 'CHAR(10)'))
    FROM posts P
    INNER JOIN postmeta M ON P.ID=M.post_id
    WHERE M.meta_key = 'wpcf-obrazek'
    GROUP BY P.ID

But how to do Update from that?
I wrote

UPDATE P, M
SET P.post_excerpt = CONCAT( P.POST_CONTENT, GROUP_CONCAT(M.meta_value ORDER BY M.meta_id SEPARATOR 'CHAR(10)'))
    FROM posts P
    INNER JOIN postmeta M ON P.ID=M.post_id
    WHERE M.meta_key = 'wpcf-obrazek'
    GROUP BY P.ID

\
but I have an error

CHill60 15-May-18 3:49am

I can't access Oracle at the moment but what is the error? I think it might be the single quotes around 'CHAR(10)' and having both aliases in the UPDATE ... try

UPDATE P
SET P.post_excerpt = CONCAT( P.POST_CONTENT, GROUP_CONCAT(M.meta_value ORDER BY M.meta_id SEPARATOR CHAR(10)))

Member 13818387 15-May-18 12:33pm

Select doesn't work when I deleted quotes on CHAR(10)

When I run my Update Statement I've got

#1064 - Something is wrong in your syntax obok 'FROM posts P
    INNER JOIN postmeta M ON P.ID=M.post_id
    WHERE M.meta_key ' w linii 3

Without quotes on CHARR(10) error is:

#1064 - Something is wrong in your syntax obok 'CHAR(10)))
    FROM posts P
    INNER JOIN postmeta M ON P.ID=M.post_id
    W' w linii 2

CHill60 15-May-18 15:10pm

There is an extra concat in there, check the syntax in the documentation. I'm on my phone so it's difficult for me to see