php display data from mysql database and group by

Question

noob here, sorry if my question is not clear, but here it goes.

I have a mysql database with 2 tables (accounts and staff) I want to display the "account_name" and underneath that a list of the staff's "name" who are assigned to that account.

with the code below I get the "account_name" and only the first staff's "name" assigned to that account. some of the accounts have 2 or more staff members assigned.

I get:

Target (account_name)

staff1 ("name")

WalMart ("account_name")

staff1 ("name")

I want:

Target ("account_name")

staff1 ("name") staff2 ("name")

WalMart ("account_name")

staff1 ("name") staff2 ("name") staff3 ("name")

any and all help will be greatly appreciated.

Alex

code:

$query = "SELECT staff.name, staff.drive_id, accounts.id, accounts.account_name
FROM staff
JOIN accounts
ON staff.drive_id = accounts.id
WHERE staff.drive_id = accounts.id
AND accounts.drive_date = CURDATE()
GROUP BY accounts.account_name";
$result = mysql_query($query);

while ($staff = mysql_fetch_array($result))
{

echo "<br />";
echo $staff['account_name'];
echo "<br /><br />";
echo $staff['name'];
echo "<br />";
}

Jelle Ferwerda · Accepted Answer · 2013-12-27 14:30:55Z

1

Group by will reduce all results to one result per grouped field You were after order by

$query = "SELECT staff.name, staff.drive_id, accounts.id, accounts.account_name
                        FROM staff
                        JOIN accounts
                        ON staff.drive_id = accounts.id
                        WHERE staff.drive_id = accounts.id
                        AND accounts.drive_date = CURDATE()
                        ORDER BY accounts.account_name";

In your loup you can then check whether the account_name has changed comparde to the previous result, and first echo the account details, then the name.

$result = mysql_query($query);
$account = '';

while ($staff = mysql_fetch_array($result))
{
if($account == $staff['account_name'])
  {
  echo "<br />";
  echo $staff['account_name'];
  $account = $staff['account_name'];
  }
echo "<br /><br />";
echo $staff['name'];
echo "<br />";
}

edited Dec 27, 2013 at 14:30

answered Dec 27, 2013 at 14:17

Jelle Ferwerda

1,2291 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

almen4 Over a year ago

when I use ORDER BY I get Target("account_name") staff1("name") Target("account_name") staff2("name"), I would like the account name just to show once

Jelle Ferwerda Over a year ago

That's why I said: i the while loop, check whether you have a new account name: Thene cho it, otherwise don't.

almen4 Over a year ago

I apologize for being such a noob. when I use the code above I just get the staff name not the account name.

almen4 Over a year ago

I switched your if statement to if($staff['account_name'] != $account) now it works thank you for all your help it is greatly appreciated.

Jelle Ferwerda Over a year ago

Sorry, my bad, indeed :|

Jamey Hicks · Accepted Answer · 2013-12-27 14:45:07Z

0

You need an operator to specify how to combine the account names, such as the following:

$query = "SELECT staff.name, staff.drive_id, accounts.id,
                 GROUP_CONCAT(accounts.account_name SEPARATOR ' ')
          FROM staff
          JOIN accounts
          ON staff.drive_id = accounts.id
          WHERE staff.drive_id = accounts.id
          AND accounts.drive_date = CURDATE()
          ORDER BY accounts.account_name"

See MySql's group by functions for more details and options.

answered Dec 27, 2013 at 14:45

Jamey Hicks

2,3781 gold badge14 silver badges20 bronze badges

Collectives™ on Stack Overflow

php display data from mysql database and group by

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related