keziah keziah - 5 months ago 13
PHP Question

PHP mysqli only one user displayed

I want to achieve this



  • john -> sub user

  • john -> sub user

  • mark -> sub user

  • mark -> sub user

  • dave -> sub user

  • dave -> sub user




But this was the unexpected output



  • john -> sub user

  • john -> sub user




Only the username
john
has appeared


// Get all the users
$s = $sqlConnection->query('SELECT * FROM accounts');

while ($row = $s->fetch_assoc())
{
$uarr = explode(',', $row['username']);

foreach ($uarr as $ueach)
{
// Get all the direct sales
$query = "SELECT *";
$query .= " FROM accounts ACC";
$query .= " INNER JOIN info INF ON ACC.user_id = INF.iuid";
$query .= " INNER JOIN sales SAL ON ACC.user_id = SAL.suid";
$query .= " WHERE SAL.mentor = '$ueach'";

$s = $sqlConnection->query($query);

if (!$s)
{
die($sqlConnection->error);
}

while ($row = $s->fetch_assoc())
{
echo $ueach . ' -> ' . $row['username'] . '<br/>';
}
}
}


Here's the database structure

accounts

user_id | username |
--------+----------+
1 | john |
--------+----------+
2 | mark |
--------+----------+
3 | dave |
--------+----------+


info

iuid |
---------+
1 |
---------+
2 |
---------+
3 |
---------+


sales

suid | mentor |
--------+----------+
1 | john |
--------+----------+
2 | mark |
--------+----------+
3 | dave |
--------+----------+

Answer

You can get all of your data with one query, rather than using two. Because of the JOINs the data you need is readily available:

SELECT SAL.mentor AS mentor, ACC.username AS username
FROM accounts ACC
LEFT JOIN info INF ON ACC.user_id = INF.iuid
LEFT JOIN sales SAL ON ACC.user_id = SAL.suid
ORDER BY SAL.mentor

When you return the rows you will have $row['mentor'] and $row['username'] available to you.

$query = "SELECT SAL.mentor AS mentor, ACC.username AS username ";
$query .= "FROM accounts ACC ";
$query .= "LEFT JOIN info INF ON ACC.user_id = INF.iuid ";
$query .= "LEFT JOIN sales SAL ON ACC.user_id = SAL.suid ";
$query .= "ORDER BY SAL.mentor ";

$s = $sqlConnection->query($query);

if (!$s) 
{
     die($sqlConnection->error);
} 
else 
{
    while ($row = $s->fetch_assoc())
   {
        echo $row['mentor'] . ' -> ' . $row['username'] . '<br/>';
   }

}