DEVILofDARKNESS Posted April 8, 2009 Share Posted April 8, 2009 Hi, this code only shows one row from the database and I wnated to show everyone. <?php session_start(); require_once 'login-check.php'; /*DATABASE SETTINGS */ $userid = $_SESSION['userid']; ?> <html> <head> <title>Cryptoworld</title> <meta name="author" content="Kruptein"> </head> <body> <center> Dit is een lijst met alle gebruikers.<p> <table border="1"> <tr> <td>User Name</td><td>Challenges Solved</td></tr> <?php $query = "SELECT * FROM users"; $result = mysql_query($query); $user = mysql_fetch_assoc($result); $query = "SELECT COUNT(DISTINCT crypto_id) FROM solved"; $result = mysql_query($query); list($ammount) = mysql_fetch_row($result); echo "<tr><td>" . $user['user_name'] . "</td><td>" . $ammount . "</td></tr>"; ?> </table> <p><hr><p> <a href="/login.php">Login</a> -- <a href="/logout.php">Logout</a> -- <a href="/register.php">Register</a> -- <a href="/index.php">Index</a> -- <a href="/stats.php">Stats</a> </center> </body> </html> Quote Link to comment Share on other sites More sharing options...
Steve Jabs Posted April 8, 2009 Share Posted April 8, 2009 I would presume it is because you aren't looping anything. Are you looking at getting the Username and then Amount for each user? If so, why don't you do just one query and combine them with a join? Then make a while loop on the result set in php. Quote Link to comment Share on other sites More sharing options...
Yesideez Posted April 8, 2009 Share Posted April 8, 2009 Are you wanting to also link the `solved` table to the users table? To display all the rows returned you need to call the results from the query inside a loop. Quote Link to comment Share on other sites More sharing options...
jamesxg1 Posted April 8, 2009 Share Posted April 8, 2009 im not 100% on this but try <?php $count = 0; $query = mysql_query("SELECT COUNT(DISTINCT crypto_id) FROM solved") or die(mysql_error()); list($ammount) = mysql_fetch_row($query); while ($user = mysql_fetch_object($query)) { if ($count!=0){echo "";} echo "<tr><td>$user->user_name</tr></td><tr><td>$ammount</tr></td>"; $count++; } ?> Quote Link to comment Share on other sites More sharing options...
DEVILofDARKNESS Posted April 8, 2009 Author Share Posted April 8, 2009 Ok I changed it but it still doesn't work. And I had to do a second query because I don't know how to make a SELECT COUNT(DISTINCT) in an inner join. <?php session_start(); require_once 'login-check.php'; /*DATABASE SETTINGS */ $userid = $_SESSION['userid']; ?> <html> <head> <title>Cryptoworld</title> <meta name="author" content="Kruptein"> </head> <body> <center> Dit is een lijst met alle gebruikers.<p> <table border="1"> <tr> <td>User Name</td><td>Challenges Solved</td></tr> <?php $query = "SELECT users.*,solved.* FROM users INNER JOIN solved ON solved.user_id = users.user_id"; $result = mysql_query($query); while($user = mysql_fetch_assoc($result)) { $usersid = $user['user_id']; $query = "SELECT COUNT(DISTINCT crypto_id) FROM solved WHERE user_id = '$usersid'"; $result = mysql_query($query); list($ammount) = mysql_fetch_row($result); echo "<tr><td>" . $user['user_name'] . "</td><td>" . $ammount . "</td></tr>"; } ?> </table> <p><hr><p> <a href="/login.php">Login</a> -- <a href="/logout.php">Logout</a> -- <a href="/register.php">Register</a> -- <a href="/index.php">Index</a> -- <a href="/stats.php">Stats</a> </center> </body> </html> EDIT: I Also tried the one from jamesxg1 but that only showed me the table header, no rows from the database Quote Link to comment Share on other sites More sharing options...
DEVILofDARKNESS Posted April 9, 2009 Author Share Posted April 9, 2009 Can't anybody help me? I have a table users (user_id,user_name) And I have a table solved (user_id,crypto_id) I want to have a table with left the username and right the COUNT(DISTINCT crypto_id) From solved. This should be orderd on that count DESC??? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.