SELECT multiple values from a single column in Wordpress Database

southafricanrob Published at Dev

southafricanrob

I am trying to output the post details, as well as some custom fields in wordpress's database. I have custom fields called 'thumbnail' and 'video'

SELECT ID, post_title, post_content, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key = "thumnail"
OR wp_postmeta.meta_key = "video"

So far I am getting individual rows like this:

| ID  | Title     | Content     | Custom Fields                      |
|--------------------------------------------------------------------|
| 234 | INCEPTION | Content etc | wp-content/thumbnail/inception.jpg |
| 234 | INCEPTION | Content etc | wp-content/video/inception.flv     |

but what i would like is (ie one line per post)

|ID |Title     |Content     |Thumbnail                  |Video                      |
|-----------------------------------------------------------------------------------|
|234|INCEPTION |Content etc |wp-content/..inception.jpg |wp-content/..inception.flv |

Could someone advise on how to separate the different values into columns within the SELECT statement?

Danijel

This process is called "pivot table" or "crosstab report". There is no PIVOT command in MySQL but it can be done manually, here are two examples. The first example uses IF statement, the second one CASE statement. And an aggregate function is used to get rid of the duplicate rows.

SELECT ID, post_title, post_content,
MAX( IF ( wp_postmeta.meta_key = "thumnail", wp_postmeta.meta_value, '' ) ) AS Thumbnail,
MAX( IF ( wp_postmeta.meta_key = "video", wp_postmeta.meta_value, '' ) ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN ( "thumnail", "video" )
GROUP BY ID

SELECT ID, post_title, post_content,
MAX( CASE WHEN wp_postmeta.meta_key = "thumnail" THEN wp_postmeta.meta_value ELSE '' END ) AS Thumbnail,
MAX( CASE WHEN wp_postmeta.meta_key = "video" THEN wp_postmeta.meta_value ELSE '' END ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN( "thumnail", "video" )
GROUP BY ID

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-10

Comments

0 comments

From Dev

Related Related

Article

SELECT multiple values from a single column in Wordpress Database

SELECT multiple values from a single column in Wordpress Database

Select values from multiple columns into single column

Select values from multiple columns into single column

Select statement with multiple rows from condition on values in single column

Select statement with multiple rows from condition on values in single column

How to SELECT multiple values from a row and join it as a single column value

Select multiple column values in single query - oracle

Select comma separated values from single column

Using read.csv.sql to select multiple values from a single column

inserting multiple checkbox values into a single column - SQL database

how to insert multiple checkbox values in single column in database using php

Is it possible to select only repeated values from a single column using eloquent?

select limited duplicate values from a specific column for multiple values

LINQ conditional select from table and return multiple column as a single list

R - Select IDs that meet multiple criteria from single column

how to select multiple values from a single level of a dataframe multiindex

select multiple column into a single row

Select multiple selected values from a column from each row

Select Number of Values in Database Column

how to run multiple website with single database in wordpress

Saving "Select Multiple" values in a database

Select rows from a DataFrame based on multiple values in a column in pandas

MySql: select values from one column into multiple columns in the result

MySQL: Select multiple max values from one column

Pivot table multiple column values in a single column

Select immediate smaller/larger values when comparing multiple columns to another single column

Multiple database row values in single variable

Select unique values from Database

Multiple values set on select2 in WordPress

Multiple tables in select statement to get single column