Make HTML table from array returned from execute_db in PHP - entries duplicated

Question

I want to populate a html table from the results of a SQL query, the fields included will vary depending on choices the user makes so I want to autmatically set the headings etc based on what is in the results of the query. I found the array_to_table function on the website https://inkplant.com/code/array-to-table and it does what I want except the data is duplicated:-

Expected result

Formation       Play    
A               SW
S               DL

Actual Result

Formation   0   Play    1
A           A   SW      SW
S           S   DL      DL

I think it may be be to do with the type of array I'm creating but I'm unsure how to alter the code to generate my expected result. I'm not sure if I should create the array in a different way or whether (or how) I should change the function to work with the type of array I have.

#SQL for creating example table

CREATE TABLE `example` ( `id` INT NOT NULL AUTO_INCREMENT , `form` VARCHAR(10) NOT NULL , `playcall` VARCHAR(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

#Sample data

INSERT INTO `example` (`id`, `form`, `playcall`) VALUES (NULL, 'A', 'SW'), (NULL, 'S', 'DL');

#mycode

$sql = "SELECT `form` as 'Formation',`playcall` as 'Playcall' FROM `example` ORDER BY `form` DESC";       
$res = execute_db($sql3 $conn);
   
$caption = "Example table";
     
echo array_to_table($res,$caption);

#function

function array_to_table($data,$caption,$args=false) {
    if (!is_array($args)) { $args = array(); }
    foreach (array('class','column_widths','custom_headers','format_functions','nowrap_head','nowrap_body','capitalize_headers') as $key) {
        if (array_key_exists($key,$args)) { $$key = $args[$key]; } else { $$key = false; }
    }
    if ($class) { $class = ' class="'.$class.'"'; } else { $class = ''; }
    if (!is_array($column_widths)) { $column_widths = array(); }

    //get rid of headers row, if it exists (headers should exist as keys)
    if (array_key_exists('headers',$data)) { unset($data['headers']); }

    $t="<table style='width:40%'  class='w3-table w3-striped w3-bordered'>";
    $t .= $caption;
    
    $i = 0;
    foreach ($data as $row) {
        $i++;
        //display headers
        if ($i == 1) { 
            foreach ($row as $key => $value) {
                if (array_key_exists($key,$column_widths)) { $style = ' style="width:'.$column_widths[$key].'px;"'; } else { $style = ''; }
                $t .= '<col'.$style.' />';
            }
            $t .= '<thead><tr>';
            foreach ($row as $key => $value) {
                if (is_array($custom_headers) && array_key_exists($key,$custom_headers) && ($custom_headers[$key])) { $header = $custom_headers[$key]; }
                elseif ($capitalize_headers) { $header = ucwords($key); }
                else { $header = $key; }
                if ($nowrap_head) { $nowrap = ' nowrap'; } else { $nowrap = ''; }
                $t .= '<td'.$nowrap.'>'.$header.'</td>';
            }
            $t .= '</tr></thead>';
        }

        //display values
        if ($i == 1) { $t .= '<tbody>'; }
        $t .= '<tr>';
        foreach ($row as $key => $value) {
            if (is_array($format_functions) && array_key_exists($key,$format_functions) && ($format_functions[$key])) {
                $function = $format_functions[$key];
                if (!function_exists($function)) { custom_die('Data format function does not exist: '.htmlspecialchars($function)); }
                $value = $function($value);
            }
            if ($nowrap_body) { $nowrap = ' nowrap'; } else { $nowrap = ''; }
            $t .= '<td'.$nowrap.'>'.$value.'</td>';
        }
        $t .= '</tr>';
    }
    $t .= '</tbody>';
    $t .= '</table>';
    return $t;
}

EDIT: Based on the tip from https://stackoverflow.com/users/13851118/dhnwebpro I dumped the array to see what it contained:-

Array

(
    [0] => Array
        (
            [Formation] => A
            [0] => A
            [Playcall] => SW
            [1] => Sw
        )

    [1] => Array
        (
            [Formation] => S
            [0] => S
            [Playcall] => DL
            [1] => DL
        )

)

EDIT2: I changed the format of the query and got it working:-

$res3 = $conn->query($sql3)->fetchAll(PDO::FETCH_ASSOC);

Fetch both was indeed the default and I was able to override that with the above statement.

Does `execute_db()` use PHP Data Objects? PDO defaults to `PDO::FETCH_BOTH` and it looks like the array of `$res` is getting both the associative and indexed values from PDO. You may need to change it to `PDO::FETCH_ASSOC` or `PDO::FETCH_NUM`. Without seeing the code from `execute_db` I have no idea how to advise you to do that. — dhnwebpro, Jul 18 '20 at 06:00
It is PDO yes, execute_db() is provided by the framework so I'll need to go digging - thanks for the pointer. — deep64blue, Jul 18 '20 at 06:06
You might try this answer to remove the numeric keys. If it fixes it then you could go digging in the framework. It'll at least show you if you're barking up the right tree. https://stackoverflow.com/questions/11042536/remove-all-elements-of-an-array-with-non-numeric-keys — dhnwebpro, Jul 18 '20 at 06:15
I think you should reformat the array first, and then put it into the function `array_to_table` as dhnwebpro suggested — Thien Huynh, Jul 18 '20 at 06:41

score 0 · Accepted Answer · answered Jul 18 '20 at 06:50

0

As @dhnwebpro said PDO was defaulting to PDO::FETCH_BOTH so I overrode it by changing the execution to:-

$res3 = $conn->query($sql3)->fetchAll(PDO::FETCH_ASSOC);

answered Jul 18 '20 at 06:50

deep64blue

268
4
16

Make HTML table from array returned from execute_db in PHP - entries duplicated

1 Answers1