CODEWITHSUNDEEP
phpmysql
MehdiB
MehdiB

Reputation: 906

MySQL - selecting members created by members

Here is the scenario. I have different levels of users. the user creation process is like this: admin -> reseller -> marketer -> autoservice

I am trying to write a SQL query to select all autoservices created by a specific reseller. all users are saved in users table which has the following simplified structure:

+----+--------+--------------+-------------+-----------+
| id | userid | username     | role        | createdby |
+----+--------+--------------+-------------+-----------+
| 1  | 334455 | reseller1    | reseller    | admin     |
| 2  | 245578 | marketer1    | marketer    | reseller1 |
| 3  | 235677 | autoservice1 | autoservice | marketer1 |
| 4  | 253569 | autoservice2 | autoservice | marketer1 |
| 5  | 234267 | autoservice3 | autoservice | marketer1 |
| 6  | 245468 | marketer2    | marketer    | reseller1 |
| 5  | 434567 | autoservice4 | autoservice | marketer2 |
| 5  | 532263 | autoservice5 | autoservice | marketer2 |
| 5  | 634262 | autoservice6 | autoservice | marketer2 |
+----+--------+--------------+-------------+-----------+

The query should select autoservice1, autoservice2, autoservice3, autoservice4, autoservice5, autoservice6 and all their fields. currently I'm doing this with a combination of MySQL and php. First I select all marketers created by reseller1:

$sql="SELECT * FROM marketers WHERE createdby=:createdby";
$st=$conn->prepare($sql);
$st->bindvalue(":createdby",'reseller1',PDO::PARAM_STR);
$st->execute();
$usersArray=$st->fetchAll();
$NumberOfusers=$st->rowcount();

Then I loop through the results:

$MembersGeoData=array();
$MembersCount=0;
for($i=0;$i<$NumberOfusers;$i++) {
    $sql="SELECT * FROM autoservices WHERE createdby=:createdby";
    $st=$conn->prepare($sql);
    $st->bindvalue(":createdby",$usersArray[$i]['username'],PDO::PARAM_STR);
    $st->execute();
    $tempUsersArray=$st->fetchAll();
    $tempNumberOfusers=$st->rowcount();
    $MembersGeoData=array_merge($MembersGeoData,$tempUsersArray);
     $MembersCount+=$tempNumberOfusers;
}

This works but seems to be very inefficient. How can I do this with one sql query?

Upvotes: 0

Views: 124

Answers (2)

MehdiB
MehdiB

Reputation: 906

I used The answer from @Gordon Linoff and answered my question :

SELECT a.* FROM autoservices AS a 
JOIN marketers AS m
ON a.createdby=m.username
WHERE m.createdby='reseller1'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Assuming that your hierarchy is exactly correct (it is never skipped and a marketer never creates another marketer), then:

select sa.*
from autoservices sm join
     autoservices sa
     on sa.role = 'autoservice' and
        sm.role = 'marketer' and
        sm.username = sa.createdBy
where sm.createdBy = 'reseller1';

You are correct. You should let the database do this work.

Upvotes: 1

Related Questions