Reorganizing Tables by Unique IDs

[lednum]

I'm new to php and was hoping someone could steer me in the right direction/tell where I am going wrong.

 

I'm trying to reorganize data from a mysql table.

 

Basically I need to turn this:

userID moduleNumber correct incorrect

0 3 3 3

0 2 2 0

0 2 5 1

-1 2 2 0

8 2 3 0

 

 

Into this:

userID Module 2 Correct Module 2 Incorrect Module 3 Correct Module 3 Incorrect

0 8 3 3 3

-1 2 0 0 0

8 3 0 0 0

 

 

This is what I have for code so far:

 

<?php

$mysql = new mysqli();


$mysql->connect("localhost", "username", "password", "database");



$query = "SELECT DISTINCT (
`userID`
)

FROM  `QuestionAttempt` ";


$squery = "SELECT * FROM `QuestionAttempt` ";



$result = $mysql->query($query);


print("<table>");


while($row = $result->fetch_object())


{


$UserID = $row->userID ;

$ModuleNumber = $row->moduleNumber;

$Correct = $row->correct;

$Incorrect = $row->incorrect;

$ModuleNumberSum = array_sum($Incorrect);


print("<tr>");

printf("<td>%s</td>", $UserID);

printf("<td>%s</td>", $ModuleNumberSum);
	printf("<td>%s</td>", $Difficulty);
	printf("<td>%s</td>", $Correct);


print("</tr>");



}



printf("</table>");


$mysql->close();


?>

 

 

I want to figure most of this out by myself but I know I am making mistakes early in code.

 

 

Any help would be highly appreciated.

 

 

[Maq]

What happens when you run your code?  Why is it wrong?

 

I'm still confused on the pattern of transforming table1 into table2...

 

(Please surround your code with

tags)

[lednum]

Thanks Maq,

 

 

I'm trying to sort the data by User ID, The second table only has one row per User ID and the values for correct and incorrect answers are summed.

 

When I run the code I get this:

"

Warning: array_sum() [function.array-sum]: The argument should be an array in /Users/jchung/Sites/brent/ex7.php on line 37

 

Warning: array_sum() [function.array-sum]: The argument should be an array in /Users/jchung/Sites/brent/ex7.php on line 37

 

Warning: array_sum() [function.array-sum]: The argument should be an array in /Users/jchung/Sites/brent/ex7.php on line 37

0

-1

8"

 

 

Here is the code again with tags

 

<?php
   
$mysql = new mysqli();


$mysql->connect("localhost", "username", "password", "database");
   
   

$query = "SELECT DISTINCT (
`userID`
)

FROM  `QuestionAttempt` ";
   

$squery = "SELECT * FROM `QuestionAttempt` ";


   
$result = $mysql->query($query);
   

print("<table>");
   

while($row = $result->fetch_object())
   

{
      

$UserID = $row->userID ;
   
$ModuleNumber = $row->moduleNumber;
   
   $Correct = $row->correct;
      
$Incorrect = $row->incorrect;
   
   $ModuleNumberSum = array_sum($Incorrect);

      
print("<tr>");
      
printf("<td>%s</td>", $UserID);

   printf("<td>%s</td>", $ModuleNumberSum);
      printf("<td>%s</td>", $Difficulty);
      printf("<td>%s</td>", $Correct);


   print("</tr>");
   
   

}

   

printf("</table>");
   

$mysql->close();


?>

 

[Maq]

You can use the MySQL aggregate function SUM() rather than getting the sum of an array.

 

You are giving the the array_sum() function an integer rather than an array.  The way you were doing it wouldn't work anyway.  You can retrieve the sum in a single query by using what I described above.