Connect to Sharepoint Database through PHP

问题:

I am not familiar with Sharepoint. I would like to query or read Sharepoint database using PHP.

Is there a way I can do that?

Thank you in advanc. Any help is greatly appreciated.

回答1:

You should consider using the Camelot PHP Tools for SharePoint, it's a well documented php framework for the Camelot XML format specially constructed for SharePoint lists.

Documentation and download

• http://docs.bendsoft.com/camelot-php-tools/
• http://www.bendsoft.com/downloads/sharepoint-php-tools/

You will also need the Camelot SharePoint Integration Toolkit, http://camelottoolkit.codeplex.com/ and the Camelot .NET Connector (http://www.bendsoft.com/net-sharepoint-connector/).

Install the Connector on a box that can reach the SharePoint server, this may be the same server as the SharePoint server, then install the Integration Toolkit on the same server as the Connector. Set up the integration service that is included in the integration toolkit (follow the instructions) and then you are done. There are a few instruction videos on the websites as well.

The upsides of using this is that you will be able to talk to SharePoint lists and libraries through the API by using common SQL queries, the underlying mssql database is never used.

Selecting data from SharePoint with SQL

$SharePointQuery = new SharePointQuery(array(
    'sql' => "SELECT * FROM Tasks WHERE ID > 10",
    'connection_name' => 'SharePointConnection1'
));

Selecting data from SharePoint by list and view name

$SharePointQuery = new SharePointQuery(
    array(
        'listName' => 'Tasks',
        'viewName' => 'All Tasks',
        'includeAttachements' => false,
        'connection_name' => 'SharePointConnection1',
        'columns' => ''
    )
);

Insert data in SharePoint with SQL and SharePointNonQuery

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "INSERT INTO Tasks (Title,AssignedTo,Status,Priority,DueDate,PercentComplete) VALUES ('Test task from PHP',1,'In Progress','(1) High', '".  date('Y-m-d H:i:s') ."',0.95)",
    'method' => 'ExecuteNonQuery',
    'connection_name' => 'SharePointConnection1'
));

There are also stored procedures to help you with some operations, like advanced handling of document libraries

Download a file

$download = new CamelotDownloadFile(array(
    "file" => $_GET["file"],
    "listName" => 'Shared Documents',
    "connection_name" => 'SharePointConnection1'
));

$download->download_file();

Upload a file

$args = array(  
    "file" => $_FILES,
    "listName" => 'Shared Documents',
    "folder" => 'Folder/',
    "connection_name" => 'SharePointConnection2'
); 

$UploadFile = new CamelotUploadFile($args);

回答2:

i highly recommend using the SharePoint WebServices instead... unless there are valid reasons (i.e. performance) i would not touch the database. Quote from this answer:

1. This is completely unsupported by the EULA you agreed to when you installed SharePoint.
2. Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
3. Directly querying the database can place extra load on a server and hence performance issues.
4. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
5. Your custom queries might lead to incorrect data being retrieved.

If you want to know more about why you shouldn't query the database, here is a really great article

Query A SharePoint WebService with PHP

回答3:

It's just a database - as long as you have the name of the server/database and the proper permissions, there is nothing that can stop you. However - the schema is pretty involved, so figuring out from there what you need can be tricky - depending on what you really want to do, you may be better off using the web services to access the Sharepoint OM.

In case you want to write to the database directly - don't. There is no practical way to do that without getting yourself into deep trouble farther down the line, and support won't be able to help you out.

回答4:

Sharepoint database is nothing but MS SQL Server. If you know the server name, you can connect to it as same as how you can connect to MSSQL server from PHP.

回答5:

The easiest way to get SharePoint data from PHP is probably via the REST API.

回答6:

I have used this in API to connect my PHP web application with SharePoint and transferring data From PHP to SharePoint, it worked 100% for me:

Usage Instructions:

Installation

Download the WSDL file for the SharePoint Lists you want to interact with. This can normally be obtained at:sharepoint.url/subsite/_vti_bin/Lists.asmx?WSDL

If you are using composer, just add thybag/php-sharepoint-lists-api to your composer.json and run composer.

    {
    "require": {
        "thybag/php-sharepoint-lists-api": "dev-master"
    }
    }

