Answers

• kthorngren Posts: 21,337Questions: 26Answers: 4,954

  December 2018

  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 Posts: 12Questions: 2Answers: 0

  December 2018

  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 Posts: 15,240Questions: 1Answers: 2,599

  December 2018 edited December 2018

  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 Posts: 21,337Questions: 26Answers: 4,954

  December 2018

  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 Posts: 12Questions: 2Answers: 0

  December 2018

  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 Posts: 21,337Questions: 26Answers: 4,954

  December 2018 edited December 2018 Answer ✓

  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 Posts: 12Questions: 2Answers: 0

  December 2018

  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 Posts: 12Questions: 2Answers: 0

  December 2018

  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 Posts: 12Questions: 2Answers: 0

  December 2018

  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 Posts: 21,337Questions: 26Answers: 4,954

  December 2018 edited December 2018

  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