0

My problem :

I have a mysql table which currently stores values from a form which allows organizations to request for a demo from another organization . The form field values are : firstname , lastname,email_id,phone.

I am storing these values in key / value pairs in a mysql table in the following way :

id(int)  form_name(varchar) request_id(varchar) key(varchar)  value(varchar)   
    1        demo                 1479568061        firstname       john
    2        demo                 1479568061        lastname        Deere
    3        demo                 1479568061        email_id   jd@gmail.com       
    4        demo                 1479568061        phone       123-123-1234 
    5        demo                 1479568301        firstname       samuel
    6        demo                 1479568301        lastname        jones
    7        demo                 1479568301        email_id    sj@gmail.com
    8        demo                 1479568301        phone      134-145-1456

The id column is the primary key .

I have stored (php)timestamp values in request_id column . Each set of values (per form submission) has a unique timestamp value . For e.g records with id from 1 till 4 will have the same request_id .

I would like to query my table in such a way so that the administrator can view each set of records pertaining to a single form submission .
For instance the administrator should be able to view the records in the following way 

firstname     lastname            email id                tel no 
john          deere               jd@gmail.com            123-123-1234

My research :

MySQL fetching a key value pair which are in two different columns

mySQL query key value pairs

Need a MySQL query for selecting from a table storing key value pairs

Order query results by two key/value pairs, where the fields for the key/value pairs are the same

Any help will be appreciated .

Community
  • 1
  • 1
aidensage
  • 23
  • 1
  • 6
  • That's not a very good way to use a SQL database. It eliminates most of its advantages. [MySQL 5.7 now has the JSON type](https://dev.mysql.com/doc/refman/5.7/en/json.html) which replaces the need for key/value tables like that. Instead, store and search a single JSON hash. Anyhow, what you're looking for is a SQL pivot, but you should really redesign that table. – Schwern Nov 19 '16 at 17:52
  • @schwern..thks..have gone through the article on pivot tables at http://www.artfulsoftware.com/infotree/qrytip.php?id=78 ....will speak to my project lead on redesigning the table..however group_concat is throwing error ..need your help in creating the query for my problem above using group_concat.. – aidensage Nov 20 '16 at 15:58

0 Answers0