If you are not using composer you can download a copy of the SharePoint API files manually and include the top "SharePointAPI.php" class in your project.

Creating SharePointAPI object

In order to use the PHP SharePoint Lists API you will need a valid user/service account with the permissions to the required list.

For most SharePoint installations, you can create a new instance of the API using:

    use Thybag\SharePointAPI;
            $sp = new SharePointAPI('', '', '');

If your installation requires NTLM Authentication, you can instead use:

    use Thybag\SharePointAPI;
    $sp = new SharePointAPI('', '', '', 'NTLM');

SharePoint Online users must use:

    use Thybag\SharePointAPI;
    $sp = new SharePointAPI('', '', '', 'SPONLINE');

All methods return an Array by default. SetReturnType can be used to specify that results should be returned as objects instead.

Reading from a List.

To return all items from a list use either

    $sp->read('');

or

    $sp->query('')->get();

To return only the first 10 items from a list use:

    $sp->read('', 10);

or

    $sp->query('')->limit(10)->get();

To return all the items from a list where surname is smith use:

    $sp->read('', NULL, array('surname'=>'smith'));

or

    $sp->query('')->where('surname', '=', 'smith')->get();

Querying a list

The query method can be used when you need to specify a query that is to complex to be easily defined using the read methods. Queries are constructed using a number of (hopefully expressive) pseudo SQL methods.

If you, for example, wanted to query a list of pets and return all dogs below the age of 5 (sorted by age) you could use.

    $sp->query('list of pets')->where('type','=','dog')->and_where('age','sort('age','ASC')->get();

If you wanted to get the first 10 pets that were either cats or hamsters you could use:

    $sp->query('list of pets')->where('type','=','cat')->or_where('type','=','hamster')->limit(10)->get();

If you need to return 5 items, but including all fields contained in a list, you can use. (pass false to all_fields to include hidden fields).

    $sp->query('list of pets')->all_fields()->get();

If you have a set of CAML for a specific advanced query you would like to run, you can pass it to the query object using:

    $sp->query('list of pets')->raw_where('Hello World')->limit(10)->get();

Adding to a list

To add a new item to a list you can use either the method "write", "add" or "insert" (all function identically). Creating a new record in a List with the columns forename, surname, age and phone may look like:

    $sp->write('', array('forename'=>'Bob','surname' =>'Smith', 'age'=>40, 'phone'=>'[phone]' ));

You can also run multiple write operations together by using:

    $sp->writeMultiple('', array(array('forename' => 'James'),array('forename' => 'Steve')));

Editing Rows

To edit a row you need to have its ID. Assuming the above row had the ID 5, we could change Bob's name to James with:

    $sp->update('','5', array('forename'=>'James'));/code>

As with the write method you can also run multiple update operations together by using:

    $sp->updateMultiple('', array(    array('ID'=>5,'job'=>'Intern'),array('ID'=>6,'job'=>'Intern')));

When using updateMultiple every item MUST have an ID.

Deleting Rows

In order to delete a row, an ID, as well as list name, is required. To remove the record for James with the ID 5 you would use:

    $sp->delete('', '5');

If you wished to delete a number of records at once, an array of ID's can also be passed to the delete multiple methods

    $sp->deleteMultiple('', array('6','7','8'));

Helper methods

The PHP SharePoint API contains a number of helper methods to make it easier to ensure certain values are in the correct format for some of SharePoint special data types.

dateTime The DateTime method can either be passed a text-based date

    $date = \Thybag\SharepointApi::dateTime("2012-12-21");

Or a unix timestamp

    $date = \Thybag\SharepointApi::dateTime(time(), true);

Troubleshooting

Unable to find the wrapper "https"

If you are getting this error it normally means that php_openssl (needed to curl https URLs) is not enabled on your web server. With many local web servers (such as XAMPP) you can simply open your php.ini file and uncomment the php_openssl line (ie. remove the; before it).

Note: If you are using SharePoint Online and having SSL errors, please pull the latest version which has changed from SSL v3 to TLS for SharePoint online connections.

Add this line to your composer.json file

    thybag/php-sharepoint-lists-api: dev-develop

You can perform CRUD (Create/Read/Update/Delete) operation with above SharePoint API.

Reference URL link: https://github.com/thybag/PHP-SharePoint-Lists-API