[SOLVED] List replies by user

[timmah1]

I have a database that inputs the users name and either correct or wrong

Each user could have up to 100 entries in the database.

 

How can pull that information from the database with the user name being listed just once, and how many that user has correct?

 

Just like forums, it shows the user and how many posts they have.

 

So it would look like this:

User - (87)

 

Instead of like this:

User - (87)

User - (87)

User - (87)

User - (87)

User - (87)

User - (87)

User - (87)

etc...

 

 

Thank you in advance.

 

[F1Fan]

SELECT user, SUM(othercolumn) FROM table GROUP BY user

[DarkWater]

SELECT username, COUNT(*) AS total FROM table_name WHERE answer='correct' GROUP BY username ORDER BY total DESC;

 

I was obviously speculating on the column names and stuff, but you get the idea.

[timmah1]

Thanks.

 

I have this

$users = mysql_fetch_assoc(mysql_query("SELECT name,answer COUNT(*) AS total FROM daily WHERE answer = 'Correct' GROUP BY name ORDER BY total DESC"));
echo $users['total'];

 

But I keep getting an error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

[DarkWater]

I'd think you'd know what to do...read the quote in your signature. =P

[timmah1]

D'oh!!

[DarkWater]

Okay, seeing as you marked this as Solved, I'd assume you got it all working?

[timmah1]

I thought I had it figured out.

 

What am I doing wrong here?

It's only showing one result

$csql = mysql_fetch_assoc(mysql_query("SELECT name,answer, COUNT(*) AS total FROM daily WHERE answer = 'Correct' GROUP BY name ORDER BY total DESC"))
			OR die(mysql_error());;


			echo $csql['name'];
			echo $csql['total'];

 

I need it to show every user that's in the database with the number beside it

 

 

[DarkWater]

You'd need to loop through the results...Also, don't use mysql_fetch_assoc() directly on the result resource from mysql_query().  Save it to a variable. =/

 

$csql = mysql_query("SELECT name,answer, COUNT(*) AS total FROM daily WHERE answer = 'Correct' GROUP BY name ORDER BY total DESC") OR die(mysql_error());
while ($row = mysql_fetch_assoc($csql)) {
        echo $row['name'] . '-' . $row['total'] . "<br />\n";
}

[timmah1]

Thank you once again DarkWater, you are a life saver.

 

This is now solved!!