Can't fetch a single field from database

Question

When I try to do it it fetches the entire column, not just one field.

$connection = mysqli_query($db, "SELECT * FROM users");




<?php
while($row = mysqli_fetch_array($connection)) {
?>
<p>Your balance is: <?php echo $row['balance']; ?></p>
<?php
}
?>

That was outputting Your balance is: 5 Your balance is: 0 Your balance is:

So I tried

$query_for_selecting = mysqli_query($db, "SELECT balance FROM users");


<?php if (mysqli_num_rows($query_for_selecting) > 0) { ?>
<?php while($row = mysqli_fetch_assoc($query_for_selecting)) { ?>
<p>Your balance is <?php $row['balance']; ?></p>
<?php } ?>
<?php }  ?>

And that wasn't outputting anything so eventually, I tried using a WHERE clause and a limit of 1

$query_for_selecting = mysqli_query($db, "SELECT * FROM users WHERE balance = '3' DESC LIMIT 1");
<?php if (mysqli_num_rows($query_for_selecting) > 0) { ?>
<?php while($row = mysqli_fetch_assoc($query_for_selecting)) { ?>
<p>Your balance is <?php $row['balance']; ?></p>
<?php } ?>
<?php }  ?>

All I got was a white screen

Answer 1

I have a feeling as to what's going on. You're fetching the entire database without either using a LIMIT of 1 and/or use a WHERE clause, given that you have unique ID's somewhere for columns. I am sure that you have more than the one record in your database table.

I was going to post this in a comment but decided not to. Stack doesn't really want us to do that, (edit) and at this point, it is way too long for a comment.

@ADyson "I initially wanted to display the balance of the user that's logged in, but that didn't work out." – markthedark

About that. It seems that what you are looking for is to get the balance for a user/record in particular. For that, you definitely need to use a WHERE clause.

If your query failed, enable error reporting and checking for errors on the query.

References:

• https://www.php.net/manual/en/function.error-reporting.php
• https://www.php.net/manual/en/mysqli.error.php

Plus, the $i = 0; and $i++; may not be helping. It's hard to say what that is supposed to do. I know the syntax, I just don't know why you're wanting to increase it.

Side note: I would avoid in using $connection as a query variable assignment. It could be confusing. Try to use something clear like $query_for_selecting.

Answer 2

I think a basic little tutorial might be in order here.

First off: SELECT * FROM users means: "give me everything in the users table". You will get the full table, every row and every column.

while($row = mysqli_fetch_array($connection)) will loop through every row your query returns. It will call mysqli_fetch_array() and put the result in $row until there are no more rows in your query's result.

If you only want to output a single row of data, you have three options:

• Add a WHERE condition so that your query will only fetch a specific row
• Add a LIMIT clause so that your query will only fetch a single row
• Call mysqli_fetch_array() only once instead of in a while loop

From the comments in the discussion thread, it looks like you want to retrieve only the balance for the currently logged in user, and you have a session variable somewhere that tells you who that user is. That means you'll want to use a WHERE condition so that your query will only fetch the row for that specific user.

You haven't told us what that session variables is called or what the name is of the column in the users table that you can compare that session variable with, so I'll assume that your users table has an id column and your session variable is called user_id and should match the id value from your users table.

So let's say the user with id 123 is currently logged in. You'll want to end up with the query SELECT balance FROM users WHERE id = 123.

The quick solution is to change your code to:

$connection = mysqli_query($db, "SELECT balance FROM users WHERE id = " . $_SESSION['user_id']);.

This is bad code. We'll make it better, but try this first and see if it gets you the result you actually want. If it doesn't, let me know.

The reason this is bad code is because adding variables to a query string like this dramatically increases the risk of SQL injections. If there's any possibility at all that the value of the variable comes from user input, then at some point a user will figure that out and make sure it contains something that will break your application.

• Best case scenario: the page simply won't render for that one user.
• Bad case scenario: the user will be able to read out your entire database and will sell sensitive user data to the highest bidder.
• Worst case scenario: the user will be able to inject some of their own Javascript code into your database in a column you're not sanitizing before rendering, letting them capture and intercept passwords and/or financial information your users are entering on your site and they will then use that information to make life miserable for all of your users.

So you don't want to just drop $_SESSION['user_id'] into your query like that. Instead, you'll want to use a prepared statement and let the database handle the problem of dropping the variable into the query.

First, you'll need to prepare your query:

$statement = mysqli_prepare($db, "SELECT balance FROM users WHERE id = ?");

The ? symbol is a placeholder where you can bind a parameter. Let's do that:

$statement->bind_param("i", $_SESSION['user_id']);

The "i" tells MySQL that you're binding an integer value. If you're not matching against a user id but a username, for example, you'll want to instead use "s" to tell MySQL you're binding a string value.

Now you can execute the query and get the result. Putting it all together:

$statement = mysqli_prepare($db, "SELECT balance FROM users WHERE id = ?");
$statement->bind_param("i", $_SESSION['user_id']);
$statement->execute();
$connection = $statement->get_result();

Let us know if that works. Some tweaking might be required.