Rendering with values from other columns, and pre-selecting rows based on a cell value

OsakaWebbie

I have data like this (simplified):

    [
        {
            "songid":       "56",
            "title":   "Song A",
            "tagged":     "0",
            "audio":     "1",
            "chords":     "1"
        },
        {
            "songid":       "7",
            "title":   "Song B",
            "tagged":     "1",
            "audio":     "0",
            "chords":     "1"
        }
        {
            "songid":       "24",
            "title":   "Song C",
            "tagged":     "0",
            "audio":     "0",
            "chords":     "0"
        }
    ]

I want to do two things when the table is set up. The first is to render the title cell with stuff that comes from three other pieces of data in the row: songid, audio, and chords. The song title would become a hyperlink that uses the songid, and that would be followed by an icon if audio=1 and/or another icon if chords=1. For example, the desired result for the first row in the above example is:

<a href="song.php?sid=56">Song A</a>&nbsp;<img src="graphics/audio.gif">&nbsp;<img src="graphics/guitar.gif">

The docs give the following example of using a function for columns.render:

    render: function ( data, type, row, meta ) {
      return '<a href="'+data+'">Download</a>';
    }

But I don't know how to reference a different column. Just from the parameter names, it looks like row might somehow hold the data for the whole row or something, but I don't know how to use it and can't find any documentation about it. Just for the simple hyperlink part, I tried:

    render: function ( data, type, row, meta ) {
      return '<a href="song.php?sid='+row.songid+'">'+data+'</a>';
    }

But it returned "undefined" for row.songid.

The second thing I want to do is pre-select the rows that have tagged=1. Based on clues I picked up from various sources in the docs, I thought this would do it:

    table.rows( function(idx, data, node) { return data.tagged == 1; }).select();

But it doesn't appear to do anything - no rows get selected.

Any clues on either of these?

kthorngren

I don't know how to use it and can't find any documentation about it

Here is the columns.render documentation. You a good guess though

I placed your code snippets into this test case and they do work:
http://live.datatables.net/duvanugi/1/edit

Without looking at what you have its hard to say why its not working. Make sure you are including the `Select extension for the row selection to work.

Kevin

OsakaWebbie

Thanks, Kevin. It's encouraging that I was on the right track. I fleshed my code out some more while looking for clues about why the simple example works but the real version doesn't. I got different errors as I went along - I think things are getting worse instead of better.

When I first wrote my question, I was not seeing any JS errors (which makes it hard to troubleshoot), but later I started getting TypeError: table.rows is not a function on the statement that attempts to pre-select rows based on "Tagged". I saved a page as plain HTML and stripped off extraneous stuff (the menu, etc.) to make it closer to a MWE. You can see it here.

With some results sets, I don't get a table at all - the code chokes on the dataset itself, which is generated by PHP and transformed into JSON by a standard PHP function. At first I could tell what the problem was, like a PHP-style line break or an un-escaped apostrophe. So I kept adding global replacements to prevent such things. Currently my PHP code is:

$alldata = mysqli_fetch_all($result);
$jsondata = json_encode($alldata);
$jsondata = str_replace('\r','',$jsondata);
$jsondata = str_replace('\n','<br>',$jsondata);
$jsondata = str_replace("'",'&#39;',$jsondata);

The errors no longer point to a spot in the data with an obvious problem, but I know there must be something somewhere. Take a look at this one. The complaint is:
SyntaxError: JSON.parse: expected ',' or ']' after array element at line 1 column 995 of the JSON data
But column 995 is in the middle of innocent text, so I don't know what character is really causing the problem. I tried different options for json_encode() to escape or not escape various things, to no avail - the column number would change a bit, but the error would persist.

Karen

colin

Hi @OsakaWebbie ,

There's an error in the console:

Uncaught TypeError: table.rows is not a function

This is because you have:

var table = $('#songlist').dataTable( {

which should be (note the case)

var table = $('#songlist').DataTable( {

I'd say fix that and see if that helps,

Cheers,

Colin

kthorngren

But column 995 is in the middle of innocent text, so I don't know what character is really causing the problem

This is an interesting problem. The site https://jsonlint.com/ validates the string even though it fails with JSON.parse()`. I put your json string in this example:
http://live.datatables.net/cucuqumo/1/edit

json is the exact string and json1 is the fixed string. Since you have unicode characters you actually have to output the string to see where the error is. I used console.log(json.slice(0, 996)); which the last part of the output is this Reuben Morgan/Hillsong Publishing","CD: Michtam "Mi. When you look at the string you have CD: Michtam \"Miracle\"<br>. In Javascript the backslash is an escape character and is not in the final representation of the string. What you want is double backslashes like this CD: Michtam \\"Miracle\\"<br>. now the final representation of the string will be correct and parsable.

There is a second spot that has this: "CD: Michtam \"Celebrate\"",.

In addition to Colin's comments if you are planning to use the ajax option to fetch the data then your select statement will need to go into initComplete so that it executes after the data is loaded. This is because ajax is an async method and your select statement will likely run before the data is loaded if its not in initComplete. You will want to grab an API instance as shown in this example:
https://datatables.net/examples/api/api_in_init

Kevin

OsakaWebbie

Yes, fixing the case of DataTable caused the table.rows error to go away, but I still get "undefined" for the SongID in render and the pre-selection still doesn't work.

