Extension talk:External Data

Invalid JSON on #get_web_data
I plan on doing some upgrades to mediawiki and possibly php as a shot in the dark, but before doing that I figured I would ask here.

Here is the exact json of the table I'm trying to display. The data itself is junk just to play with.

[{"IPAddress":"192.168.000.001","Type":"Server","Device":null,"Location":null,"Comment":null,"HostnameDNS":null,"OSFirmware":null,"LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.002","Type":"Server","Device":"Virtual Server","Location":"ERA - Server Room","Comment":"File and Print Server","HostnameDNS":"fileserver","OSFirmware":"Server 2016","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.003","Type":"Server","Device":"Virtual Server","Location":"ERA - Server Room","Comment":"Video Server","HostnameDNS":"videoserver","OSFirmware":"Linux","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.004","Type":"Server","Device":"Physical Server","Location":"ERA - Server Room","Comment":"Web Server","HostnameDNS":"webserver","OSFirmware":"IBM OS\/400 7.2","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.005","Type":"Server","Device":"Physical Server","Location":"ERA - Server Room","Comment":"Mainframe","HostnameDNS":"mainframe","OSFirmware":"IBM OS\/400 7.2","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"}]

I'm getting this from a really generic json_encode( $data ); function in select_ipam.php, which queries a Microsoft SQL server database. I have already verified that my header information is set correctly as another user pointed out.

header("Content-Type: application/json; charset=utf-8"); echo( json_encode( $data ) );

I also tried using the "use jsonpath" approach but it just gives me the same thing.


 * I'm not sure what the exact problem you're seeing is, but I would recommend switching to the latest version of External Data - the version you're running is rather old. Yaron Koren (talk) 00:46, 8 January 2021 (UTC)


 * I was able to get it working with some different syntax not using a template and a version update. Now if I can just figure out how to get it to update more frequently. When I click edit and save the page, without making any other changes it updates the table, but otherwise it doesn't want to grab new data from the php post.  Are there any plans to integrate an "ajax mode" that would allow the table data to be refreshed say every 10 seconds without a full page reload?


 * Okay, that's good. No... that sounds like it would require a whole different approach - JavaScript-based instead of PHP-based. Yaron Koren (talk) 20:18, 8 January 2021 (UTC)

get_web_data error after updating mediawiki
updating to mediawiki 1.35.1 and using the latest external data  results in my template: erroring with:

Could not get URL http://localhost/api.php?action=query&list=categorymembers&cmtitle=Category:CHOPs&format=xml&cmlimit=500 after 3 tries.

Clicking the url though returns the expected XML..

--Wuestenarchitekten (talk) 21:47, 13 January 2021 (UTC)


 * Silly question, but did this work before? Yaron Koren (talk) 14:00, 14 January 2021 (UTC)

#get_db_data with 'join on' statement returning blank table
Apologies in advance, I am somewhat new to SQL but I have some experience. I am attempting to use #get_db_data to pull data from a local server DB to display sports statistics. One of the tables has a field (Position) that I need for the wikitable, so I was hoping to join these two tables using the 'PlayerID' field. I can't do this server-side because it would add considerable time to processing and, again, I don't have enough experience.

Using the aliases throughout and executing the query returns no syntax issues or DB errors, but #for_external_table returns an empty table. I get the appropriate number of rows in the table, so I assume it's returning nulls or default values. Without the ability to debug there's no way to track down where in the process it's losing the values.

Here's the code I wrote:

Documentation is limited for the 'join on' statement for this extension, and there aren't even any reported issues from the past (I've checked the archives). Should I create a template and use #display_external_table instead? Any help would be appreciated.

2603:8001:4948:DF00:7D69:EB88:902:704 09:50, 26 January 2021 (UTC)gplehner


 * I don't know. My guess is that the problem is in the "data" parameter, somehow - that External Data is failing to find the external variables listed. I would try taking out those table aliases, i.e. keeping the original table names... there's a chance that that will fix the problem. Yaron Koren (talk) 15:11, 26 January 2021 (UTC)


 * Thanks, I had time to come back to this today. Removing the table aliases from the "data" parameters returned Error 1052. I incrementally removed each alias until I narrowed the issue to two parameters, team and playerID. Both tables have a field with the name 'Team' and 'PlayerID'. It doesn't seem to matter which one I request in the "data" parameter, both print out the default/'else' values I set. The 'data' parameter certainly is understanding the alias names.


 * I should also mention that I switched to using '#display_external_table' in an attempt to pass data parameters that way but it didn't work either. Thanks for your response & suggestions. 45.51.157.21 08:21, 28 January 2021 (UTC) -gplehner


 * If you have database server access you should create a VIEW on the database and then do the select on that view.
 * In the view you can join whatever table you want without touching the tables.
 * See: https://www.w3schools.com/sql/sql_view.asp
 * In your case you create a view called PlayerSeasonStats. --Felipe (talk) 13:14, 28 January 2021 (UTC)


 * I meant removing table aliases from the entire query, by the way, not just from "data". Yaron Koren (talk) 16:26, 28 January 2021 (UTC)

function to check whether any line of external data matches a string
My use case for External data is reading a single csv file, and then displaying different tables for lines that contain video/pdf/image etc, indicated by a type column. While I can use {{#ifeq:{{#external_value:type}} to check the first line of data, the header, has been read, I can't decide to only display the video table if videos are available. How hard would it be to have a function that checked all entries in a particular column against a search string, returning true if any matched? At the moment I have lots of tables with headers and no content. Vicarage (talk) 16:48, 7 March 2021 (UTC)


 * If I understand this question correctly, you may be able to accomplish this with the Variables extension - instead of displaying the table directly, set it in a variable, and then only display that variable (and its header) if it's non-empty. Yaron Koren (talk) 02:12, 8 March 2021 (UTC)

Thanks, I'll look at that. My initial workaround was to create a counts.csv file, with no header, and check against {{#ifexpr:{{#external_value:imagecount}} > 0, where imagecount=column 4 of the file. Vicarage (talk) 11:57, 8 March 2021 (UTC)

retrieving a template reference from external_data.
I'd like to be able to retrieve a json object that contains one key ('data') and a string that contains mediawiki formatted text. Is there a way to parse that retrieved string, so that it displays on the page as formatted data, rather than plain text?

Example: I'd like to retrieve  from an external source, and have it render the template

173.16.242.134 04:23, 8 March 2021 (UTC)