Fastest way to take a table with 40 fields and use it with Datatables (PHP preferred at the moment)

Basementjack

Hi, I have a mysql table with about 40 fields, no relationships.
Whats the fastest way to use datatables with that?

I know I am being lazy, but I don't want to type out 40 or so field names- what I have now is just a proof of concept, trying to show someone that using datatables.net and a backend DB can be faster to develop than using SharePoint.

allan

If the field names can be derived programmatically, then you could simply add them one at a time:

for ( $i=0 ; $i<10 ; $i++ ) {
  $editor->field( new Field( 'field_'.$i ) );
}

If they can't be derived that way, then you could possibly query the database for the column names and then loop over them that way - but it wouldn't provide the option to add any validators, etc - unless you also programmed that in, by which time, you'd have been better just creating the 40 fields the "normal" way :-)

Regards,
Allan

Basementjack

ok here's what I did to pull some field names (Posting it in case it helps someone else)

 private function RenderHTMLForDataTables($table)
    {
          $result = $this->GetResult($table);

            //get the field names and write out the table header
            
            $fieldinfo = $result->fetch_fields();
            foreach ($fieldinfo as $val) {
                $str.= "<th>".$val->name."</th>".PHP_EOL; 
            }
           echo $str;
    }

    private function RenderJavaScriptForDataTables($table)
    {
          $result = $this->GetResult($table);

            //get the field names and write out the table header
            $str = "columns: [".PHP_EOL;
            
            $fieldinfo = $result->fetch_fields();
            foreach ($fieldinfo as $val) {
                $str.= "{ data: '".$val->name."', name: '".$val->name."'},".PHP_EOL; 
            }
            $str .= "],".PHP_EOL;
           echo $str;

    }

    private function GetResult($table)
    {
        $result = mysqli_query($this->connection, "Select * from $table limit 1");
                if (!$result) {
                    echo "Sql error" . mysqli_error($this->connection) . "->" . mysqli_errno($this->connection);
                    exit;
                }
        return $result;
    }

The snippet above is part of a larger class - it's not quite usable as is without the rest, but it shows the technique used to pull the field names from the DB and create the needed HTML and Javascript snippets to copy/paste into your existing code.

Basementjack

After this, I'm running into the 414 request too large issue - I'm using an extension for laravel that uses GET instead of POST, so I'll need to figure out how to change that...

Basementjack

Ok Got the post part figured out, that was here on the samples.
In Laravel, I also got a csrf error, I fixed that by excluding the path in VerifyCsrfToken.php

allan

Sounds good - thanks for posting back with your findings!

Allan