Combine Columns - server_processing.php

Teunis

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!

Teunis

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, )
);

allan

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

Allan

Teunis

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, )
);

tangerine

There's an example here:
https://datatables.net/examples/advanced_init/column_render.html

Teunis

Perfect! Thanks @tangerine

Any chance on doing this server side too?

allan

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

Allan

justhans

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' ),