Display fields as values in array from external SQL DB

Question

I see no reason to use wpdb for this, unless your table is in the WordPress database. Also your code would be much clearer (and therefore less prone to errors) if you separate the fetching of the result into an array, and the processing of this array for transformation, from the generation of the final html output. Separate logic from presentation.

So, first put all data into an array in the standard way:

$query = ("SELECT * FROM titles WHERE `active` = 0 ORDER BY author, title");
$results_array = array();
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
  $results_array[] = $row;
}

The first row of this array, i.e. $results_array[0], for example, will be the array ('Title' => 'Title 1','Author' => 'j cool', 'Device1' => 'inputA',...)

Edit: Alternatively, if you insist on using the wpdb class, replace the above code by:

$mydb = new wpdb('user','password','database','server');
$results_array = $mydb->get_results("SELECT * FROM titles WHERE `active` = 0 ORDER BY author, title",ARRAY_A);

$results_array has the same content with both approaches. The rest of the code doesn’t change.

End edit

You want to obtain from $results_array an associative array that associates to each Author a table mapping input to devices, like this:

array('j cool' => array('inputA' => array('Device1', 'Device2'),
                        'inputB' => array('Device3')),
      'l maker' => array('inputA' => array('Device1'),
                        'inputB' => array('Device2'),
                        'inputC' => array('Device3')),
       'm this' => ...)

The following code assumes you know all possible values of inputs in advance, and that these do not match exactly authors or titles.

 $inputs = array('inputA','inputB','inputC');

 $input_table = array();

 foreach ($results_array as $row) {
   $row_inputs = array();
   foreach ($inputs as $in) {
      //get all keys (devices) in $row  that have input $in as value
      $devices = array_keys($row,$in); 
      if (!empty($devices))
        $row_inputs[$in] = $devices;
   }
   $input_table[$row['Author']] = $row_inputs;
 }

This generates an array where each row, indexed by the Author, is an array like the example above. Now we add the title:

$final_table = array();
foreach ($results_array as $row) {
  $final_table[] = array('Title' => $row['Title'],
                       'Author' => $row['Author'],
                       'Inputs' => $input_table[$row['Author']]);
}

Finally, display the resulting array in html:

$html="";
//$html .= '<html><body>';
$html .= "<table>"; //add here code for table headers too

foreach ($final_table as $row) {
  //first row for author
  $html .= '<tr><td>'. $row['Title'] . '</td><td>' . $row['Author'] . '</td>';
  foreach (array_keys($row['Inputs']) as $in) {
    //output input names for this author
    $html .= '<td>'.$in.'</td>';
  }
  $html .= '</tr><td></td><td></td>';
  //second row for author, starting in third column
  foreach ($row['Inputs'] as $in => $devices) {
    $html .= '<td>'.implode(" ",$devices).'</td>';
  }
  $html .= '</tr>';
}

$html .= '</table>';
// $html .= </body></html>
echo $html;

Fine-tune the presentation at your liking…