datatable in php function and swich case

datatable in php function and swich case

eleonoraeleonora Posts: 5Questions: 1Answers: 0
edited March 2020 in Free community support

Hi
i have this function in PDO

function lista_candidati() {
    global $conn;
    $out='<table id="example" class="table display" style="width:100%;">';
    $out.='<thead>';
    $out.='<tr><th>Candidato</th><th>Città</th><th>Banca</th><th>Cluster</th><th>Stato Trattativa</th><th>Trattativa condotta da</th><th>N° cellulare</th><th>Modifica</th><th>Elimina</th></tr>';
    $out.='</thead>';
    $out.='<tbody>';
    $sql = 'SELECT persone.*, comuni.nome AS comune,  comuni.id AS comune_id, banche_mue.nome  AS nbanca FROM persone ';
    $sql .= 'INNER JOIN banche_mue ON persone.id_banca = banche_mue.id ';
    $sql .= 'INNER JOIN comuni ON banche_mue.id_comune = comuni.id  ';
    $sql .= 'ORDER BY persone.cognome ASC';  //LIMIT '.$primo.','.$perpage.'
    //$sql = 'SELECT * FROM persone ORDER BY id ASC LIMIT '.$perpage.' OFFSET '.$primo.'';  //LIMIT '.$primo.','.$perpage.'
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        $out.='<tr>';
        $out.='<td><a href="?azione=dettaglio_candidati&id='.$row['id'].'">'.$row['cognome'].'&nbsp; &nbsp;'.$row['nome_persone'].'</a></td>';
        $out.='<td>'.$row['comune'].'</td>';
        $out.='<td>'.$row['nbanca'].'</td>';
        $out.='<td>'.$row['cluster'].'</td>';
        $out.='<td>'.$row['stato_trattativa'].'</td>';
        $out.='<td>'.$row['trattiva'].'</td>';
        $out.='<td>'.$row['telefono'].'</td>';
        //$out.='<td><a href="?azione=dettaglio_candidati&id='.$row['id'].'"><span class="glyphicon glyphicon-zoom-in"></span></a></td>';
        $out.='<td><a href="?azione=form_candidati&id='.$row['id'].'"><span class="glyphicon glyphicon-pencil"></span></a></td>';
        $out.='<td><a href="?azione=elimina_candidati&id='.$row['id'].'"><span class="glyphicon glyphicon-remove"></span></a></td>';
        $out.='</tr>';
        }
$out.='</tbody>';
$out.='</table>';
return($out);

}

then there is a swich case, which inserts the content of the function into the $contenuto

switch ($azione) {
    case 'lista_candidati':
        $contenuto = lista_candidati();
        break;

The $contenuto is inserted in an index page that shows the function query.

I would like to integrate to this datatable system but I have encountered the following error

DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

this is the script

<script>
    $(document).ready(function() {
    $('#example').DataTable( {
       dom: "Bfrtip",
"processing": true,
"serverSide": true,
"ajax": {
"url": "index.php",
"type": "POST"
},
        buttons: [
            'copyHtml5',
            'excelHtml5',
            'csvHtml5',
            'pdfHtml5'
        ]
    } );
} );
    </script>

Thank you for any support

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Answers

  • eleonoraeleonora Posts: 5Questions: 1Answers: 0

    function lista_candidati() { global $conn; $out='<table id="example" class="table display" style="width:100%;">'; $out.='<thead>'; $out.='<tr><th>Candidato</th><th>Città</th><th>Banca</th><th>Cluster</th><th>Stato Trattativa</th><th>Trattativa condotta da</th><th>N° cellulare</th><th>Modifica</th><th>Elimina</th></tr>'; $out.='</thead>'; $out.='<tbody>'; $sql = 'SELECT persone.*, comuni.nome AS comune, comuni.id AS comune_id, banche_mue.nome AS nbanca FROM persone '; $sql .= 'INNER JOIN banche_mue ON persone.id_banca = banche_mue.id '; $sql .= 'INNER JOIN comuni ON banche_mue.id_comune = comuni.id '; $sql .= 'ORDER BY persone.cognome ASC'; //LIMIT '.$primo.','.$perpage.' //$sql = 'SELECT * FROM persone ORDER BY id ASC LIMIT '.$perpage.' OFFSET '.$primo.''; //LIMIT '.$primo.','.$perpage.' $stmt = $conn->prepare($sql); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)){ $out.='<tr>'; $out.='<td><a href="?azione=dettaglio_candidati&id='.$row['id'].'">'.$row['cognome'].'&nbsp; &nbsp;'.$row['nome_persone'].'</a></td>'; $out.='<td>'.$row['comune'].'</td>'; $out.='<td>'.$row['nbanca'].'</td>'; $out.='<td>'.$row['cluster'].'</td>'; $out.='<td>'.$row['stato_trattativa'].'</td>'; $out.='<td>'.$row['trattiva'].'</td>'; $out.='<td>'.$row['telefono'].'</td>'; //$out.='<td><a href="?azione=dettaglio_candidati&id='.$row['id'].'"><span class="glyphicon glyphicon-zoom-in"></span></a></td>'; $out.='<td><a href="?azione=form_candidati&id='.$row['id'].'"><span class="glyphicon glyphicon-pencil"></span></a></td>'; $out.='<td><a href="?azione=elimina_candidati&id='.$row['id'].'"><span class="glyphicon glyphicon-remove"></span></a></td>'; $out.='</tr>'; } $out.='</tbody>'; $out.='</table>'; return($out); }
  • eleonoraeleonora Posts: 5Questions: 1Answers: 0
    <script>
        $(document).ready(function() {
        $('#example').DataTable( {
           dom: "Bfrtip",
    "processing": true,
    "serverSide": true,
    "ajax": {
    "url": "index.php",
    "type": "POST"
    },
            buttons: [
                'copyHtml5',
                'excelHtml5',
                'csvHtml5',
                'pdfHtml5'
            ]
        } );
    } );
        </script>
    
  • colincolin Posts: 15,116Questions: 1Answers: 2,583

    The first place to start would be the diagnostic suggestions contained in the link in the error message. How did you get on with that?

    Colin

  • eleonoraeleonora Posts: 5Questions: 1Answers: 0

    yes I read the link but I don't use json but I create the table directly on php

  • eleonoraeleonora Posts: 5Questions: 1Answers: 0

    ok now it works
    i changed the script

    $(document).ready(function() {
        $('#example').DataTable( {
           dom: "Bfrtip",
            buttons: [
                'copyHtml5',
                'excelHtml5',
                'csvHtml5',
                'pdfHtml5'
            ]
        } );
    } );
    
  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited March 2020

    You have "serverSide": true, which expects the server script to use the protocaol described here:
    https://datatables.net/manual/server-side

    Its used for large data sets to reduce the amount of data fetched to improve the page's speed. See this FAQ.

    The Ajax docs discuss the expected formats when using the ajax option. Since you are not returning JSON format that is why you are getting the error. Instead of returning HTML you could return a JSON formatted string and have Datatables build the table. However if you wish to return HTML format then I suggest you call the PHP script outside of Datatables, build the HTML table then initialize Datataables removing these options:

    "processing": true,
    "serverSide": true,
    "ajax": {
    "url": "index.php",
    "type": "POST"
    },
    

    Kevin

This discussion has been closed.