Adding +x to each record returned in a SELECT

Asked

Viewed 179 times

1

For example, in a select in the bank, will be returned 5 records:

SELECT * FROM esc_usuarios
WHERE usu_indicador_codigo = '" . $_SESSION['codigo'] . "'
AND usu_situacao = 'ativo'"

Then I want to assign a value to each record, for example 10. Now I want to give a echo in the sum of the value of all these records, i.e., 5 (select records) x 10 (value assigned to each record), then returning the number 50. How can I do that?

Update 0: New query

SELECT usu_nome, (
          (SELECT (count(usu_codigo)*10) FROM esc_usuarios WHERE usu_indicador_codigo = a.usu_codigo AND usu_situacao = 'ativo')
          +
          (SELECT (count(usu_codigo)*5) FROM esc_usuarios WHERE usu_indicador_codigo in (
            (SELECT usu_codigo FROM esc_usuarios WHERE usu_indicador_codigo = a.usu_codigo AND usu_situacao = 'ativo')
          ))
        ) usu_porcentagem
        FROM esc_usuarios a
        WHERE usu_codigo = 1
php sql query mysqli

  • Hello friend, welcome. Come on, could you detail better how you would like to assign this number 10 to each row returned? would it be a column specific to the table consulted or would it be a variable within the loop in which you query the lines in php? because until now I think I could solve everything within the select itself... try to better detail your objective and the situation please.

  • Hello! So, in this system, one person can register other people, and these other people register more people, forming a hierarchy. If the first one registers the second one, the first one gets 10 points, and if the second one registers the third one, the first one gets 5 points and the second one gets 10 points, then if you registered someone and they’re "active", 10 points for you, and +5 for each person you registered, register another

  • @Then I wanted to show how many points a person already has, adding the 10’s that she registered, + the 5’s that the people she registered, and so on

  • The score will stay only on 10 and 5, wanted to use as a variable within the loop

  • then it is a query of users where the id is the session code and it is as "active", only applying this rule of 10 and 5 points for each registered user

  • right, and how do we know who was registered by whom or who registered who? How is the table structure?

  • in the table of registered users, this "usu_indicador_code" is a foreign key where it shows the id of who registered it, and in the registration form, it already inserts the id of the Session, ie, of who is registering

Show 2 more comments

1 answer

1


Friend, if I understood your intention correctly, I wouldn’t need PHP intervention to calculate the score. You can perform the entire calculation on the database side. Follow the corresponding select and link with the example running.

Note: I used the field id as the key Primary, switch to what you used.

This select returns all data of all users with a column with the score on the side. To return this column is made a sub-select to return the score of the direct indication, summed with the other sub-select with the indication of the indicated.

Select * , (
  (Select (count(id)*10) from esc_usuarios where usu_indicador_codigo = a.id)
  +
  (Select (count(id)*5) from esc_usuarios where usu_indicador_codigo in (
    (Select id from esc_usuarios where usu_indicador_codigo = a.id)
  ))
) pontos
From esc_usuarios a
Where usu_situacao = 'ativo'

If you want a specific user, enter the rule in Where as you did in your example.

AND id = '" . $_SESSION['codigo'] . "'

Follow the link to test: http://sqlfiddle.com/#! 9/334a44/2

  • Perfect! Just what I needed, I am very grateful, looking now it is much easier to understand the logic.

  • Boy, I’m just having a little problem, the query I updated based on yours, it’s not counting the assets that give 5 points, it only counts if there is someone active that gives 10 points. For example, user 1 registered user 2 (+10), so user 2 registered user 3(+5), only if user 2 is inactive, user 1 does not receive the 5 user points 3. I will update the post.

  • Ah, if user 3 is inactive, user 1 still gets 15 points, when it should only be with 10, that would be by user 2 be active

  • when I take the condition situation = 'active', it counts normal, only that this condition is very necessary for the project

  • Nevermind, I took a bottle of coffee and discovered the hahahha error, thank you

Browser other questions tagged php sql query mysqli

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