Databases

[Simon.AU]

Hi all,

Using the cloud version of Invision Community here. Does anybody know whether there is a way to interact with the "databases" component (Pages > Content > Databases in the ACP) with php functions, that I could embed into custom blocks?

The ultimate aim here is to manage a list of selected names that are shown on a particular page, and we need to make this list easy to update.

Another alternative would be file access (using a .csv or .json file), does anyone know if it's possible to access media files via php in this way? eg. like the { media="id" } path that is available for URLs.

cheers
David

[Simon.AU]

Oh I just discovered the API stuff again. This is pretty good actually. I'm thinking /cms/records should do what I'm after 🙂

[Simon.AU]

OK still stuck... somehow requests to

/cms/records/2

(2 is my database id) just create records with bits of the data just sprinkled seemingly randomly into the fields, only 1 character per field.

I've created a database with 4 fields:

State
Organisation
Name
Email address

Name is set as the "Content" field and Email address is set as the "Title" field. I do a simple CURL POST request to create a record like so:

$communityUrl = 'https://<server>.invisionservice.com/';
$apiKey = '...snip...';
$endpoint = '/cms/records/2';// 2 is the database id

$curl = curl_init( $communityUrl . 'api' . $endpoint.'?' );
curl_setopt_array($curl, [
    CURLOPT_RETURNTRANSFER    => TRUE,
    CURLOPT_HTTPAUTH    => CURLAUTH_BASIC,
    CURLOPT_USERPWD        => "{$apiKey}:",
    CURLOPT_USERAGENT    => "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36",

    CURLOPT_HTTPHEADER => array(
        'Accept: application/json',
        'Cache-Control: no-cache',
    ),
    CURLOPT_POST => true,

    CURLOPT_POSTFIELDS => [
        'author' => 2,
        'fields' => urlencode('&State=teststate&Organisation=testorg&Name=NameTest&Email=email@test.com')
    ],
]);
$strJSON = curl_exec($curl);
$objJSON = json_decode($strJSON);

echo '<pre>';
print_r($objJSON);
echo '</pre>';

...I run that on our server, and I get this record created in our IPB database:

...full of weird data:

Can anybody please help explain what is going on? I don't understand why it would be taking only one character per field. It looks as though it's taking the field values from the first part of "state=" in the POST data, and reversing it, and assigning one character per field sequentially according to the database field order.

[Marc Stridgen]

Development is beyond the scope of our standard support unfortunately. However I have moved this ticket to our developer connect forum, as its better placed to get you the assistance you need.

[Simon.AU]

I haven't been able to solve the issue with this API interaction. Does anybody know why the database is only taking one character?

Is this support that we need to pay for? If so, is anyone from IPB able to direct me how to do that?

[Stuart Silvester]

Hi Simon,

Fields need to be structured as an array with the field ID as the key (you can find this in the URL when editing a field in the AdminCP).

    CURLOPT_POSTFIELDS => [
		'author' => 2,
		'fields[1]' => 'teststate',
		'fields[2]' => 'testorg',
		'fields[3]' => 'NameTest',
		'fields[4]' => 'email@test.com'
    ]

Something like this, but change those keys for the proper IDs

[Simon.AU]

On 6/16/2022 at 5:52 AM, Stuart Silvester said:

Hi Simon,

Fields need to be structured as an array with the field ID as the key (you can find this in the URL when editing a field in the AdminCP).

    CURLOPT_POSTFIELDS => [
		'author' => 2,
		'fields[1]' => 'teststate',
		'fields[2]' => 'testorg',
		'fields[3]' => 'NameTest',
		'fields[4]' => 'email@test.com'
    ]

Something like this, but change those keys for the proper IDs

Hooray, that did it - thanks Stuart 🙂

[Brew Digital]

Hi, not sure if I need follow on from this question or not, we have an old app that still works fine, running against the /cms/records API endpoint, but were hoping to add 'anonymous' onto the postfields, they were initially in the same urlencode format that Simon had above, so I've changed the fields to be in the array format suggested and added anonymous in, something like this

CURLOPT_POSTFIELDS => [
		'author' => 2,
		'category' => 4,
		'fields[1]' => 'title',
		...
		'anonymous' => 1
    ]

This doesn't seem to post correctly at all, in fact I get the weird single character issue mentioned above. I've tried adding the anonymous into the URL encoded string as well, that still posts the data fine but doesn't do anything with the anonymous value (ie it remains at 0 in the custom table). Apologies if this is the wrong place to ask the question, but any help, or just just any thoughts about it at all, would be greatly appreciated.

[Simon.AU]

This is probably not much help to you but have you set 

CURLOPT_POST => true

?

[Brew Digital]

Thanks for the reply, I have actually sorted this one, turned out my code was perfectly fine and it was the anonymous posting abilities of the users on the categories that was the issue.