I want to do a select from a wordpress DB

Question

the db is structured as follows:

id | meta-key | meta-value

1  | client   | John

1  | bday     | today

i want it to be

id | client | bday

1  | John   | today

there are many lines like client and bday and i want to transform some of them into columns either when displaying or inserting into another table

i tried something like this:

SELECT p1.meta_value, wph0_posts.post_title, p2.meta_value as client, p3.meta_value as end_date, p4.meta_value as description
FROM wph0_postmeta p1
INNER JOIN wph0_posts ON p1.post_id = wph0_posts.ID
INNER JOIN wph0_postmeta p2 ON
p1.post_id = p2.post_id AND
p2.meta_key = 'client'
INNER JOIN wph0_postmeta p3 ON
p1.post_id = p3.post_id AND
p3.meta_key = 'end_date'
INNER JOIN wph0_postmeta p4 ON
p1.post_id = p4.post_id AND
p4.meta_key = 'description';

can i get some tips? pretty new to db'es

score 0 · Answer 1 · answered Jun 26 '20 at 23:24

CREATE TABLE wph0_postmeta  (
  `id` INTEGER,
  `meta-key` VARCHAR(20),
  `meta-value` VARCHAR(20)
);

INSERT INTO wph0_postmeta 
  (`id`, `meta-key`, `meta-value`)
VALUES
  ('1', 'client', 'John'),
  ('1', 'bday', 'today'),
  ('1', 'descriptiuon', 'test'),
    ('2', 'client', 'Jerry'),
  ('2', 'bday', 'tomorrow'),
  ('2', 'descriptiuon', 'secondtest');

INSERT INTO wph0_postmeta 
  (`id`, `meta-key`, `meta-value`)
VALUES
  ('1', 'client', 'John'),
  ('1', 'bday', 'today'),
  ('1', 'descriptiuon', 'test'),
    ('2', 'client', 'Jerry'),
  ('2', 'bday', 'tomorrow'),
  ('2', 'descriptiuon', 'secondtest');

CREATE TABLE wph0_posts ( id INT, post_title varchar(20));
INSERT INTO wph0_posts VALUES (1,'Dr.Dr.'),(2,'Prof.Dr.Dr.')

SELECT t1.id,
t2.post_title
,MAX(IF(t1.`meta-key` = 'client',   t1.`meta-value`,NULL)) 'client'
,MAX(IF(t1.`meta-key` = 'bday',   t1.`meta-value`,NULL)) 'birthday'
,MAX(IF(t1.`meta-key` = 'descriptiuon',   t1.`meta-value`,NULL)) 'descriptiuon'
FROM wph0_postmeta t1 INNER JOIN wph0_posts t2 ON t1.id = t2.id
GROUP BY t1.id,t2.post_title

id | post_title  | client | birthday | descriptiuon
-: | :---------- | :----- | :------- | :-----------
 1 | Dr.Dr.      | John   | today    | test        
 2 | Prof.Dr.Dr. | Jerry  | tomorrow | secondtest

db<>fiddle here

This works for a fixed set of value perfect. but of you have much more and deffrent you must look at MySQL query to create a pivot table by joining 4 different table

score 0 · Answer 2 · edited Jun 27 '20 at 00:44

Thx for the help, the following worked:

SELECT wph0_postmeta.post_id, 
GROUP_CONCAT( if(wph0_postmeta.meta_key='client',wph0_postmeta.meta_value,NULL) ) AS Nume, 
GROUP_CONCAT( if(wph0_postmeta.meta_key='end_date',wph0_postmeta.meta_value,NULL) ) AS Descriere 
FROM wph0_postmeta GROUP BY wph0_postmeta.post_id

i only extracted the name and some stuff from wph0_postmeta and used the if statement . I can now add another group_concat and add another column from a meta_key, i got the link for pivots here:

Need Table Values as Column Titles

I want to do a select from a wordpress DB

2 Answers2