Camelot PHP Tools 1.1 for SharePoint released

As of today the next version of the Camelot PHP Tools for SharePoint is available for public download, visit the official web site to download, http://www.bendsoft.com/downloads/sharepoint-php-tools/

Highlights in this update

  • PHP Tools now consorts with Camelot WCF Service
  • Seamless integration with SharePoint (through the WCF Service)

The toolkit has undergone a general stabilization and is more powerful than ever.

Execute Select, Insert, Update and Delete queries in SharePoint lists directly from PHP

The vision for this release was to simplify normally complex integrations to a level where selecting and manipulating data in SharePoint could be done with a single command, from PHP that is. So, here it goes.

Selecting data from SharePoint with SQL

$SharePointQuery = new SharePointQuery(
    array(
        'sql' => "Select * from `My SharePoint List`.`ViewName`",
        'compression' => true,
        'connString' => 'sharepoint_connection',
        'sharedKey' => constant("WSDL_SHARED_KEY")
    )
);

Selecting data from SharePoint by list and view name

$SharePointQuery = new SharePointQuery(
    array(
        'listName' => 'My SharePoint List',
        'viewName' => 'ViewName',
        'includeAttachements' => false,
        'compression' => true,
        'connString' => 'sharepoint_connection',
        'columns' => 'ID;Title;ListItem1;ListItem2',
        'sharedKey' => constant("WSDL_SHARED_KEY")
    )
);

Insert data in SharePoint with SQL and SharePointNonQuery

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "INSERT INTO contactform (title,email,company,message) VALUES ('John Doe','john.doe@example.com','Johns Company','A test message!')",
    'method' => 'ExecuteNonQuery',
    'connString' => 'sharepoint_connection',
    'sharedKey' => constant("WSDL_SHARED_KEY")
));

Delete data in SharePoint with SQL and SharePointNonQuery

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "DELETE FROM contactform WHERE Title = 'John Doe'",
    'method' => 'ExecuteNonQuery',
    'connString' => 'sharepoint_connection',
    'sharedKey' => constant("WSDL_SHARED_KEY")
));

Update data in SharePoint with SQL and SharePointNonQuery

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "UPDATE contactform SET Status = 'Accepted' WHERE `Contact Email` = 'John Doe@example.com'",
    'method' => 'ExecuteNonQuery',
    'connString' => 'sharepoint_connection',
    'sharedKey' => constant("WSDL_SHARED_KEY")
));

Insert data in SharePoint with a Scalar Command

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "INSERT INTO `My List` (title,email) VALUES ('John Doe','john.doe@example.com')",
    'method' => 'ExecuteScalar',
    'connString' => 'sharepoint_connection',
    'sharedKey' => constant("WSDL_SHARED_KEY")
));

// Echo last inserted id
if($SharePointScalarQuery->_result > 0) {
    echo "Last inserted ID is $SharePointScalarQuery->_result";
}

 

$SharePointQuery in a bit more detail

The SharePointQuery is a class who contains methods that initiates a connection to the Camelot WCF Service, it returns a structured Object array (see image below) that is used to display the data.

Decoded Camelot XML Packet

Decoded Camelot XML Packet

Rendering the data

From this point you may choose to do whatever you want with the data, we recommend using the Twig template engine to render the data. The framework is included in PHP Tools and there are some usage examples in the documentation. Below is an example reflecting a list from SharePoint.

try {
    $SharePointQuery = new SharePointQuery(
        array(
            'sql' => "Select ID, LinkTitle as Title, Article, `Publish to external` AS Publish, Created from `SharePoint Articles`.Article",
            'compression' => false,
            'connString' => 'sharepoint_cms',
            'sharedKey' => constant("WSDL_SHARED_KEY")
        )
    );

    $SharePointResult = $SharePointQuery->CamelotSoap->_sorted;

    require_once 'twig/lib/Twig/Autoloader.php';

    Twig_Autoloader::register();
    $loader = new Twig_Loader_Filesystem('templates');
    $twig = new Twig_Environment($loader);
    $template = $twig->loadTemplate('sp2007.html');
    echo $template->render(array(
        'list_header' => $SharePointResult->_schema->display_name,
        'list_content' => $SharePointResult->_content
    ));
} catch (Exception $exc) {
    pr($exc->getTraceAsString());
    pr($exc->getMessage());
}

For the nerds

Lets break that down a bit

$SharePointQuery = new SharePointQuery(

This creates a new instance of the SharePointQuery class which automatically uses the values in the settings.php file (where the WCF service is stored) and open a connection to the WCF service connected to SharePoint.

array(
    'sql' => "Select ID, LinkTitle as Title, Article, `Publish to external` AS Publish, Created from `SharePoint Articles`.Article",
    'compression' => true,
    'connString' => 'sharepoint_cms',
    'sharedKey' => constant("WSDL_SHARED_KEY")
)

This is the command we send to the WCF service, the WCF service executes this towards the SharePoint installation.

  • sql, the command you want to send to SharePoint
  • compression, whether you want to get the returned data compressed or not
  • connString, which connection string to use from the web.config file in the WCF service
  • sharedKey, we implemented some security to disable open access to the methods. This key should correspond to the one set in the web.config file of the WCF service.
$SharePointResult = $SharePointQuery->CamelotSoap->_sorted;

This fetches the returned result and steps in to the object where the data is getting interesting for this purpose (displaying it in a html page).

require_once 'twig/lib/Twig/Autoloader.php';

Twig_Autoloader::register();
$loader = new Twig_Loader_Filesystem('templates');
$twig = new Twig_Environment($loader);
$template = $twig->loadTemplate('sp2007.html');
echo $template->render(array(
    'list_header' => $SharePointResult->_schema->display_name,
    'list_content' => $SharePointResult->_content
));

This loads the twig framework and selects the sp2007 template as output template

  • list_header, the header of the list which comes from the schema
  • list_content, all content rows from the list

Simple as that, this displays the following result

Simple as that, you can use the PHP Tools to form wonderful websites in no time. Download now, it’s free!

This entry was posted in PHP. Bookmark the permalink.

2 Responses to Camelot PHP Tools 1.1 for SharePoint released

  1. Mike says:

    I’m not able to get this working. I don’t find where the SharePoint user/pass authentication parameters should be entered. Do I need an additional component for that?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>