UPDATED
Ok here I have a list of users with their IDs and the sponsor ID. In my database it is larger since it contains dates, email, name and other user data, so I only put what interests me to finish my project.
ID Status IDpatrocinador
1 765810095 activo 123456789
2 463913563 inactivo 765810095
3 102195308 activo 463913563
4 091299283 inactivo 102195308
5 701757563 activo 091299283
6 262313912 inactivo 701757563
7 779891896 activo 262313912
8 463913563 inactivo 779891896
9 796540095 activo 463913563
NOTE: There are no numbers from 1 to 9 in my database, I only put them to guide you.
Okay. What I'm trying to do is: Active number 1 is the sponsor of number 2 who is inactive, 2 of 3, etc. Well so far we're good.
IF a user is inactive eg. number 2, I will take the sponsor ID and place it in the sponsor ID of 3, leaving 3 like this.
3 102195308 activo 765810095
Because 3 had the sponsor ID of 2 which is now inactive.
So in the end everything will be like this.
ID Status IDpatrocinador
1 765810095 activo 123456789
2 463913563 inactivo 765810095
3 102195308 activo 765810095
4 091299283 inactivo 102195308
5 701757563 activo 102195308
6 262313912 inactivo 701757563
7 779891896 activo 701757563
8 463913563 inactivo 779891896
9 796540095 activo 779891896
In this example use a sponsored user for each sponsor, but the sponsor can have multiple sponsors, example:
ID Status IDpatrocinador
1 765810095 inactivo 123456789
2 741852896 activo 765810095
3 321654852 activo 765810095
4 852715671 activo 765810095
So it should be like this:
ID Status IDpatrocinador
1 765810095 inactivo 123456789
2 741852896 activo 123456789
3 321654852 activo 123456789
4 852715671 activo 123456789
What I would do is the following. I would create a temporary table and save the results of the first query.
You do what you have to do and, in the end, you attack the update directly to the temporary table
The idea would be this.
And when you're done, remember to delete the table so it doesn't crash if you do the same thing again in the future.
I'm going to give you an example of how I did it once and I hope it helps you. In my case I had to change the product barcodes.
The first thing I use is the operator
IN
that allows multiple values to be included in one statementWHERE
.In this way I listed all the
id
so I could make the corresponding change. In my case, I repeat, the client entered a product and changed the bar code.In
WHERE id IN ('".$fila['id']."')";
IN
it takes all lineid
to make the change.Well, I hope I have helped you!
Doing self-join:
Where
u
are the sponsored andp
the sponsors.