How to generate array from multiple mysql rows with a common value

Question

I have a mysql database with the following structure.

id|form_id|element_label|element_value|group_id|date|ip

This is generated from a form plugin in joomla so I have no control over its structure.

The form id is the form the value is related to, element_label is the input that it relates to, element_value is the value that the user enetered and group_id is the id of the new entry.

The group_id is a common value in all rows related to the users input.

Of an example when a user fills out a simple form for contact it is populated in the database like this:

1|15|1|Some Persons Name|12|12-11-2014|10.0.0.3
2|15|2|[email protected]|12|12-11-2014|10.0.0.3
3|15|3|The users message|12|12-11-2014|10.0.0.3

It is important to note that the form_id is common between all rows and group_id is only assigned to a single users form submission.

I want to now pull this data out from MySQL and place it in a standard HTML table with standard PHP.

I want is to look like.

group_id|Some Persons Name|[email protected]|The users message|date

I have tried to use GROUP BY in the query but it didn't work.

Please can some one advise on how I could approach a solution to this issue.

Thanks in advance.

data source sample

Bhavya Shaktawat · Accepted Answer · 2014-11-29 19:19:21Z

1

Try this one

SELECT `group_id`, group_concat(`element_value`) as value, date FROM `table_name` group by `form_id`

answered Nov 29, 2014 at 19:19

Bhavya Shaktawat

2,5021 gold badge15 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dogy Vogey Over a year ago

Thanks for you'r answer, please check the question again I don't know if you sore the image under the link "data source sample" I need every value in element_value. This SQL query gives me all the element_value in one line.😐

Bhavya Shaktawat Over a year ago

mysql can not give result in array format. It is upto the processing code. so you need to handle this in your code like if you are using php then you can generate array by using function explode(',', $value);

TheLittleHawk · Accepted Answer · 2014-11-29 18:11:16Z

0

Maybe you can try something like this:

SELECT
    IFNULL((SELECT t2.group_id FROM test t2 WHERE t1.group_id = t2.group_id And t2.form_id = t1.form_id), 0),
    -- Some Persons Name
    IFNULL((SELECT t2.element_value FROM test t2 WHERE t1.group_id = t2.group_id And t2.form_id = t1.form_id And t2.element_label = 1), ''),
    -- [email protected]
    IFNULL((SELECT t2.element_value FROM test t2 WHERE t1.group_id = t2.group_id And t2.form_id = t1.form_id And t2.element_label = 2), ''),
    -- The users message
    IFNULL((SELECT t2.element_value FROM test t2 WHERE t1.group_id = t2.group_id And t2.form_id = t1.form_id And t2.element_label = 3), ''),
    IFNULL((SELECT t2.date FROM test t2 WHERE t1.group_id = t2.group_id And t2.form_id = t1.form_id), 0)
FROM (Select * From test Group By group_id) t1

edited Nov 29, 2014 at 18:11

answered Nov 29, 2014 at 17:48

TheLittleHawk

6384 silver badges22 bronze badges

4 Comments

Dogy Vogey Over a year ago

Will this work if everything is in 1 table on its own?

TheLittleHawk Over a year ago

I edited code, From test would not work, so I filtered them by group_id. As long as each form entry has separate group_id this will work. However, if group_id is not unique, than you will need to change it to the field that is unique for each form submission.

Dogy Vogey Over a year ago

Thanks for your reply but it didn't seem to produce the right result. I added a link to a image of the data source I am dealing with and the output I'm looking for.

TheLittleHawk Over a year ago

I can see that you have more than 3 elements form elements. Why don't you just add other columns to the query above? Maybe I'm missing something :)

Collectives™ on Stack Overflow

How to generate array from multiple mysql rows with a common value

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related