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>