MYSQL Database -> 2d array

[lednum]

I'm trying to load a mysql table as a 2d array.

 

This is what I have so far, I have a feeling it's even close to what I need...

 

<?php
$mysql = new mysqli();

$mysql->connect("localhost", "*****", "*****", "*****");

   
$query = "SELECT DISTINCT (`userID`), subModule, moduleNumber	FROM  `QuestionAttempt`";
$result=mysql_query($query);
$nume=mysql_num_rows($result);

while($row = mysql_fetch_array($result)) {
    $detail = $row['userID'];
    echo '
    <div style="float:left; width:380px; padding-left:15px; padding-top:10px; [u]border-bottom: dotted1px;[/u]"></div>
    ';
}	

?>

 

Any help would be greatly appreciated...

[rhodesa]

before we get to the PHP code, you are probably having a problem with the SQL query. when using mysql_query() you should add a mysql_error() after for debugging:

 $result=mysql_query($query) or die(mysql_error());

 

you can't select with DISTINCT and also have other fields in the query. can you describe what the data in the table looks like?

[lednum]

Thanks rhodesa!

 

The mysql table looks like 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

 

I'm trying to organize it into an array that looks like this(where everything is sorted by unique use ids):

 

userIDModule 2 Module 2 Incorrect Module 3 CorrectModule 3 Incorrect

08 3 33

-12 0 00

83 0 00

 

[rhodesa]

Ah, ok...well...you won't be able to do that with PURE SQL...but no fear, we can build it with PHP. do you just want basic numeric arrays or associative arrays with key names?

 

aka - if you did a print_r() on your final result, what would you want it to be?

[lednum]

I think associative arrays?

 

I want the final result to look like the second table...

 

The first table mysql table is updated frequently and will have more user accounts added to it once the site it is tracking goes online...

[rhodesa]

What if it was reduced to a row for each UserID/ModuleNumber combination? So the final output would be:

userID	 moduleNumber	 correct	 incorrect
0	3	3	3
0	2	7	1
-1	2	2	0
8	2	3	0

[lednum]

I think that would work too!

[rhodesa]

In that case...

 

<?php
$query = "SELECT userID, moduleNumber, SUM(correct) as totCorrect, SUM(incorrect) as totIncorrect FROM  `QuestionAttempt` GROUP BY userID, moduleNumber";
$result=mysql_query($query) or die(mysql_error());
$nume=mysql_num_rows($result);
while($row = mysql_fetch_array($result)) {
  print_r($row);
}   
?>