Combine Columns - server_processing.php

Combine Columns - server_processing.php

TeunisTeunis Posts: 4Questions: 1Answers: 0

Hey all,

I'm looking for a way to combine a few columns from my database into a single one.

Adres_Street
Adres_Number
Adres_Bus
Adres_Code
Adres_City

These are 4 different columns, that I want to read from my database. They all work seperate, but I want to get them in one column on my website.

Adres_Street Adres_Number Adres_Bus / Adres_Code Adres_City

The Adres_Bus part should only show if it's not NULL

I've tried modifying the following part in server_processing.php, but keep getting syntax errors

$columns = array(
    array( 'db' => 'Firm', 'dt' => 0 ),
    array( 'db' => 'Adres_Street', 'dt' => 1 ),  << tried changing this in various ways
    array( 'db' => 'Phone', 'dt' => 2 ),
    array( 'db' => 'Website', 'dt' => 3 ),
    array( 'db' => 'Contact', 'dt' => 4,    ),
    array( 'db' => 'Status', 'dt' => 5, )
);

Ideal solution for me would be to be able to alter it and make a single $variable that I can insert into the $column array.
For instance:

$CombinedColumn = $Adres_Street . " " . $Adres_Number . " " . $Adres_Bus . " / " . $Adres_Code . " " . $Adres_City

Hope someone can help me out!

This question has an accepted answers - jump to answer

Answers

  • TeunisTeunis Posts: 4Questions: 1Answers: 0

    Found this SO post... but I keep getting errors.

    DataTables warning: table id=datatable - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`, ' ', `Adres_Nummer`, ' ', `Adres_Postcode`, ' ', `Adres_Plaats`)`, `Telefoon`' at line 1
    

    My current code is

    $columns = array(
        array( 'db' => 'Firmanaam', 'dt' => 0 ),
        array( 'db' => "CONCAT(`Adres_Straat`, ' ', `Adres_Nummer`, ' ', `Adres_Postcode`, ' ', `Adres_Plaats`)", 'as' => "address", 'dt' => 1 ),
        array( 'db' => 'Telefoon', 'dt' => 2 ),
        array( 'db' => 'Website', 'dt' => 3 ),
        array( 'db' => 'Contactpersoon', 'dt' => 4, ),
        array( 'db' => 'Status', 'dt' => 5, )
    );
    
    
  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    I would suggest you use a data renderer to combine multiple SQL columns into a single column.

    Allan

  • TeunisTeunis Posts: 4Questions: 1Answers: 0

    How would I implement this in my current page?

    Snippet from the table part:

                    <div class="row">
                        <div class="col-12">
                            <div class="card-box">
                                <h4 class="m-t-0 header-title"><b>Default Example</b></h4>
                                <p class="text-muted font-13 m-b-30">
                                    DataTables has most features enabled by default, so all you need to do to use it with your own tables is to call the construction function: <code>$().DataTable();</code>.
                                </p>
    
                                <table id="datatable" class="table table-bordered">
                                    <thead>
                                    <tr>
                                        <th>Firmanaam</th>
                                        <th>Adres</th>
                                        <th>Telefoon</th>
                                        <th>Website</th>
                                        <th>Contactpersoon</th>
                                        <th>Status</th>
                                    </tr>
                                    </thead>
    
                                    <tbody>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div> <!-- end row -->
    
            <script type="text/javascript">
                $(document).ready(function() {
                    $('#datatable').DataTable( {
                        "responsive": true,
                        "processing": true,
                        "serverSide": true,
                        "ajax": "/scripts/server_processing.php"
                    } );
                } );
            </script>
    

    server_processing.php

    $columns = array(
        array( 'db' => 'Firmanaam', 'dt' => 0 ),
        array( 'db' => 'Adres_Straat', 'dt' => 1 ),
        array( 'db' => 'Telefoon', 'dt' => 2 ),
        array( 'db' => 'Website', 'dt' => 3 ),
        array( 'db' => 'Contactpersoon', 'dt' => 4, ),
        array( 'db' => 'Status', 'dt' => 5, )
    );
    
    
  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓
  • TeunisTeunis Posts: 4Questions: 1Answers: 0

    Perfect! Thanks @tangerine

    Any chance on doing this server side too?

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    Yes, you should be able to do this with server-side processing as well.

    Allan

  • justhansjusthans Posts: 1Questions: 0Answers: 0

    Although https://datatables.net/examples/advanced_init/column_render.html does render correctly, the problem comes in when you want to search/filter while using server side processing.

    What worked for me when using server_processing.php was:

    array( 'db' => 'CONCAT(Lastname, ", ", Firstname)', 'dt' => 1, 'field' => 'Fullname', 'as' => 'FullName' ),
    
This discussion has been closed.