1

Have a SQL dataset like this:

    Title   Author  Device1 Device2 Device3
    Title1  j cool  inputA  inputA  inputB
    Title2  l maker inputA  inputB  inputC
    Title3  m smith inputB  inputB  inputB

Want to display like this:

    Title   Author  header1 header2 header3
    Title1  j cool  inputA: inputB:
                    Device1 Device3
                    Device2 
    Title2  l maker inputA: inputB: inputC:
                    Device1 Device2 Device3
    Title3  m smith inputB:
                    Device1
                    Device2
                    Device3

Have the following php, along with some HTML inside a table, to transliterate into wpdb:

    $sqlSelect = "SELECT * FROM titles WHERE `active` = 0 ORDER BY author, title";
    $myquery = $mysqli->query ($sqlSelect);
    if ($myquery = $mysqli->query ($sqlSelect)) {
      $result = array();
      while($row = mysqli_fetch_assoc($myquery)){
        $tmp = array();
        foreach (array('device1', 'device2', 'device3', 'device4') as $key) {
          if (!isset($tmp[$row[$key]])) $tmp[$row[$key]] = array();
          $tmp[$row[$key]][] = $key;
        }
        $result[$row['title'] . "</td><td>" . $row['author']] = $tmp;
       }

      $max = 0;
      foreach ($result as $data => $inputs) {
        $max = max($max, count($inputs));
      }
      // looping rows begins here
      foreach ($result as $data => $inputs) {
        print('<tr><td>' . $data . '</td>');
        foreach ($inputs as $input => $devices) {
          print('<td>' . $input . ':<br/>' . implode('<br/>', $devices) . '</td>');
        }
    //next two lines are for displaying no cells where there is no $data
        for ($i = 0; $i < $max - count($inputs); ++$i) {
          print('<td class="db"></td>');
        }

        print('</tr>');

Tried this:

    $mydb = new wpdb('user','password','database','server');
    $results = $mydb -> get_results("SELECT * FROM titles WHERE `active` = 1 ORDER BY author, title");
    $result = array();
    while ($result = $row) { //this doesn't work; and only one row without data except all devices displays without it 
        $tmp = array(); //ditto to the end

WordPress doesn't seem to like $data. Is there a way to do this with wpdb?

  • You set result to an empty array and then try to loop over it so it won't have anything to show. $row isn't defined either. – Andy Macaulay-Brook Oct 1 '16 at 10:46
1

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...

  • Thanks. This is worth trying. Want to transition to wpdb in order to use native WP functions on the page. This is a project of transitioning from Drupal to WP and making it more user-friendly for my co-workers. – motorbaby Oct 1 '16 at 0:18
  • Edited to use wpdb. As you can see most of the code doesn't change. – adelval Oct 1 '16 at 9:58
  • haha, glad it helped :-) No big deal, but would you care to upvote the answer and mark it as an answer? – adelval Oct 4 '16 at 9:44
  • It looks a little weird, but it works! Thank you. I can't vote the answer up, yet, because I only have a reputation of 1, since I signed up just to ask the question. But I think I can upvote your answer if someone upvotes my question. – motorbaby Oct 6 '16 at 21:18
  • You can still mark it as an answer meta.stackexchange.com/questions/5234/… even if you cannot upvote it. Anyway I upvoted the question. – adelval Oct 8 '16 at 10:45
0

Got it working by installing the WP DB Driver plugin and skipping wpdb altogether. However, the page can only retrieve header and footer templates. The mysqli query seems incompatible with WordPress codex.

  • Would still like to find a wpdb solution. – motorbaby Sep 30 '16 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.