iterating for loop to form a custom html table

Question

I want to form a custom html table in php from a db query. My sql query is

$query = db_select('taxonomy_term_data','td');
    $query->join('drone','dg','dg.tid = td.tid ');
    $query->fields('td',array('tid','name'));
    $query->fields('dg',array('drone_name'));
    $result=$query->execute()->fetchAll();

The data returned by the query looks like:

Drone Name        Drone Assigned
Huge                DJI
Huge                Parrot
Beginner            Skydio
Beginner            Insitu
Beginner            EHANG

 $drone_table='<br/>
  <table id =  width="40%" cellspacing="5" cellpadding="5" border="1" align="center";">
    <th>Drone Name</th> 
    <th>Drones Assigned</th>';

I wrote a for loop statement

foreach ($result as $ts){

      $drones_assigned[] = $ts->name;
      $drn_name = $ts->drone_name;      

    $com_arr = implode(",",$drone_assigned);

    $drone_table.='<tr>
    <td>'.$drn_name.'</td>
    <td>'.$com_arr.'</td>
    </tr>';
}

This doesnt form the table I want. t forms a table a combination of all the drones assigned and one drone.

I want something like

Drone Name           Drone Assigned
     Huge                 DJI, Parrot
     Beginner             Skydio,Insitu,EHANG

But I am getting

Drone Name        Drone Assigned
    Beginner             DJI, Parrot, Skydio,Insitu,EHANG

Can you represent a sample of the data returned by the query? I also recommend using more readable variables in the future because your code is unreadable honestly. — Raed Yakoubi
– Raed Yakoubi, Commented Feb 1, 2020 at 3:51
So you just need to print the query in a table, for instance if there is 3 Beginner drones you will just have to print one Beginner in the "Drone Name" cell, and all of the assigned drones in the other ones, is that right? — Raed Yakoubi
– Raed Yakoubi, Commented Feb 1, 2020 at 17:01

Raed Yakoubi · Accepted Answer · 2020-02-01 19:08:31Z

Alright, here is one way to do that, i will be using the alternative foreach syntax in the HTML code because i personally think it is more readable this way.

First we have this database query result which i assigned to a PHP array:

$results = [
    ['name' => 'Huge', 'assigned' => 'DJI'],
    ['name' => 'Huge', 'assigned' => 'Parrot'],
    ['name' => 'Beginner', 'assigned' => 'Skydio'],
    ['name' => 'Beginner', 'assigned' => 'Insitu'],
    ['name' => 'Beginner', 'assigned' => 'EHANG'],
];

Now we have to get the assigned drones for each drone name, we'll go with a foreach loop that will generate an array of the form $array['drone_name'] = ['assigned_drones', '.....']:

$drones = [];

foreach ($results as $drone) {
    if (!array_key_exists($drone['name'], $drones)) {
        $drones[$drone['name']] = [];
    }

    $drones[$drone['name']][] = $drone['assigned'];
}

Lastly we'll print out these values inside of an HTML table using the foreach loop (alternative syntax):

<table border="1">
    <tr>
        <th>Drone Name</th>
        <th>Assigned Drones</th>
        <?php foreach ($drones as $name => $assigned): ?>
            <tr>
                <td><?php echo $name; ?></td>
                <td><?php echo implode(',', $assigned); ?></td>
            </tr>
        <?php endforeach ?>
    </tr>
</table>

Fky · Accepted Answer · 2020-02-02 18:07:11Z

1

You can chage your sql query to get formatted results like this :

$result = db_query("SELECT  dg.drone_name , GROUP_CONCAT(td.name) FROM taxonomy_term_data td
                        INNER JOIN drone dg ON dg.tid = td.tid GROUP BY dg.drone_name")->fetchAllKeyed();


foreach ($result as $drone_name => $drones_assigned){

    $drone_table.='<tr>
    <td>'.$drone_name.'</td>
    <td>'.$drones_assigned.'</td>
    </tr>';
}

Also you can take a look to theme table rendering by drupal to create html table easyly https://api.drupal.org/api/drupal/includes%21theme.inc/function/theme_table/7.x

answered Feb 2, 2020 at 18:07

Fky

2,1711 gold badge19 silver badges24 bronze badges

1 Comment

Questions Over a year ago

Thanks @Fky, This is what I wanted. An sql query!!

Collectives™ on Stack Overflow

iterating for loop to form a custom html table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related