CODEWITHSUNDEEP
mysql
Sharmin Jahan
Sharmin Jahan

Reputation: 21

How can I retrieve multiple rows from a table and display the combined information in a single query?

I have used a dynamic form builder plugin in WordPress. When I input some data from this form data inserted in the table. The table looks like below.

id    entry_id    field_id       slug            value
1     1           fld_7213434    name            abc
2     1           fld_5474822    father          def
3     1           fld_4459896    gender          Female
4     1           fld_6364274    village_name    ijk
20    2           fld_7213434    name            J Jahan
21    2           fld_5474822    father          Md Ali
22    2           fld_4459896    gender          Female
23    2           fld_6364274    village_name    ijk

I need to show this table's data like below

S.N.   name      father   gender    village_name
1      abc       def      Female    ijk
2      J Jahan   Md Ali   Female    Adabor

How can I do that?

Upvotes: 0

Views: 105

Answers (1)

Imanuel
Imanuel

Reputation: 3667

One idea is to join the table to itself:

SELECT 
    t1.entry_id `S.N.`,
    t1.value name,
    t2. value father,
    t3.value gender,
    t4.value village_name
FROM mytable t1
LEFT JOIN mytable t2 ON t2.entry_id = t1.entry_id AND t2.field_id = 'fld_5474822'
LEFT JOIN mytable t3 ON t3.entry_id = t1.entry_id AND t3.field_id = 'fld_4459896'
LEFT JOIN mytable t4 ON t4.entry_id = t1.entry_id AND t4.field_id = 'fld_6364274'
WHERE
    t1.field_id = 'fld_7213434'

Of course, instead of mytable you have to use your correct table name that you have not mentioned in your question.

A join is the Cartesion product (Wikipedia) of all involved tables. After filtering the resulting set of rows for those with the correct combinations (in this case, entry_id and field_id must be filtered), you get exactly what you want.

For performance reasons, you might want to have an index on the columns entry_id and field_id, but you'll find out.

Upvotes: 1

Related Questions