CODEWITHSUNDEEP
mysql
user1464025
user1464025

Reputation: 11

Display profile content row wise

i have 2 table one is field_id and other is field_value. i want to display profile content in row wise, first name, last name, age,hobby,about me.

field_id
-------------------------------------------------
   id   |           name           |
--------|--------------------------|-----------------
1                   First name
2                   Last name
3                   Age
4                   Hobby
5                   About me
----------------------------------------------------------

field_value --here id pmkey auto increment and field_id is mentioned in up table

------------------------------------------------------------------
id | user_id  | field_id |   field_value                       |
---|----------|----------|--------------------------------------|----
1  | 1        |     1    |John       
2  | 1        |     1    |smith
3  | 1        |     2    |Capili
4  | 1        |     3    |32
5  | 1        |     4    |Reading Book
6  | 1        |     4    |Swimming
7  | 1        |     4    |Boating
8  | 1        |     5    |I
9  | 1        |     5    |am
10 | 1        |     5    |very
11 | 1        |     5    |simple
11 | 1        |     5    |person
-----------------------------------------------------------

I want to display profile everry content in row wise like this

name -:john smith capili
Age-:32
Hobby-:Reading Book,Swimming,Boating
About me-:I am very simple person.

Please help me. and suggest me this type database we should use or not.

Upvotes: 1

Views: 283

Answers (1)

Fahim Parkar
Fahim Parkar

Reputation: 31647

At the least what you can get is as below.

John smith
Capili
32
Boating Swimming Reading Book
I am very simple person

To get above output use below query.

SELECT GROUP_CONCAT(field_value SEPARATOR ' ') as Profile FROM myTable
WHERE user_id = 1
GROUP BY field_id

Demo.

However, I would suggest you to change your DB Structure and make it as below.

Table personal_details, with following fields.

+++++++++++++++++++++++
+ field     +  type   +
+++++++++++++++++++++++
+ personID  + INT     +
+ firstName + varchar +
+ middleName+ varchar +
+ lastName  + varchar +
+ age       + INT     +
+ aboutMe   + varchar +
+++++++++++++++++++++++

hobbies_person table

+++++++++++++++++++++++
+ field     +  type   +
+++++++++++++++++++++++
+ personID  + INT     +
+ hobbID    + INT     +
+++++++++++++++++++++++

hobbies table

+++++++++++++++++++++++
+ field     +  type   +
+++++++++++++++++++++++
+ hobbID    + INT     +
+ hobbName  + varchar +
+++++++++++++++++++++++

I have added new table as hobbies because if we don't have hobbies table then for each person we have to write hobbies name (extra field too & increase in size too). In hobbies_person we will already have hobbies defined and person write new hobby then it would be first added into hobbies table and then hobbies_person table.

See, New Database Structure for more details.

Upvotes: 3

Related Questions