Dysfunction DataTables with Bootstrap and PHP/MySQL

Dysfunction DataTables with Bootstrap and PHP/MySQL

(.....)(.....) Posts: 7Questions: 1Answers: 0
edited March 2023 in Free community support

Hello everyone.

I created a table, displaying all the data from a MySQL Database.

But i would like to have all these elements displayed, with a autocomplete search and a sort of the columns of my table.

Thanks to DataTables, i found how i can use this for my personal project, but it doesn't seem to work as i'd like it.

Actually, the table seems "cut" by the datatables hidden classes and in spite of that, the search and the sort are functional. Now, the "filter" input and the the "length" select are in the footer, what i wanted.

What i'm looking for now, it's to see my table closed like in the first example, with still the sort and search functions in the bottom.

So here's the code for my example

index.php

<?php
session_start();
// appel de la base de données
include('config.php');

<?php
>
<!DOCTYPE html>

    
        
        
        
        Les opérateurs
    
?>


    <body>
        <div class="container mt-4">
            <div class="row">
                <div class="col-md-12 mt-4">

                <?php if(isset($_SESSION['message'])) : ?>
                    <h5 class="alert alert-success"><?= $_SESSION['message']; ?></h5>
                <?php endif; ?>

                    <div class="card">
                        <div class="card-header">
                            <h3 class="text-center">Les opérateurs
                            </h3>
                                <a href="operateur_add.php" class="btn btn-primary float-start">Ajouter l'opérateur</a>
                                <!-- Récupération des paramètres dans le fichier gestion_operateurs_exports.php -->
                                <form action="gestion_operateurs_exports.php" method="post">                    
                                    <button type="submit" name="export" class="btn btn-success float-end">Exporter en CSV</button>
                                </form>
                        </div>

                        <!-- Création de la vue du tableau -->
                        <div class="card-body">
                            <table class="table table-bordered table-striped mx-auto text-center">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Identité</th>
                                        <th>Charge hebdomadaire</th>
                                    </tr>
                                </thead>
                                
                                <tbody>
                                    <?php 
                                    // On récupère les données contenues dans la BDD
                                        $query = "SELECT * FROM operateurs ORDER BY id ASC";
                                        $statement = $conn->prepare($query);
                                        $statement->execute();
                                        $statement->setFetchMode(PDO::FETCH_OBJ);
                                        $result = $statement->fetchAll();

                                        if($result) {
                                            foreach($result as $row) {
                                    ?>
                                            <!-- On affiche les résultats BDD dans la table -->
                                            <tr>
                                                <td><?= $row->id; ?></td>
                                                <td><?= $row->operateurs_identite; ?></td>
                                                <td><?= $row->operateurs_charge; ?></td>
                                                <td>
                                                    <!-- Bouton de MODIFICATION -->
                                                    <a href="operateur_edit.php?id=<?= $row->id; ?>" class="btn btn-warning rounded-pill">Modifier</a>
                                                </td>
                                                <td>
                                                    <!-- Bouton de SUPPRESSION -->
                                                    <form action="code.php" method="POST">
                                                        <button type="submit" name="delete_operateur" value="<?=$row->id;?>" class="btn btn-danger rounded-pill">Supprimer</button>
                                                    </form>
                                                </td>
                                            </tr>
                                    <?php
                                            }
                                        }
                                            else {       
                                    ?>
                                            <tr>
                                                <td colspan="3">Aucune donnée trouvée</td>
                                            </tr>
                                    <?php
                                        }
                                    ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    
                </div>
            </div>
        </div>
    </body>
</html>

<?php
    $_SESSION['search'] = "operateurs";
    require_once("vue/vue_form_search.php");

<?php
>
```
?>


**script.js**

```js
$(document).ready(function() {
    $('#table1').dataTable({
        "dom": 'rt<"bottom"lf>',
        responsive: true,
        language: {
            url: "datatables/media/french.json"
        },
        "pageLength": 10,
        "lengthMenu": [ 10, 25, 50, 100 ],
        //"info": true,
        "bPaginate": true,
        "bFilter": true,
        "LengthChange": true,
        "AutoWidth": true });
    });

