PHP - Data Explorer API Example

Community Building Data & reporting

AI-generated summary

The discussion PHP - Data Explorer API Example involves using PHP to pull data from Discourse using the Data Explorer API. LotusJeff shared a PHP code skeleton, which includes setting an API key, defining the Discourse forum URL, and passing parameters to a query. The code uses curl to execute a POST request and retrieve the result as a PHP array.

The example code is paired with a Data Explorer query that selects specific data from the Discourse database, including usernames, post IDs, upload URLs, and topic titles, based on parameters such as term and user.

CocoQuark suggested trimming unnecessary comments from the code to improve readability and provided a tip on setting syntax highlighting for code blocks. LotusJeff updated the initial post based on this feedback.

1

I am working on pulling data out of Discourse using PHP and the Data Explorer API. Here is the skeleton of my PHP code for others as a starting point.

PHP Code

<?php
// Set API key
$api_key = '<your key goes here>';
//
// 1. $url should be set to your Discourse forum URL
// 2. Obtain the query # you want to run. Update the url for that query
//    i.e. query #5 => .../queries/5/run
//    i.e. query #47 => .../queries/47/run
$url = 'https://discourse.com/admin/plugins/explorer/queries/7/run';
//
// Update your parameters to pass to the query.
//  Example for one parameter - hard coded
//  $data = 'params={"user":"fred_smith"}';   
//  Example for one parameter - passing a variable
//  $data = 'params={"user":"'.$user.'"}';   
//  Example for two or more paramters - passing a variable
$data = 'params={"user":"'.$user.'","term":"'.$term.'"}';   
//
// Curl Commands
//
$headers = array("Content-Type: multipart/form-data;","Api-Key: $api_key","Api-Username: system",);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers );
curl_setopt($ch, CURLOPT_POSTFIELDS,$data);

$result = curl_exec( $ch );
if ( curl_errno( $ch ) !== 0 ) {
   // Handle error, call curl_close( $ch ) and return.
}
curl_close( $ch );
//
// Convert curl result to a php array to programatically work with
$result_array = json_decode( $result,true);

print($result_array);

?>

Data Explorer Query

In the example of the code using two passed variables, my data explore code looked like this:

--[params]
-- string :term
-- string :user

SELECT users.username, posts.id, uploads.url, topics.title
     FROM upload_references
     JOIN uploads ON uploads.id = upload_references.upload_id
     JOIN posts   ON posts.id   = upload_references.target_id
     JOIN topics  ON topics.id  = posts.topic_id
     JOIN users   ON posts.user_id = users.id
    WHERE (posts.cooked like '%' || :term || '%' 
       OR topics.title like '%' || :term || '%') 
      AND users.username_lower like '%' || :user || '%'
      AND topics.archetype = 'regular'
 ORDER BY uploads.created_at DESC
 limit 10

I hope this helps someone jumpstart a project.

2

I’d trim most of the comments to make it easier to read :slight_smile:
Some comments are unnecessary, like:

// Obtain result
//
$result = curl_exec( $ch );

or

// close curl session
curl_close( $ch );

Also, know you can set the syntax highlighting by appending the language name to the first line of backticks:

```php
$headers = array("Content-Type: multipart/form-data;","Api-Key: $api_key","Api-Username: system",);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers );
curl_setopt($ch, CURLOPT_POSTFIELDS,$data);
```

Renders as

$headers = array("Content-Type: multipart/form-data;","Api-Key: $api_key","Api-Username: system",);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers );
curl_setopt($ch, CURLOPT_POSTFIELDS,$data);
3

Thanks for the feedback. I updated the initial posts.