Use SQL to output table differently

Question

I am not sure whether this is possible or not, but I would really like to know if SQL can solve this issue, or I should continue using PHP to handle it.

I have a table that contains information from a form. The setup is made so that the submission column identifies the form entry, the field column represents an input field's name attribute and then data is the posted information.

It looks like this:

+---------------------------------------------------------------+
|id   |submission|ref       |field       |data                  |
+---------------------------------------------------------------+
|1    |1         |hox23     |name        |John Doe              |
+---------------------------------------------------------------+
|2    |1         |hox23     |address     |Sesame Street 12      |
+---------------------------------------------------------------+
|3    |1         |hox23     |phone       |5555-1234             |
+---------------------------------------------------------------+
|4    |1         |hox23     |email       |[email protected]           |
+---------------------------------------------------------------+
|5    |2         |hox23     |name        |Josh Smith            |
+---------------------------------------------------------------+
|6    |2         |hox23     |address     |Any Street 34         |
+---------------------------------------------------------------+
|7    |2         |hox23     |phone       |5555-5678             |
+---------------------------------------------------------------+
|8    |2         |hox23     |email       |[email protected]         |
+---------------------------------------------------------------+
|9    |3         |hox23     |name        |Jane Summer           |
+---------------------------------------------------------------+
|10   |3         |hox23     |address     |Last Street 4         |
+---------------------------------------------------------------+
|11   |3         |hox23     |phone       |5555-9012             |
+---------------------------------------------------------------+
|12   |3         |hox23     |email       |[email protected]        |
+---------------------------------------------------------------+
|13   |4         |hox23     |name        |Patrick Thom          |
+---------------------------------------------------------------+
|14   |4         |hox23     |website     |www.thom.ex           |
+---------------------------------------------------------------+
|15   |4         |hox23     |phone       |555-1235              |
+---------------------------------------------------------------+
|16   |4         |hox23     |email       |[email protected]       |
+---------------------------------------------------------------+
|17   |5         |hox23     |name        |Hillary Good          |
+---------------------------------------------------------------+
|18   |5         |hox23     |website     |www.good.ex           |
+---------------------------------------------------------------+
|19   |5         |hox23     |phone       |5555-8365             |
+---------------------------------------------------------------+
|20   |5         |hox23     |email       |[email protected]       |
+---------------------------------------------------------------+
|21   |6         |hox23     |name        |Toby Chalk            |
+---------------------------------------------------------------+
|22   |6         |hox23     |email       |[email protected]         |
+---------------------------------------------------------------+
|23   |6         |hox23     |website     |www.chalk.ex          |
+---------------------------------------------------------------+
|24   |7         |hox23     |name        |Kat Buo               |
+---------------------------------------------------------------+
|25   |7         |hox23     |email       |[email protected]            |
+---------------------------------------------------------------+
|26   |7         |hox23     |website     |www.buo.ex            |
+---------------------------------------------------------------+
|27   |8         |hox23     |name        |Mill Green            |
+---------------------------------------------------------------+
|28   |8         |hox23     |email       |[email protected]         |
+---------------------------------------------------------------+
|29   |8         |hox23     |website     |www.green.ex          |
+---------------------------------------------------------------+
|30   |9         |hox23     |phone       |555-6123              |
+---------------------------------------------------------------+
|31   |9         |hox23     |address     |Some other place 7    |
+---------------------------------------------------------------+
|32   |9         |hox23     |name        |Carl Stuff            |
+---------------------------------------------------------------+

As you can see, there are not the same amount of lines per entry, nor is there the same order or the same fields. Currently my PHP script fetch the table content, creates a KEY array based on the different field variables. Then I loop through all the entries again to merge them into another array to end up with a final array containing a row for each submission with data per field.

What I would like to know, is if SQL (currently I am using MySQL) can do this for me. Is there a way to make a select statement that is so dynamic that it can output a table like this:

+---------------------------------------------------------------------------------+
|submission|name        |address           |phone    |email          |website     |
+---------------------------------------------------------------------------------+
|1         |John Doe    |Sesame Street 12  |5555-1234|[email protected]    |-           |
+---------------------------------------------------------------------------------+
|2         |Josh Smith  |Any Street 34     |5555-5678|[email protected]  |-           |
+---------------------------------------------------------------------------------+
|3         |Jane Summer |Last Street 4     |5555-9012|[email protected] |-           |
+---------------------------------------------------------------------------------+
|4         |Patrick Thom|-                 |555-1235 |[email protected]|www.thom.ex |
+---------------------------------------------------------------------------------+
|5         |Hillary Good|-                 |5555-8365|[email protected]|www.good.ex |
+---------------------------------------------------------------------------------+
|6         |Toby Chalk  |-                 |-        |[email protected]  |www.chalk.ex|
+---------------------------------------------------------------------------------+
|7         |Kat Buo     |-                 |-        |[email protected]     |www.buo.ex  |
+---------------------------------------------------------------------------------+
|8         |Mill Green  |-                 |-        |[email protected]  |www.green.ex|
+---------------------------------------------------------------------------------+
|9         |Carl Stuff  |Some other place 7|555-6123 |-              |-           |
+---------------------------------------------------------------------------------+

My current select statement are looking for ref as it's identifier. I only want to fetch all the entries connected with this id. Note that the column field is not consistent, and you don't know if there is 3 or 4 lines per submission id. You don't know if the name field is the first or the last row per entry, or if the name field exists at all. I myself have found it very difficult to figure this out, but I would really like to know, if there is a way for SQL to manage this setup?

EDIT

I have at least reduced my PHP script a lot. Currently it looks like this:

