Group query result set data by column value while looping [duplicate]

Question

I making a script to display data from some VPSs, which is being stored in a MySQL database.

$result = mysql_query("SELECT * FROM data");
$data = array();
while ($row = mysql_fetch_array($result)) {
    $data[] = array(
             'id' => $row['id'],
             'hostname' => $row['hostname'],
             'loadavrg' => $row['load average']
    );
}

I would like to separate data by hostnames so I can read it like the following example:

foreach ($data['sitename.com'] as $d)
    echo $d['loadavrg'];

I already tried with the following code (just for testing), but didn't work:

$result = mysql_query("SELECT * FROM data WHERE hostname='sitename.com'");
$data = array();
while ($row = mysql_fetch_array($result)) {
    $data[] = array(
        'sitename.com' => array(
            'id' => $row['id'],
            'loadavrg' => $row['load average']
        )
    );
}

I'm just missing the right way and syntax to achieve it.

u_mulder · Accepted Answer · 2016-12-02 10:37:00Z

3

Every element should be added as subarray of 'sitename.com':

while ($row = mysql_fetch_array($result)) {
    $data['sitename.com'][] = array(
        'id' => $row['id'],
        'loadavrg' => $row['load average']
    );
}

answered Dec 2, 2016 at 10:37

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

1 Comment

Mc Kernel Over a year ago

Uh, I though that I already tried that, I must have forgot the second brakets after ['sitename']... however, it works! thanks :)

Sougata Bose · Accepted Answer · 2016-12-02 10:59:18Z

2

Try this,

while ($row = mysql_fetch_array($result)) {
    $hostname = $row['hostname'];
    $data[$hostname][] = array(
        'id' => $row['id'],
        'loadavrg' => $row['load average']
    );
}

edited Dec 2, 2016 at 10:59

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

answered Dec 2, 2016 at 10:39

GoSmash

1,1081 gold badge12 silver badges38 bronze badges

3 Comments

Sougata Bose Over a year ago

What if sitename.com has multiple entries?

GoSmash Over a year ago

@SougataBose I guess the edit would handle the exact requirement

Mc Kernel Over a year ago

Yea, that's help even more that I was asking for, thanks, but you miss [] after [$hostname] if I ain't wrong, that's what I already tried and didn't worked because I was missing it too.

Collectives™ on Stack Overflow

Group query result set data by column value while looping [duplicate]

2 Answers 2

1 Comment

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Linked

Related