I'm not using Ajax for the initial data (although I hope to use it for updating the database based on the user selections and perhaps some other things, once I get the basic part working). PHP code builds the content being sent to the browser in one shot - the PHP code I shared previously is followed by this:
var dataSet = JSON.parse('<?=$jsondata?>');
(The content of the PHP variable is simply echoed within the JS.)

Perhaps that's why json_encode() seems to be doing the escaping all wrong - the function is normally used for Ajax, so perhaps different things need to be escaped. It seems like I shouldn't have to chase one special character after another with special option parameters and after-encoding str_replace() calls. Yes, I could use Ajax, but it would become two queries of the database (because I need to know if there are at least two matching songs before I even bother with a table) and two HTTP requests, slowing everything down unnecessarily. (Note: This is not a new application - I'm trying to modernize existing legacy code, which built all the markup for a static table in PHP.)

In one of my later attempts to find a combination of escaping that would work, I did include an option for json_encode that would change " into \u0022. But it still chokes on that - I don't understand why. I currently have JSON_HEX_APOS | JSON_HEX_QUOT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE (see http://php.net/manual/en/json.constants.php for the description of what each one does). See my clone of your JSBin with fresh data here: http://live.datatables.net/zisomaga/1/edit Why doesn't it like \u0022? And of course the original questions remain: Why are row.songid and row.SongID (I'm not sure whether I'm supposed to use the "name" or the "class" so I keep trying both) undefined? And why won't the pre-select work? I'm sorry I'm not better at troubleshooting this myself - I'm more of a backend developer; my Javascript and browser debugging skills are weak.

Hmm, I just now looked again at your original JSBin where you used my simplified example. In that case, you built the data as an array of objects with property names ("songid", "title", etc.), and then specified columns.data using those property names. But my real data comes in as an array of arrays with no key names, so there is no columns.data. Is that perhaps why it doesn't work for me? Might the row.something have to be from columns.data?

kthorngren

you built the data as an array of objects with property names ("songid", "title", etc.), and then specified columns.data using those property names. But my real data comes in as an array of arrays with no key names, so there is no columns.data. Is that perhaps why it doesn't work for me? Might the row.something have to be from columns.data?

Yes, you would access it as arrays rather than as objects. Here is an updated example using json1 variable:
http://live.datatables.net/zisomaga/2/edit

Modified the first row, array position 1, with a 1 to simulate the tagged example. Not sure that is your tagged column but for the example its fine. You can use columns.visible to hide any columns you don't want displayed.

EDIT: I updated that column for another row just to highlight the select is working.

Yes, escaping quotes that are within the text of a json string is a tricky one. I don't use PHP so won't be much help there. Coincidentally, at the moment I'm trying to workout the same issue of escaped quotes with my application. So I fell your pain and frustration.

Kevin

OsakaWebbie

I got the inspiration to try searching for all songs that don't have a double-quote in Source (the only column likely to have it), to see if that's the only character causing problems. Of course that produced a much larger result set (384 rows), and I got a different error:
SyntaxError: JSON.parse: bad escaped character at line 1 column 9659 of the JSON data
When I tried putting that beast into JSBin, I got a warning: "Line 7: This character may get silently deleted by one or more browsers." What character? I have no idea how to tell what character it is talking about in my big string! Anyway, I adjusted my search criteria to reduce it to something more manageable, but even a subset of 25 songs had the same error, on column 1644. See json3 in the JSBin. And with this particular error, the trick of sending a slice to the console doesn't work for some reason - nothing appears on the console.

OsakaWebbie

Oops, our messages "crossed in the mail" yesterday, so I didn't notice yours until now, after a night's sleep (it's likely we are in very different time zones) and a busy day of other tasks. (I can't seem to get the forum to send me email notifications; I have to keep checking manually.)

Aha! All along I was referencing the data pieces in object style, because that's the only way I saw it in any examples, including your original one. Not fully understanding the syntax, I was just mimicking. I've fixed it, and the two problems I originally wrote about are solved. Plus, I'm starting to understand the meaning of the syntax better. I wouldn't mind using objects, because text keys would be more readable, but

As for the quotes we were both battling, see the answer by Grégoire Lafortune here: https://stackoverflow.com/questions/7462394/php-json-string-escape-double-quotes-for-js-output He explicitly says that JSON.parse doesn't like \u0022. (I wish he had also said why that particular hex value needs to be escaped, but oh well.) Note how many backslashes I need in my case - who'da thunk? But using his code worked for me. I don't know if that's applicable to your situation, but I thought I'd share, just in case you need more backslashes...

The other error I was seeing with the JSON (bad escaped character) turned out to be a backslash (grammatically it should have been a forward slash, but I don't want to restrict the users). json_encode() of course escaped it once, but taking a clue from Gregoire, I then replaced \\ with \\\\, and it got well.

So I'll sign off for the moment. I have other questions I might later ask in fresh threads, but this thread's topics are resolved, thanks to both you guys!

OsakaWebbie

I marked your last reply as the "Answer", but for some reason the top of the page still says, "Posts: 5 Questions: 1 Answers: 0". Anyway, I hope you get whatever sort of points the forum doles out.

kthorngren

Thanks for the update and posting the SO thread regarding JSON.parse().

"Posts: 5 Questions: 1 Answers: 0"

Those are your stats not specific to this thread

Kevin