bakertaylor28 Posted August 31, 2020 Share Posted August 31, 2020 (edited) I'm trying to simply display the contents of an entire database for the purpose of building an administrative super-user into the back-end to remove login credentials without having to resort to having to manually interact with Mariadb or PHPmyadmin. However, the code is only returning the last row that was entered into the database. I can't seem to figure out why it's not working as intended. The code is intended to simply display the entire contents of the database into an html table. The code i'm using: <?php $link = mysqli_connect("127.0.0.1", "database user", "password", "login"); if (!$link) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } if ($result = mysqli_query($link, "SELECT * FROM accounts", MYSQLI_STORE_RESULT)) { while ($row = $result->fetch_assoc()) { $id = $row["id"]; $username = $row["username"]; $pass = $row["password"]; $email = $row["email"]; $su = $row["su"]; } } mysqli_close($link); ?> <!DOCTYPE html> <html> <head> </head> <body> <table> <tr> <th>id</th><th><th>user name</th><th>password</th><th>email</th><th>elevated privlege</th></tr> <tr> <td> <?php echo $id; ?> </td> <td> <?php echo $username; ?> </td> <td> <?php echo $pass; ?> </td> <td> <?php echo $email; ?> </td> <td> <?php echo $su; ?> </td> </tr> </table> </body> </html> Edited August 31, 2020 by bakertaylor28 correction of the code used. Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/ Share on other sites More sharing options...
requinix Posted August 31, 2020 Share Posted August 31, 2020 Besides the header, your table has exactly one row in it. If you want to display multiple data rows then you're going to need multiple table rows too. Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581050 Share on other sites More sharing options...
bakertaylor28 Posted August 31, 2020 Author Share Posted August 31, 2020 1 minute ago, requinix said: Besides the header, your table has exactly one row in it. If you want to display multiple data rows then you're going to need multiple table rows too. So then how would you do this if there's no way to hard code the exact number of rows into the script? Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581051 Share on other sites More sharing options...
requinix Posted August 31, 2020 Share Posted August 31, 2020 Have you considered using a loop? A loop very much like the one already written in there? Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581052 Share on other sites More sharing options...
bakertaylor28 Posted August 31, 2020 Author Share Posted August 31, 2020 (edited) I thought that was what the whole point of what that loop did as it is written, or at least that is the explanation on practically every site I've seen, in that this is the code example given practically everywhere to do what I'm trying to do. Edited August 31, 2020 by bakertaylor28 Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581053 Share on other sites More sharing options...
requinix Posted August 31, 2020 Share Posted August 31, 2020 That's true, as far as I can tell what you're talking about. But that loop you have only uses some variables. Every time through the loop it updates those same five variables to have five new values. By the time the loop ends, all you have left is whatever the five latest values were - everything that happened before is lost. You've got a loop that can go through all the results. You've got some HTML for a table row. Instead of using the loop for some variables, use the loop for the table row. 1 Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581055 Share on other sites More sharing options...
dodgeitorelse3 Posted August 31, 2020 Share Posted August 31, 2020 your table header also has an extra <th> before user name 1 Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581056 Share on other sites More sharing options...
bakertaylor28 Posted August 31, 2020 Author Share Posted August 31, 2020 29 minutes ago, requinix said: That's true, as far as I can tell what you're talking about. But that loop you have only uses some variables. Every time through the loop it updates those same five variables to have five new values. By the time the loop ends, all you have left is whatever the five latest values were - everything that happened before is lost. You've got a loop that can go through all the results. You've got some HTML for a table row. Instead of using the loop for some variables, use the loop for the table row. so, if I'm understanding you correctly, then the code would look something like this? if ($result = mysqli_query($link, "SELECT * FROM accounts", MYSQLI_STORE_RESULT)) { while ($row = $result->fetch_assoc()) { echo '<table><th></th>'; echo '<td>$row["id"]</td>'; // and so on for each column... } } mysqli_close($link); Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581057 Share on other sites More sharing options...
requinix Posted August 31, 2020 Share Posted August 31, 2020 You're putting the entire table in there. The only thing you need you need to repeat is the data row (that being the second <tr> and what's inside it). You'll also have a bit of a problem with where your code is. The loop is before your <html> so you can't put the table row in there. Instead you need to move the loop itself. That also means moving the mysqli_close, since you can't very well read the query results once you've closed the connection. The good news is that you don't need to close the connection in the first place. PHP will do it for you when the script ends. 1 Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581058 Share on other sites More sharing options...
bakertaylor28 Posted September 1, 2020 Author Share Posted September 1, 2020 5 hours ago, requinix said: You're putting the entire table in there. The only thing you need you need to repeat is the data row (that being the second <tr> and what's inside it). You'll also have a bit of a problem with where your code is. The loop is before your <html> so you can't put the table row in there. Instead you need to move the loop itself. That also means moving the mysqli_close, since you can't very well read the query results once you've closed the connection. The good news is that you don't need to close the connection in the first place. PHP will do it for you when the script ends. Of course, i realized to put the entire table in there and close the sql connection just before closing the function. (Mind you I ended up wrapping all of this within a function, and then calling the function in the HTML code after closing the main php tag and issuing <!DOCTYPE HTML> due to the complexity of the HTML code everywhere else to keep the HTML halfway readable, other than the code for the table itself which doesn't make a difference, as well as to keep the end user from reading the php code.) But you helped me figure out the problem- namely that I can't write these things to variables and then simply call the variable in HTML. Quote Link to comment https://forums.phpfreaks.com/topic/311410-sql-database-display-problem-in-php-70/#findComment-1581060 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.