how to select main user and his sub users from mysql database

Question

I am developing an application - a kind of mini bio metrics. in this application admin will create a particular user and give him an access to create other users. The users created by the main user will be the ones creating registration for people. I have two tables, one for admin users, the other for users registration. What I want is to fetch all sub users under admin with the number of users this sub users have added to the users table.

here the source code. It it not giving me what I needed.

  <div class="content-loader">
    <?php
    if($pri == 'user' && $utype == 'super_admin'){
    ?>

    <table cellspacing="0" width="100%" id="example" class="table display table-striped table-hover table-responsive">
    <thead>
    <tr>
    <th>#ID</th>
    <th>Name</th>
   <th>username</th>
   <th>no of people registered</th>
   <th>Date Added</th>       
    <th></th>

    </tr>
    </thead>
    <tbody>
    <?php


    $i =1;
    $stmt = $db_con->prepare("SELECT * FROM users where adder in (select uid from admin_user where adder = '$id')" );
    $stmt->execute();
    $row=$stmt->fetch(PDO::FETCH_ASSOC);
    $ad = $row['adder'];
    $stmt = $db_con->prepare("SELECT * FROM admin_user where uid = '$ad' " );
    $stmt->execute();
    while($rows=$stmt->fetch(PDO::FETCH_ASSOC))
    {

    ?>
        <tr class="delete_user<?php echo $rows['uid']; ?>">
        <td><?php echo $i; $i++;?></td>
        <td><?php echo $rows['name']; ?> </td>
        <td><?php echo $rows['username'];?></td>
       <td><?php //echo $rows['tot'];?></td>
       <td><?php echo $rows['date_added'];?> (<?php echo timeAgo($rows['time_added']) ?>)</td>
      <td class="td-actions text-right">
      <!--<a id="<?php echo $rows['uid']; ?>" class="edit-link btn btn-primary btn-simple btn-xs" rel="tooltip" href="#" title="Edit User"><i class="material-icons">edit</i></a>-->
    <a id="<?php echo $rows['uid']; ?>" class="btn btn-danger btn-user">
        <span class="glyphicon glyphicon-trash"></span>
        </a>
    </td>

        </tr>
        <?php

    }
    ?>
    </tbody>

    </table>
    <?php
    }else{
    ?>  
        <div class="panel-warning" style="padding:7px; text-align:center; font-size:18px;">This page is for the administrator!</div>
        <?php
    }
    ?>

    </div>


                    </div>

For clarification, here are the two tables: Admin users table

Admin users table Img. 1

Users table added by both the admin and his sub users

Users table Img.2

Also, you shoud "clean" stmt before doing a new query: oci_free_statement($stmt); — Foo Bar, Jun 13 '17 at 11:08
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jun 13 '17 at 11:09
Easy way to "clean" possible injection: $password = preg_replace('/\s+/','',$password); (it will delete empty spaces into a variable) — Foo Bar, Jun 13 '17 at 11:14

score 1 · Answer 1 · answered Jun 13 '17 at 11:12

From your code what my suggesstion is to execute only one query. Your desired result can be get using on query only. Try with this

$stmt = $db_con->prepare("SELECT * FROM admin_user where uid = (SELECT adder FROM users where adder in (select uid from admin_user where adder = '$id') )" );
$stmt->execute();

score 0 · Accepted Answer · answered Jun 13 '17 at 11:45

With the joints it is very simple

SELECT ad.name as nom_admin, users.society_name as mySociety // I guess society is the name of the user
FROM admin_user ad
LEFT JOIN users ON users.adder= ad.Uid

If you want to know the number of users that each administrator has registered

SELECT ad.name as nom_admin, count(users.society_name) as nbSociety //users.IDsociety
FROM users
LEFT JOIN admin_user ad ON ad.Uid = name.adders
GROUP BY cl.classe

Your tables sound odd. Arrange well the fields

My friends, is it computer science that we do? : No, we do crafts

how to select main user and his sub users from mysql database

2 Answers2