Jump to content

SQL Database Display problem in php 7.0


bakertaylor28

Recommended Posts

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 by bakertaylor28
correction of the code used.
Link to comment
Share on other sites

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 by bakertaylor28
Link to comment
Share on other sites

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.

  • Thanks 1
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

  • Thanks 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.