Im trying to create referals system. I am doing that by passing into during database every registaration all referals (first, second and third "stage").
to clarify, if user ID 3 registers from ref. of user ID 2, and user ID 2 was registered from ref of ID 1, the values for user ID 3 would look like:
`r1` = 2, `r2` = 1, `r3` = NULL
To read from database who is registered using some id's ref link with whole path, I figured out this query:
SELECT `sub`.`id` AS first,`sub2`.`id` AS `second`,`sub3`.`id` AS `third`
FROM `users` AS `usr`
JOIN `users` AS `sub` ON `sub`.`r1` = `usr`.`id`
JOIN `users` AS `sub2` ON `sub2`.`r2` = `usr`.`id`
JOIN `users` AS `sub3` ON `sub3`.`r3` = `usr`.`id`
WHERE `usr`.`id` = :CurrentUserId ;
(:CurrentUserId is PDO placeholder for PHP for int value)
Although, its problematic, as it works only for accounts with all 3 refers into past, so people with only first line or first and second line, but without third will not be listed. In other words, that would mean, if there is user, that has following values
r1 = 2, r2 = 1, r3 = NULL
is not listed. It works if r3 is equal to something.
I could work it around by sending 3 queries to database, but I am sure, that there is better way.
(Im not very expirienced with databases)
Best Answer
Use left joins instead of inner joins so that records from users will still display even if r1, r2, and r3 are
null:However, based on your example I think you may need the following query instead, changing the join conditions so that sub, sub2, and sub3 represent referred users: