Using Server Side Script: How Can I Make A Link (URL) From Two Columns (Same Table)

gbyrd

I know that I can not be the only person trying to sort this out but surprisingly there does not seem to be much in the way of information about how to do this.

I have one table that stores the links to news articles, the title of that article, and other information not relevant to this post.

I want to display the title of the article so that it is a link using the correlating url to the article.

EXAMPLE: <a href="url_from_database">Title_from_database</a>

Here is what my JS looks like:

$(document).ready(function() {
    $('#example').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "server_processing.php",

        columns: [

            {
                className: 'title', 
                data: 'title',
                render: function(data, type) {
                    if (type === 'display') {

                        return '<strong class="title">' + data + '</strong> ';
                    }

                    return data;
                }
            },

            {
                className: 'url', 
                data: 'url',
                render: function(data, type) {
                    if (type === 'display') {

                        return '<a href="' + data + '" class="url">' + data + '</a> ';
                    }

                    return data;
                }
            }
        ]
    });
});

I am just at a complete stand still for months now ... There must a way to accomplish what I am trying to accomplish. Any help would be greatly appreciated.

kthorngren

The columns.render has more parameters than data, type. The third is row which allows you to access the row data. See this example for using the row parameter. Note the example is using arrays (row[3]) but you are using objects so you would access the row data using object syntax, for example row.title .

Kevin

gbyrd

So you are saying this then?

$(document).ready(function() {
    $('#example').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "server_processing.php",

        columns: [

            {
                className: 'title', 
                row.title: 'title',
                render: function(row, type) {
                    if (type === 'display') {

                        return '<strong class="title">' + row.title + '</strong> ';
                    }

                    return row.title;
                }
            }

        ]
    });
});

kthorngren

So you are saying this then?

Its unclear exactly what you want in the URL. But what I'm saying is if you want to access data from another column you need to use the third row parameter. For example:

$(document).ready(function() {
    $('#example').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "server_processing.php",
 
        columns: [
 
            {
                className: 'title',
                data: 'title',
            },
            {
                className: 'url',
                data: 'url',
                render: function(data, type, row) {
                    if (type === 'display') {
 
                        return '<a href="' + data + '" class="url">' + row.title + '</a> ';
                    }
 
                    return data;
                }
            }
 
        ]
    });
});

If you still need help please build a test case showing what you have so we can help you display what you want.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

gbyrd

@kthorngren - you replied to me back in December with pretty much the same answer and I haven't posted back because I didn't want to be a nuisance because I was just grateful that anybody replied, but Ifeel like I have just been guessing for months now ... placing this word here, trying this code, trying that code and I am still at the same place.

I've been using DataTables for a lot of years now so I am familiar with how it works but I have never had a need until now to make a link from a large database.

I just can not believe that this need is so rare that it doesn't warrant an example somewhere in the docs.

The columns.render example doesn't seem to really get to what I am doing. For example, if I use the following code all I get returned is "undefined". This was as far as I got back in December .... "undefined".

$('#example').dataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": "server_processing.php",
        "columnDefs": [ {
        "targets": 0,
        "data": "url",
        "render": function ( data, type, row, meta ) {
            return '<a href="'+data+'">'+data+'</a>';
        }
    } ]
} );

})(jQuery);

kthorngren

I just can not believe that this need is so rare that it doesn't warrant an example somewhere in the docs.

The forth example in the columns.render docs show an example of creating a URL.

Your example code works here:
http://live.datatables.net/hajijehu/1/edit

What is the value of data for that column?

Kevin

kthorngren

Do you have another columns or columnDefs defined that might be overwriting your above code snippet? Please post your full Datatables init code.

Kevin

gbyrd

Right now this is it ... I am just trying to get a link created before I add the title.

Again, my DB has 2 columns: 1 column (title) is the Title of a web page and the other column (url) is the url to that webpage. I just want the title display'd so that it is a link.

Here is all the init code I am using so far

$('#example').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "server_processing.php",

        "columnDefs": [ {
            "targets": 0,
            "data": "url",

            "render": function ( data, type, row, meta ) {

                return '<a href="'+row.url+'">'+row.url+'</a>';
            }
        } ]


    });

NOTE: The above code is incomplete because it it only trying to get a success at making the link properly (as it is now, it returns "undefined")

kthorngren

What is the value of data for that column?

Use console.log(data); on line 11 to see the values of data.

The column is defined with "data": "url",. The render function data parameter will be the value of the url property of that column. You don't need to use row.url but you can since it contains all the values for that row.

Use the browser's network inspector tool to get the JSON response. Paste the full response here so we can take a look.

Kevin

gbyrd

Here is what gets returned: When I test to make sure its valid JSON it says it is valid (https://jsonformatter.curiousconcept.com/#)

{"draw":1,"recordsTotal":16608,"recordsFiltered":16608,"data":[["\t\t10 Miles Of Cars Line Up For Trump  And A Raucous Night In A Small Town\t"],["\t\t10 Years After Gosnells House Of Horrors  It Could Still Happen Again\t"],["\t\t12 Ways For Trump To Bomb The Battlefield While Biden Claims He Won\t"],["\t\t1960s Riots Foreshadow Todays Communist Weaponization Of Black Pain\t"],["\t\t2020 Shatters Annual Gun Sales Record  An Estimated 17 Million Sold Year-To-Date\t"],["\t\t3 Breakdowns In Standards That Explain The Atlantics Trump Hit Piece\t"],["\t\t3 Reasons Election Forecasts Made False Projections Favoring Joe Biden\t"],["\t\t3 Ways Donald Trumps Debate Performance Will Affect The Race\t"],["\t\t400 Years Later The Mayflower Compact Matters Now More Than Ever\t"],["\t\t5 Big Problems With NYTs Investigation Of Amy Coney Barretts Children\t"]]}

kthorngren

Your row data is array based not objects. The columns.data is typically used for object data.

    "data": [
        ["\t\t10 Miles Of Cars Line Up For Trump  And A Raucous Night In A Small Town\t"],
        ["\t\t10 Years After Gosnells House Of Horrors  It Could Still Happen Again\t"],
....
]

See the Data Srouces docs for more details. Aloe see the Ajax with arrays example.

Here is a working test case using your data:
http://live.datatables.net/ziqohepa/1/edit

Kevin

gbyrd

Well then this creates a new issue ...

1. I used the "Server-side" example because I need to process the data on the server (db is too large)
2. I didn't purposefully make it "array" based as opposed to "object" based ... I just copy/pasted the example code.

So, I need to either make the data object based or I need to see how to get the "data" to feed to the table.

NOTE: I can successfully get all the data to the table as raw text no problem, its just making a link of the data. There seems to be no examples of that.

In your example form here: http://live.datatables.net/ziqohepa/1/edit

You have manually entered the data in and it feeds the table, but it is not possible for me to manually do that because the DB is growing every day by hundreds.

gbyrd

Here is the complete server_processing.php I am using ...

<?php

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simple to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

// DB table to use
$table = 'links';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'title', 'dt' => 0 ),
    array( 'db' => 'url',  'dt' => 1 ),


);

// SQL server connection information
$sql_details = array(
    'user' => 'xxx',
    'pass' => '*xxx',
    'db'   => 'xxx',
    'host' => '108.xxx.xx.xxx'
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( 'ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

kthorngren

NOTE: I can successfully get all the data to the table as raw text no problem,

Is this without using columns or columnDefs definitions for the array based data?

its just making a link of the data. There seems to be no examples of that.

There is an example in the dos I linked to. I provided both a server side example and an example with the data example you provided.

You have manually entered the data in and it feeds the table, but it is not possible for me to manually do that because the DB is growing every day by hundreds.

Did you try my example? What happens?

Your data source, whether Javascript as in my example or ajax, will behave the same when it comes to using column and columns.render definitions. I updated my example to use array based server side processing:
http://live.datatables.net/ziqohepa/2/edit

I need to either make the data object based or I need to see how to get the "data" to feed to the table.

Here is an example SSP with objects. Click on the Server-side script tab.

Kevin

gbyrd

I am sorry, there seems to be a breakdown in communication and will take all the blame.

You keep saying that there are examples of turning data into a link but nothing you have shown me does that, except for making a download link, This is not what I want.

I am frustrated because the simplicity of what I want seems to be becoming more complicated than it needs to be.

I have not written any code, all I have done is try to take the examples given and make it work for me.

There is nothing special about my data, one column is a word(s) and the other column is a url. All I really want is for the word(s) to be a link from the correlating url in the same table.

Arrays, objects, rows, data ... its all getting blurry because I keep getting told to look at a certain example that does not really fit my need.

Even in your example, sure, I could manually enter in all the data in a var, but that would take daily or hourly maintenance on my part and would defeat the purpose.

If you go here maybe you can get a better grasp of what I am trying to accomplish
https://garysbundle.com

Again, please forgive me if I am coming across obtuse its just that I have tried multiple dozens, perhaps hundreds, of combinations code snippets for months and am still stuck at the same place.

gbyrd

You ask "Did you try my example? What happens?"

Yes, you can go here https://garysbundle.com and look at the table at the bottom of the page (2nd table)
and you can see that your example produced the same results on my page as in your example.
But ... that is not really helping me.

Should I be expected to make a javascript file with 16,000 data entries in it and then add more each day?

var data = [
        ["\t\t10 Miles Of Cars Line Up For Trump  And A Raucous Night In A Small Town\t"],
        ["\t\t10 Years After Gosnells House Of Horrors  It Could Still Happen Again\t"],
        ["\t\t12 Ways For Trump To Bomb The Battlefield While Biden Claims He Won\t"],
        ["\t\t1960s Riots Foreshadow Todays Communist Weaponization Of Black Pain\t"],
        ["\t\t2020 Shatters Annual Gun Sales Record  An Estimated 17 Million Sold Year-To-Date\t"],
        ["\t\t3 Breakdowns In Standards That Explain The Atlantics Trump Hit Piece\t"],
        ["\t\t3 Reasons Election Forecasts Made False Projections Favoring Joe Biden\t"],
        ["\t\t3 Ways Donald Trumps Debate Performance Will Affect The Race\t"],
        ["\t\t400 Years Later The Mayflower Compact Matters Now More Than Ever\t"],
        ["\t\t5 Big Problems With NYTs Investigation Of Amy Coney Barretts Children\t"]

          x 16,000 more entries
    ];

I thought the whole purpose of using the server-side script was so you wouldn't have to do stuff like that ...

Also, the links created point to an undefined page ...

I am not blaming you and am grateful for your time and direction, but your example does not seem to address my question and problem. I must not be doing a good job of explaining what I want.

kthorngren

You keep saying that there are examples of turning data into a link but nothing you have shown me does that, except for making a download link, This is not what I want.

The examples may not address your specific need but are there to provide an idea of what can be done. The link you provided is very helpful in understanding what you want. I did provide an example and explain how to access data in other columns in the row. I also provided many test cases, based on your code snippets, to show how URLs can be created. In fact I provided this example:

return '<a href="' + data + '" class="url">' + row.title + '</a> '

Although its not what you want since you are using arrays and I placed it in the URL column instead of the Title column. The concept of the example is the same.

Even in your example, sure, I could manually enter in all the data in a var, but that would take daily or hourly maintenance on my part and would defeat the purpose.

As I said the source of the data doesn't matter. I did this to show how it would work whether sourced from Javascript or Ajax. I did the same thing with the data from your page:
http://live.datatables.net/judijapo/1/edit

Basically the code is this:

    "columnDefs": [ {
      "targets": 1,
      "render": function ( data, type, row, meta ) {
        return '<a href="'+row[2]+'">'+data+'</a>';
      }
    } ]

Nothing has changed except the href is pulling from row[2] which is the column with the link. It uses data to show the title. You can hide column 2 with columns.visible which I added to the example.

Kevin

gbyrd

Ok ... I am starting to see it now ...

How does it know where to get the data though?

In your example, you show

  var data = [["2","Coronavirus FLASHBACK- President Trump Establishes Coronavirus Response Task Force - Headed by HHS Secretary Alex Azar","https:\/\/theconservativetreehouse.com\/2020\/01\/30\/president-trump-establishes-coronavirus-response-task-force-headed-by-hhs-secretary-alex-azar\/"],["3","Senator Hawley-  The House Managers \"Just Dont Have a Case\"","https:\/\/theconservativetreehouse.com\/2020\/01\/29\/senator-hawley-the-house-managers-just-dont-have-a-case\/"],["4","Flynn Case Update - Flynn Files Motion to Dismiss  Declaration of Plea Reversal - DOJ Files Revised Sentence Recommendation for Probation Only","https:\/\/theconservativetreehouse.com\/2020\/01\/29\/flynn-case-update-flynn-files-motion-to-dismiss-declaration-of-plea-reversal-doj-files-revised-sentence-recommendation-for-probation-only\/"],["5","Chief Justice John Roberts Nixes Rand Pauls Question on Whistleblower","https:\/\/www.breitbart.com\/politics\/2020\/01\/30\/chief-justice-john-roberts-shuts-down-rand-pauls-question-on-alleged-whistleblower\/"],["6","Day 1 of Impeachment Q&A-  Schiff Undermines His Case for Witnesses","https:\/\/www.breitbart.com\/politics\/2020\/01\/30\/day-1-of-impeachment-qa-schiff-undermines-his-case-for-witnesses\/"],["7","Graham-  There Are 53 Republican Votes to Call Hunter Biden","https:\/\/www.breitbart.com\/clips\/2020\/01\/29\/graham-there-are-53-republican-votes-to-call-hunter-biden\/"],["8","Washington Post Wrote About Hunter Biden  Burisma 3 Days Before Ukraine Call","https:\/\/www.breitbart.com\/the-media\/2020\/01\/30\/washington-post-wrote-about-hunter-biden-burisma-3-days-before-ukraine-call\/"],["9","Poll-  Medicare for All Unpopular in Battleground States - Even Among Democrats","https:\/\/www.breitbart.com\/politics\/2020\/01\/29\/poll-medicare-for-all-unpopular-in-battleground-states-even-among-democrats\/"],["10","Parents Convicted in First Female Genital Mutilation Case in Ireland","https:\/\/www.breitbart.com\/europe\/2020\/01\/30\/parents-convicted-in-first-female-genital-mutilation-case-in-ireland\/"],["11","Ann Coulter-  Fully Automatic Media","https:\/\/www.breitbart.com\/politics\/2020\/01\/29\/ann-coulter-fully-automatic-media\/"]];

But how does this data get created?
Does it create the data itself?
Do I have to insert some code there instead?

kthorngren

The data comes from the JSON response of tour ajax request. All you need to do is copy the columnDefs code snippet above into your Datatables initialization code. Datatables will take care of the rest

Kevin

gbyrd

Wow .... You definitely deserve a pay raise for helping me though this ... Seriously!

So now I want to hide the last column (the url) but if I use "visible:false,"
It removes the href link for the title.

$(document).ready(function() {
        $('#example').DataTable( {
            "processing": true,
            "serverSide": true,
            "ajax": "server_processing.php",
            "columnDefs": [ {
            "visible": false,
            "targets": 0,
            "render": function ( data, type, row, meta ) {
                return '<a href="'+row[1]+'">'+data+'</a>';
  }
} ]
        } );
    } );

NOTE: I have already removed the "ID" as it is not needed ...

gbyrd

Nevermind ... evidently it was a browser cache issue