Count how many times a value appears in a PHP and SQL Column

Asked

Viewed 193 times

0

I would like the code to automatically count and show next to the form options, the amount of times the values that are pulled from the database appear in the TABLE clients COLUMN account

Obs: The values of the options are pulled from the TABLE Accounts COLUMN user, and wanted the values pulled that are in the options to be counted in the TABLE clients COLUMN account

wanted to leave so equal in the image:

https://i.stack.Imgur.com/xceBC.png

already this last value is not registered in the table Accounts user column so it does not need to appear in the options nor be counted. https://i.stack.Imgur.com/hB6Jh.png

SELECT account, COUNT(*) FROM clients GROUP BY account

<div class="form-group">
      <text class="bold">
        Conta *
        </text><br>
        <select name="conta" class="form-control" required>
                <option value="">Selecione a conta</option>
            <?php 
                while($row = mysqli_fetch_array($sqldata, MYSQLI_ASSOC)) {
                {
                echo "<option value='".$row['usuario']."'>".$row['usuario']."</option>";
                }
}
?>  
        </select>
</div>

and if possible to count and give 6 or more, disable the option (disabled).

php

1 answer

2


Give an alias the count:

SELECT conta, COUNT(0) total FROM clients GROUP BY conta

In the while check that the total is greater than 5, and set the disabled, and the rest is to concatenate:

<?php 
while($row = mysqli_fetch_array($sqldata, MYSQLI_ASSOC)) {

    # verifica se é maior que 5 e seta disabled
    $disable = ($row['total'] > 5) ? 'disabled' : '';
    # gera linha
    echo '<option value="' . $row['conta'] . '" ' . $disable . '>' . $row['conta'] . '(' . $row['total']  . ')</option>';
}
?>

  • perfect! thank you so much for helping! :)

  • Actually that alias you told me about where I put it? because I just changed the $sqldata variable = "SELECT * FROM Accounts"; to what you said $data = "SELECT account, COUNT(0) total FROM clients GROUP BY account"; and instead of pulling the accounts from the Accounts table pulled the accounts from the clients table.

  • You didn’t want to count the amount of accounts in the table clients? That it was the select that passed SELECT conta, COUNT(*) FROM clients GROUP BY conta. If it’s from accounts (if I understood correctly, it would be SELECT usuario, COUNT(*) FROM accounts GROUP BY usuario. Or you want to pull values from accounts and tell of clients? What is the link between the 2 tables?

  • is that in the ACCOUNTS table I register the accounts, and in the CLIENTS table I register the customers, and at the time I register the customers I pull the accounts that are available from the ACCOUNTS table, and I can register up to 6 clients for each account, is basically a sharing of accounts, why at the time I am registering the client wanted to see by the options how many are using that account, understand?

  • Now I understand, so you have to make a JOIN. And there is no reason you bring the accounts with more than 5, since it will leave disabled, only behind those who still have "space". I won’t be able to do that now, maybe a little later, or tomorrow, and I’ll update my answer to you.

  • How’s the client-client relationship? Because this "user" field in "Accounts" is strange, since you link more than one user in an account, right!?

  • Actually the ACCOUNTS table has a user field, and the CLIENTS table has an account field, but the same data is stored, only only with a different name. the values stored in the USER table ACCOUNTS column go to the ACCOUNT column of the CLIENTS table. I think I just put so at the time, for me not to confuse with the name of the person, so I did not put user put account.

  • Try it this way: SELECT c.codigoconta, (SELECT COUNT(0) FROM accounts WHERE conta = c.id) total FROM clients c . where is codigoconta, is the field of the account that should display, which should be id probably. Then you have to change $row['conta'] for the correct column name.

  • It has a lot of wrong definition, the correct is to restructure this comic first, understand why, then understand the select, then the while.

  • I didn’t understand that last code you passed, I changed it codigoconta for conta but came normal without telling and came (0).

Show 5 more comments

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.