Jump to content

Reorganizing Tables by Unique IDs


lednum

Recommended Posts

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.

 

 

Link to comment
https://forums.phpfreaks.com/topic/161970-reorganizing-tables-by-unique-ids/
Share on other sites

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();


?>

 

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.