style.css

/*
LE CORPS DE LA PAGE
*/
@font-face {
    font-family: 'Raleway';
    src: url(‘/fonts/Raleway-VariableFont_wght.ttf) format(‘truetype’);
    font-style: normal;
}

body {
background: -webkit-linear-gradient(to left, #dd5e89, #f7bb97);
background: linear-gradient(to left, #85777c, #f7bb97);
min-height: 100vh;
font-family: 'Raleway', sans-serif;
height: 100vh;
}


/* DataTable Header & Footer
**************************/
.dataTables_wrapper .dataTables_length,
.dataTables_wrapper .dataTables_filter{
    font-size: 11px;
    margin-top: 20px;
}

.dataTables_wrapper .dataTables_length label,
.dataTables_wrapper .dataTables_length select,
.dataTables_wrapper .dataTables_filter label,
.dataTables_wrapper .dataTables_filter input {
    font-size: 11px;
    border-radius: 25px;
}

.dataTables_wrapper .dataTables_paginate .paginate_button {
    padding: 0.1em 0.3em;
    font-size: 11px;
}

input[type=search]{
    width: 99.8125px;
    height: 20px;
}

And please take a look at the illustrations to understand more what my actual project looks like

And what i want my project looks like ... with the footer inside ...

Thanks in advance.

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

Answers

  • kthorngrenkthorngren Posts: 21,391Questions: 26Answers: 4,961

    Your thead has only three columns defined but your table has five columns. Datatables requires a th for each column. See the HTML docs for details.

    Please build a simple test case showing the styling issues you have so we can take a look and offer suggestions.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • (.....)(.....) Posts: 7Questions: 1Answers: 0
    edited March 2023

    Thanks for your answer.

    So, if i understand well, i have to replace all my <tr> by <th> ?
    Even for the columns with the buttons "Modify" (in yellow) and "Delete" (in red) ?

    I have only three columns with MySQL data, the other two are simply serve to modify or delete the operator.

  • kthorngrenkthorngren Posts: 21,391Questions: 26Answers: 4,961

    Datatables requires one th for each column to apply its sorting click handlers, etc. You will probably see errors in the browser's console.

    You don't need to replace anything. You need to add two th to your header, like this:

                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Identité</th>
                                        <th>Charge hebdomadaire</th>
                                        <th></th>
                                        <th></th>
                                    </tr>
                                </thead>
    

    Kevin

  • (.....)(.....) Posts: 7Questions: 1Answers: 0

    I just tried to add <th> just like you explained before ... well, it closes the table perfectly but it deletes the sort and search functions ...

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    That suggests that there is a Javascript error happening on the page. It would allow us to help you a lot faster if you could link to a page showing the issue please.

    If that isn't possible, can you show me the generated HTML (i.e. "View source") and any information that it shown on the browser's console.

    Allan

  • (.....)(.....) Posts: 7Questions: 1Answers: 0

    No more Datables classes, as you can see, with the <th> added.

  • kthorngrenkthorngren Posts: 21,391Questions: 26Answers: 4,961
    edited March 2023

    Allan asked to see the generated HTML table by using View Source. The error and screenshot suggests missing cells in the tbody.

    Kevin

  • (.....)(.....) Posts: 7Questions: 1Answers: 0


    Here it is.

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    Your first tr only has three cells. The others call have five. That's the issue. You can see in the screenshot that the first row is missing two cells.

    Allan

  • (.....)(.....) Posts: 7Questions: 1Answers: 0
    edited March 2023

    Hello everyone.

    It finally works yesterday with the correction you told me before bit this morning, these two functions disappeared ... can i show you the code ?

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    can i show you the code ?

    Sure. No need to ask.

    Allan

  • (.....)(.....) Posts: 7Questions: 1Answers: 0

    I don't believe but it works again !!

    I still don't know why and how, but it works.

    However, i remember i had yesterday the "Cannot reinitialise DataTable" each time i refreshed the page, maybe is this the cause ...

Sign In or Register to comment.