Sharepoint SQL Table with PHP

[whiteboikyle]

So we have Sharepoint and I am trying to extract certain information from the sharepoint SQL and code a page to output that data.

 

This is what i have:

 

<?php
  $serverName = "127.0.0.1";
  $connectionInfo = array("Database"=>"SharedServices_DB");
  $conn = sqlsrv_connect($serverName, $connectionInfo);
  var_dump($conn);
  echo("<br />");
  $sql = "SELECT TOP 100
    Name = upf.PreferredName,
    Email = upf.Email,
    Modified = upf.LastUserUpdate,
    Property = pl.PropertyName,
    Value = upv.PropertyVal
    FROM
    dbo.UserProfile_Full upf,
    dbo.UserProfileValue upv,
    dbo.PropertyList pl
    WHERE
    upv.RecordID = upf.RecordID
    AND pl.PropertyID = upv.PropertyID
    ORDER BY Name ASC";
  $stmt = sqlsrv_query($conn, $sql);
  if( $stmt === false ) {
   die( print_r( sqlsrv_errors(), true));
  }
  var_dump($stmt);
  echo("<br />");
  $row_count = sqlsrv_num_rows( $stmt );
  var_dump($row_count);
  while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
  var_dump($row);
  //$second = $row['Department'];
  //if($first != $second){
   //if($row['Name'] != NULL || $row['Email'] != NULL){
    echo('<div id="department">'.$row['Name'].'</div><hr />');
    //$first = $row['Department'];
   //}
  }
  sqlsrv_free_stmt( $stmt);
?>

 

I know for sure the $sql works I manually did the query on the sql server and it output all the data i needed.

 

But on the PHP site it is coming up blank not outputting anything.

 

Here are the results of the var_dump

 

 

resource(1) of type (SQL Server Connection)
resource(2) of type (SQL Server Statement)
bool(false)

[mac_gyver]

as to the bool(false), have you read the documentation for sqlsrv_num_rows()?

 

If a forward cursor (the default) or dynamic cursor is used, FALSE is returned.

 

you can however use sqlsrv_has_rows() to find if php thinks the query matched any rows or not.

 

as to why the query apparently didn't match any rows and your while(){} loop is being skipped, about the only thing that comes to mind is you have multiple database names and the database selected in your code above is not the same database you used when you directly ran the query.

Edited June 28, 2014 by mac_gyver

[whiteboikyle]

as to the bool(false), have you read the documentation for sqlsrv_num_rows()?

 

 

you can however use sqlsrv_has_rows() to find if php thinks the query matched any rows or not.

 

as to why the query apparently didn't match any rows and your while(){} loop is being skipped, about the only thing that comes to mind is you have multiple database names and the database selected in your code above is not the same database you used when you directly ran the query.

I made sure it was the right directory.. I even did

$row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);

  var_dump($row);

 

and still comes up bool false..

 

Im thinking maybe a security to the tables?

[mac_gyver]

your sql query is running without errors (the var_dump($stmt); would be a false if there was any sort of permission/database/table problems.)

 

all indications are that for the database you are using, the query is matching zero rows. if you were putting an external value into the WHERE clause using a php variable, i would suspect you have some white-space as part of the value in your query and it isn't matching the actual data in the table, but that's not the case here. are you sure the $sql query you are showing in this thread is EXACTLY the same query (including all capitalization/letter-case) you ran manually?

 

about the only other thing that comes to mind is a php error is occurring (perhaps you have a mix of versions of drivers/extensions and the sqlsrv_fetch_array() statement is throwing a php error.) do you have php's error_reporting set to E_ALL and display_errors set to ON and you know for a fact that ALL php errors are being shown?

[whiteboikyle]

your sql query is running without errors (the var_dump($stmt); would be a false if there was any sort of permission/database/table problems.)

 

all indications are that for the database you are using, the query is matching zero rows. if you were putting an external value into the WHERE clause using a php variable, i would suspect you have some white-space as part of the value in your query and it isn't matching the actual data in the table, but that's not the case here. are you sure the $sql query you are showing in this thread is EXACTLY the same query (including all capitalization/letter-case) you ran manually?

 

about the only other thing that comes to mind is a php error is occurring (perhaps you have a mix of versions of drivers/extensions and the sqlsrv_fetch_array() statement is throwing a php error.) do you have php's error_reporting set to E_ALL and display_errors set to ON and you know for a fact that ALL php errors are being shown?

It must be a security issue than. I will look into. Its a direct copy and paste in the query and it pops up rows.. And E_ALL is on.. No errors.

[boompa]

Here's the documentation for sqlsrv_num_rows: http://www.php.net/manual/en/function.sqlsrv-num-rows.php

 

Your call to this function is returning FALSE. Reading the linked page above, we see the following in the Return Values section:

 

 

 

Returns the number of rows retrieved on success and FALSE if an error occurred. If a forward cursor (the default) or dynamic cursor is used, FALSE is returned.

 

Please take note of the second sentence. You need to change how you call sqlsrv_query, specifically the options you are sending. Here is the manual for that function: http://www.php.net/manual/en/function.sqlsrv-query.php

[whiteboikyle]

Here's the documentation for sqlsrv_num_rows: http://www.php.net/manual/en/function.sqlsrv-num-rows.php

 

Your call to this function is returning FALSE. Reading the linked page above, we see the following in the Return Values section:

 

 

 

 

Please take note of the second sentence. You need to change how you call sqlsrv_query, specifically the options you are sending. Here is the manual for that function: http://www.php.net/manual/en/function.sqlsrv-query.php

Not really sure what to put for the "Options" Array to fix this query though.

[boompa]

Again, the documentation for sqlsrv_num_rows says

 

 

 

The statment[sic] resource must be created with a static or keyset cursor.

 

And, if you look on the sqlsrv_num_rows page, there's even...an example.

Edited June 30, 2014 by boompa