API sample code for updating a field value

[gsgriffin]

I've developed an application which would benefit by using the API, but the instructions given are not a great way for me to learn.  Having instructions that say, "Just do this and then that" is missing the whole point of how and assuming those reading are professional programmers that do this all day for a living.  Those of us who are self taught are left to do a lot of work trying to figure it out.  I learn best by just seeing code and understanding what it is doing and then adapting to my needs.  Wondering if anyone might be able to post a little code to help?

All I need is an API code where I can send it the table, Row Id, field, and value and have that update in the table.  I don't need Gets. I realize that doing all of this from Javascript is not a good idea with the with the client key being exposed (but OH, how that would make my life easier and faster).  So, PHP on the server is my only other option, I suppose.  I've already set up and have the account permissions.

Could I ask that someone kindly just post an example PHP which would receive the needed values posted from the URL and do the action of updating a table field?  My Javascript can post all the needed values in the URL call to the PHP.  I can modify it from there to hide details a little better so it isn't obvious enough for someone else to simply make direct calls to the PHP.  If I'm going about this the wrong way, feel free to offer a different solution, but some code example would really be helpful as opposed to telling me the better way.

THANKS!!!!

[gsgriffin]

Just curious.  Is anyone on this forum anymore or is the Caspio community dead?  Find it odd that nobody at all has responded...even with this being too big of an ask or too complicated or more info needed.  Not even a moderator?

[gsgriffin]

Since it appears nobody has done this before and support from Caspio isn't willing to make their documentation clear enough to figure this out, I spent many hours in trial and error to figure out how the API works and to do so with PHP...which everyone on the web should know.  Below is the code sample that when called through a URL with query values will:

1) attempt to get a file which will store the auth token in a function

2) if there is no file, get a new auth token and then store it in a bearer file in a function

3) attempt to write the value to the field in the table

4) if unsuccessful, try renewing the token, store the new token, and write to the table again.

This should hopefully live forever and never need me to go and renew the tokens, which is a pain and prone to messing up the program someday.

This is what I was looking for all over the forums. Hopefully this will help someone someday:

<?php

$bearer_filename="bearer.php"; //file to hold function which responds to request for current auth token
$sub_domain="c1eib....";//replace with the subdomain Caspio assigns to your url for token auth
$client_id='e16e1a....replace with your client id 51ade07';
$client_secret='e235b22....replace with your secret.......d4b3d9c1e6d8a5';

function getBearer(){

	global $sub_domain;
	global $client_id;
	global $client_secret;

	$body="grant_type=client_credentials&client_id=".$client_id."&client_secret=".$client_secret;
	$grant_type='grant_type=client_credentials';
	$auth_url='https://'.$sub_domain.'.caspio.com/oauth/token';

	$curl = curl_init();
		curl_setopt($curl, CURLOPT_URL, $auth_url);
		curl_setopt($curl, CURLOPT_POST, 1);
		curl_setopt($curl, CURLOPT_POSTFIELDS, $body);
		curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
	$result = curl_exec($curl);

	if(!$result){return "Connection Failure";}
	curl_close($curl);
	
return $result;
}


function updateTable($table,$id,$matching,$field,$value,$auth){

	global $sub_domain;
	
	$auth=json_decode($auth);
	$url = 'https://'.$sub_domain.'.caspio.com/rest/v2/tables/'.$table.'/records?q.where='.$id.'%3D'.$matching;
	$update=array($field => $value);
	$postdata=json_encode($update);
	$auth = 'bearer '.$auth->access_token;

	$curl = curl_init();
	curl_setopt_array($curl, array(
		CURLOPT_URL => $url,
		CURLOPT_RETURNTRANSFER => true,
		CURLOPT_ENCODING => "",
		CURLOPT_MAXREDIRS => 10,
		CURLOPT_TIMEOUT => 30,
		CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
		CURLOPT_CUSTOMREQUEST => "PUT",
		CURLOPT_POSTFIELDS => $postdata,
		CURLOPT_HTTPHEADER => array(
			"Authorization:" . $auth,
			"accept: application/json",
			"cache-control: no-cache",
			"content-type: application/json",
			'Content-Length: ' . strlen($postdata)
		)
	));

	$results=curl_exec($curl);
	curl_close($curl);
	
    echo $results;
	
	$response=json_decode($results);

	if($response->RecordsAffected==1){
		echo "<br>Updated 1 row successfully";
	}else{
		//get the auth key and try again

	}
	return $response;
}