<?php

$conn = new PDO(
    'mysql:host=localhost;dbname=data_loop',
    "username",
    "password",
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

// Reference variable. Can be a posted variable to look up.
$ref = 'hox23';

$sql = $conn->prepare('SELECT * FROM data_loop WHERE ref = :ref GROUP BY field');
$sql->bindParam(':ref', $ref, PDO::PARAM_STR);
$sql->execute();
$res = $sql->fetchAll(PDO::FETCH_ASSOC);

$que = 'SELECT A.submission';

$ry = 'FROM data_loop AS A ';

$i = 'B';

foreach ($res as $key => $value) {
    
    $que .= ', '.$i.'.Data AS '.$value['field'].' ';
    $ry .= 'LEFT JOIN data_loop AS '.$i . ' ON A.submission = '.$i.'.submission '. 'AND '.$i.".field = '".$value['field']."' ";

    $i++;
}

$query = $que.$ry."WHERE A.ref = '".$ref."'";

$stmt = $conn->prepare($query);
$stmt->execute();
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($row as $key => $value) {
    echo $value['name'] . '<br>' . $value['address'] . '<br>' . $value['email'] . '<br>' . $value['phone'] . '<br>' . $value['website'] . '<br>';
}

?>

Hope it can help someone else out there.

Search for mysql pivot,yes it can be done.

Mihai
– Mihai

2014-08-15 14:59:05 +00:00
Commented Aug 15, 2014 at 14:59 — Mihai
– Mihai, Commented Aug 15, 2014 at 14:59

Bob · Accepted Answer · 2014-08-15 15:22:29Z

1

Easiest way i can think to do this is to join the table on its self.

So...

    Select A.Submission,
    B.Data AS Name,
    C.Data as Address,
    D.Data as Phone,
    E.Data as Email,
    F.Data as Website
    FROM TableData AS A
    LEFT JOIN TableData AS B
    ON A.Submission = B.Submission
    AND B.Field = "Name"
     LEFT JOIN TableData AS C
    ON A.Submission = C.Submission
    AND C.Field = "Address"
    LEFT JOIN TableData AS D
    ON A.Submission = D.Submission
    AND D.Field = "Phone"
   LEFT JOIN TableData AS E
    ON A.Submission = E.Submission
    AND E.Field = "Email"
   LEFT JOIN TableData AS F
    ON A.Submission = F.Submission
    AND F.Field = "Website"

Changed to left join as suggestion below (from kristof), you can also put some isnull around the select statements to put a "-" in place of a NULL (ISNULL(e.Data, "-")) or Coalese (thanks kristof)

Edit: Added in website, i dont have enough points to comment (50!) so yes you can dynamically create it, the way i would do it would require more work. You would need to create a text string that builds a select statement based on how many variables you have then execute the string once its built. Its a bit of a fiddle to do, but when it works its lovely. I suspect that might be a bit too complicated based on the level of the question, so i would recommend just adding in extra fields when they occur and producing "-" when they dont exist

edited Aug 15, 2014 at 15:22

answered Aug 15, 2014 at 14:59

Bob

1535 bronze badges

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

6 Comments

kristof Over a year ago

better to use Left join for the scenario where some fields are missing and do coalesce(c.data,'-') as Name,coalesce(d.data,'-') as Address ... etc

Corfitz. Over a year ago

This one looks really good. Is there a way to make the naming of columns dynamic as well? Or maybe I should make a first select statement grouped by the field value, and then generate your SQL statement dynamically using PHP? For example later in the table we suddenly get a website field as well. Is there a way to take this into consideration as well during this statement.

kristof Over a year ago

you also need to add a where clause as where a.field = 'Name' otherwise you will end up with multiple rows for each submission. once you add where you can remove join to table b and derive both submission and Name from table a. Also is it a good practice to not use ambiguous names for aliases, instead of a,b,c,.. f. It is better to use n for name, a for address, p for phone e for email etc.

Corfitz. Over a year ago

Thanks a lot @Das. I can understand that this is more difficult part I was expecting. Thanks for the enlightenment. I will work a little with this and see how I can make it dynamic :) The same table is used for multiple ref id's (different forms), so the PHP script actually doesn't know which variables exists in the fields column. But thanks a lot for your answer.

Corfitz. Over a year ago

@kristof Where should I put a WHERE clause? Currently I am doing so in the end if the script, but I still get those multiple rows per submission. Check update

|

mstagg · Accepted Answer · 2014-08-15 15:06:22Z

0

I'm not going to write out all of the code you need, but I will walk you through one possible solution.

Use case statements to evaluate the 'field' column and subsequently assign values to the other columns. Like so:

SELECT
  CASE
    WHEN table.field = 'name'
      THEN table.data
    ELSE null
  END
  AS name,

Repeat that for each new column you want to add, replacing 'name' with the relevant column. (Address, Phone, Email, Website).

answered Aug 15, 2014 at 15:06

mstagg

5074 silver badges16 bronze badges

Comments

drneel · Accepted Answer · 2014-09-10 11:33:44Z

0

SELECT SUBMISSION,
       [NAME],
       [address],
       [phone],
       [email] 
  FROM (SELECT SUBMISSION,
               FIELD,
               DATA 
          FROM rec) P
 PIVOT
  (
       MAX(DATA) FOR FIELD IN ([NAME],[address],[phone],[email])
  ) AS PVT

edited Sep 10, 2014 at 11:33

drneel

2,9055 gold badges32 silver badges49 bronze badges

answered Sep 10, 2014 at 11:06

Avinash Meru

1

Collectives™ on Stack Overflow

Use SQL to output table differently

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related