Search code examples
phpquickbase

QuickBase foreach insert

I'm having an issue using the Quickbase API to perform the following:

SELECT 1, 2, 3 FROM table AA that has column BB = 1

foreach record {

Insert 1, 2, 3 into table ZZ.

}
function add_children($opportunity_id) {
    global $config;

$qbc = new QuickBase($_SESSION['qb_username'] , 
                     $_SESSION['qb_password'], 
                     true, 
                     $config['AA'], 
                     $config['debug'], 
                     $config['app_token']);
$xml = $qbc->do_query("{'" . $config['AA'] . "'.EX.''}", 0, 0, 'a', 0, '', '');


$records = array();
foreach($xml->record as $record) {
    $r = array();
    $r['record_id'] = $record->record_id_;
    $r['account_number'] = $record->account_number;
    $records[] = $r;

$xml = $qbc->add_record($records[]);
    }
}
Solution

  • First, I'm assuming that you're using this PHP SDK by QuickbaseAdmirer https://github.com/QuickbaseAdmirer/QuickBase-PHP-SDK. There are a few potential problems with your code.

    1. Double check that your constructor is correct. Unless you've modified it, the Quickbase constructor in the SDK (again that I'm assuming you're using) takes user name, password, xml, database id, and then token in that order. Whatever value is in $config['debug'] may be taken as the token and the value of $config['app_token'] may be taken as your realm. Also, $config['AA'] as used in the constructor should be a string of random seeming characters like "bbqn1y5qv". Here's the constructor in the SDK for reference:

      public function __construct($un, $pw, $usexml = true, $db = '', $token = '', $realm = '', $hours = '')

    2. Your query $xml = $qbc->do_query("{'" . $config['AA'] . "'.EX.''}", 0, 0, 'a', 0, '', ''); is not returning any records because $config['AA'] is both being used as your DBID (in the constructor) and your field ID in the query. The DBID must be a string and the field ID must be an integer that corresponds to the field you're making the query for. For example, if you wanted to return records created today your query would be '{1.IR.today}' because 1 is always the field ID for date created. It's also not returning any records because the SDK requires queries be passed as an array of arrays. So, my records created today query needs to be rewritten as:

      $query= array( array( 'fid' => '1', 'ev' => 'IR'), 'cri' => 'today'), );

      You'll also need to pass a string of period separated values to the clist parameter of the method or leave it blank for the table defaults. For example, if I wanted to get the date created and record ID for all records in this table sorted by date ascending, I would use this:

      $query= array( array( 'fid' => '3', 'ev' => 'GT'), 'cri' => '0'), ); $xml = $qbc->do_query($query, '', '', '1.3', '1', '', 'sortorder-A');

      You can read up more on the Quickbase API, and do_query specifically, here http://www.quickbase.com/api-guide/index.html#do_query.html

    3. The add record API call takes pairs of field IDs and values. The SDK handles that by taking an array of arrays with 'fid' and 'value' pairs. Assuming you want to put the value of $record->record_id_ in field #37 and $record->account_number in field #30 your code should look like this:

      foreach($xml->record as $record) { $records= array( array( 'fid' => '37', //Whatever field you want to store the value to 'value' => $record->record_id_), array( 'fid' => '30', 'value' => $record->account_number), ); $xml = $qbc->add_record($records); }

      Throw in a print_r($xml); at the end and you can see any response from Quickbase for debugging. You should get something like this for a success:

      SimpleXMLElement Object ( [action] => API_AddRecord [errcode] => 0 [errtext] => No error [rid] => 81 [update_id] => 1436476140453 )

    4. The way your code is presented, you may not get the results you expect. Your do query and add record method calls are performed on the same table and that isn't normally what someone would want. Usually, the goal is to perform a do query on one table and then use that data to add records in a different table. If that's the case, you'll need to change the database ID in your $qbc object before you preform the add record call. This is easy enough to do with $qbc->set_database_table('DBID'); where DBID is the target table ID (which should be a string of random seeming characters like "bbqn1y5qv").

    Best of luck!