function createBearer(){
	global $bearer_filename;

	$new_auth = getBearer(); //get new bearer tokens

	$code='<'.'?php function getAuth(){return \''.$new_auth.'\';} ?'.'>'; //code for file with function to return values
	file_put_contents($bearer_filename,$code);

	return $new_auth;
}


if(!file_exists($bearer_filename)){
    //create new bearer file and get auth
	$auth = createBearer();
}else{
    //file exists with function to give auth
	include $bearer_filename;
	$auth = getAuth();
}

//get values from URL for posting to table
$table=$_GET["Table"];
$id=$_GET["ID"];
$matching=$_GET["Matching"];
$field=$_GET["Field"];
$value=$_GET["Value"];

$results = updateTable($table,$id,$matching,$field,$value,$auth);

if($results->Message=="Authorization has been denied for this request."){
	echo "<br>Problem with update.  Try getting new bearer and try again";
	$auth = createBearer(); //could be that the token has expired.  create new one, save the file and try again with fresh token
	$results = updateTable($table,$id,$matching,$field,$value,$auth);
	echo "Results after retry:".$results;
}

?>

Wherever you place this on your server, you can call it directly using:

https://...your url to this php file...?Table=YOUR_TABLE&ID=FIELD_NAME_IN_TABLE_FOR_MATCHING_ID&Matching=VALUE_TO_MATCH_ID&Field=FIELD_YOU_WANT_TO_UPDATE&Value=VALUE_YOU_WANT_IN_THE_FIELD

This was design for a single field update in a single row.  You will need to adapt for any other use, but at least I will hopefully never have to worry about an auth token ever again and my program will be stable.

[HappyAlligator]

Hi! I am a month late to the party but maybe I can make an apport.

https://github.com/moksamedia/caspio-rest-api-lib

I think is a cool idea to share things like this, speacily since authetication with cookies is so problematic, the users always get problems for this.

[Dichan]

Hi,

gsgriffin thank you for your php code.

I want to use it as a plugin or a child theme in wordpress and have a few questions:

1. Do I have to create a file bearer.php or it will create a file if there is none in the same directory?
2. When replacing all the values ($sub_domain, $client_id, $client_secret), do I have to keep the double and single quotes? 
3. If I want to get the current user logged in the wordress site to use as Value, can I add this code in the file, just before //get values from URL for posting to table:

function get_currentuserinfo(){ 

global $current_user;

               get_currentuserinfo();

               echo 'User email: ' . $current_user->user_email . "\n";

}

          4. And should I replace "Value" by $current_user ?

          5. Do you recommand a plugin or a child theme or where to save the php file?

       

Thank you

 

Edited October 5, 2022 by Dichan
I made errors in my post

[Dichan]

Hello, I fugured out all the answers to my questions! It works fine and thank you so much gsgriffin for your code. Just one thing, I think "getBrearer();" is missing in gsgriffin's code : 

 

	$results=curl_exec($curl);
	curl_close($curl);
	
    echo $results;
	
	$response=json_decode($results);

	if($response->RecordsAffected==1){
		echo "<br>Updated 1 row successfully";
	}else{
		//get the auth key and try again

	}
	return $response;
}

Here is what I wrote:

    $results=curl_exec($curl);
    curl_close($curl);
    
    echo $results;
    
    $response=json_decode($results);

    if($response->RecordsAffected==1){
        echo "<br>Updated 1 row successfully";
    }else{
        //get the auth key and try again
        getBearer();
    }
    return $response;
}

[gsgriffin]

Is this really too big of an ask?  I've seen posts linking to some code that is much more extensive and more than I need.  If the API is this difficult that only professional coders can do it, perhaps the API need to be easier?  Make the API so more can use it?  Has nobody used the API to simply update fields in tables?  I honestly thought this would be a just minute for someone to copy and paste without explanation.  Hopefully some are still using this forum and I'm not the only one that would benefit from this.