dongwen5870
2016-03-11 15:36 阅读 69
已采纳

操作/格式化数据库输出

I've got a database query that outputs the data I need, but I'm not sure how to get it into the format I need (also need to get it into csv format, but need to get the output right first).

SELECT `user_id`, `assessment_name`, `question_id`, `question_type_id`, `answer`, `created_at` FROM answer WHERE `assessment_id` = 1

The output is like this (although with 30 rows per submission - just put three each here as an example):

11  Three Intelligences 31  6   4   7/22/08 11:30
11  Three Intelligences 40  4   4   7/22/08 11:30
11  Three Intelligences 41  6   5   7/22/08 11:30
10  Three Intelligences 31  6   3   7/22/08 14:54
10  Three Intelligences 40  4   4   7/22/08 14:54
10  Three Intelligences 41  6   4   7/22/08 14:54
12  Three Intelligences 31  6   4   7/29/08 10:31
12  Three Intelligences 40  4   4   7/29/08 10:31
12  Three Intelligences 41  6   4   7/29/08 10:31

What I need is to reformat this so that it can be used for data analysis, which means getting the data into a single row for each user_id and assessment_name and created_at

assessment_name, user_id, answer(for question 31), question_type_id(for question 31), answer(for question 40), question_type_id(for question 40), answer(for question 41), question_type_id(for question 41), created_at
Three Intelligences, 11, 6, 4, 4, 4, 6, 5, 7/22/08 11:30
Three Intelligences, 10, 6, 3, 4, 4, 6, 4, 7/22/08 14:54
Three Intelligences, 12, 6, 4, 4, 4, 6, 4, 7/29/08 10:31

Is this possible? If so, I also assume I can do it in php, but I don't know enough to figure out the 'while' loops necessary. It probably doesn't even need to produce a csv file output... if I can just get it properly formatted on the page I can copy/paste for this particular project.

Thanks

--EDIT-- Here's what I've got so far -- this will generate the semi-raw output of the data... I think what I need is just to figure out what loops within loops are required within the 'while' to get the desired output.

<?php
require_once '../connection.php';   

// Create connection
$conn = new mysqli(localhost, $dbuser, $dbpass, $db);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
    
    $query_responses = "SELECT `user_id`, `assessment_name`, `question_id`, `question_type_id`, `answer`, `created_at` FROM answer WHERE `assessment_id` = 1";
    $result_responses = $conn->query($query_responses);
    if ($result_responses->num_rows > 0) {
        // output data of each row
        while($row = $result_responses->fetch_assoc()){
           echo $row["created_at"]. ", " .$row["assessment_name"]. ", " .$row["user_id"]. "," .$row["question_id"].  "," .$row["question_type_id"].  "," .$row["answer"].  "<br />";
           
        }
    } else {
        echo "0 results";
    }
    $conn->close();
    
?>

</div>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duanan2732 duanan2732 2016-03-11 18:44

    is it this what you looking for:

    SELECT
      `user_id`,
      `assessment_name`,
      CONCAT(
      GROUP_CONCAT(
        CONCAT(`question_type_id`, ', ', `answer`)
       SEPARATOR ', ' ), ', ', `created_at`) AS RESULT
    FROM answer 
    WHERE `assessment_id` = 1
    GROUP BY `user_id`
    ORDER BY `user_id`;
    

    Sample

    MariaDB []> SELECT
        ->   `user_id`,
        ->   `assessment_name`,
        ->   CONCAT(
        ->   GROUP_CONCAT(
        ->     CONCAT(`question_type_id`, ', ', `answer`)
        ->    SEPARATOR ', ' ), ', ', `created_at`) AS RESULT
        -> FROM answer
        -> -- WHERE `assessment_id` = 1
        -> GROUP BY `user_id`
        -> ORDER BY `user_id`;
    +---------+---------------------+---------------------------------------------+
    | user_id | assessment_name     | RESULT                                      |
    +---------+---------------------+---------------------------------------------+
    |      11 | Three Intelligences | 6, 4, 4, 4, 6, 5, 2016-01-01 00:00:00       |
    |      12 | Three Intelligences | 6, 4, 6, 4, 6, 4, 6, 4, 2016-01-01 00:00:00 |
    +---------+---------------------+---------------------------------------------+
    2 rows in set (0.00 sec)
    
    MariaDB []>
    

    Please let me know if it works for you

    点赞 评论 复制链接分享