1

I am trying to communicate to a MSSQL Server database with PHP from a woocommerce website (to fetch data like products, categories etc.). But I get no results, here is my code:

$connectionInfo = array( "UID"=>$uid,                            
                             "PWD"=>$pwd,                            
                             "Database"=>$databaseName); 
$conn = sqlsrv_connect( $serverName, $connectionInfo);  

$tsql = "SELECT * FROM eshopItemsTable";

$stmt = sqlsrv_query($conn, $tsql);
//uncomment to get some results : $table = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
while( $table = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) { 
    print_r($table);
        
}
sqlsrv_free_stmt( $stmt);  
sqlsrv_close( $conn);

with this code it prints nothing. But if I uncomment the line above while then I get some results but I dont get all of them (there are ~2000 items in the db but I get something like 10 items without the first one - which is obvious because I already consume the first row). What is the proper way to get all the results?

Improve this question
8
  • The code appears OK (in essence, it matches the example from PHP docs). Have you tried printing out errors - sqlsrv_errors() - either inside the while loop, or after the while loop, to see if there is actually some kind of error occurring? Also, if you just print a static string inside the while loop, do you get a result? (ie. can you confirm whether the while loop is actually occurring, even when you see "no results"?) Commented May 4, 2021 at 20:59
  • @Craig if I remove the commented line then it seems that it not getting into the while - it doesn't print static words. Also there are no errors printed with sqlsrv_errors() Commented May 4, 2021 at 21:03
  • it could be a timeout, as you are getting dqata from a remote server. Commented May 8, 2021 at 20:10
  • Is this the same database your website is running on? Or a different one? Commented May 10, 2021 at 8:33
  • @Mr.Jo different one Commented May 10, 2021 at 8:39

4 Answers 4

Reset to default
2
+25

Seems something wrong with the while.

The while is ok, but seems like ends before that he had to.

I've looking for some solutions and this man had the same problem:

SQLSRV doesn't fetch all rows

First of all gets the num of results:

$result_num = sqlsrv_num_rows( $result_count_res );

And then use a for instead of a while.

for($i = 0; $i < $result_num; $i++){
            $data[] = sqlsrv_fetch_array( $result_res, SQLSRV_FETCH_ASSOC);
        }

Consider also, a time out. Because you are attacking to another server...

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

it looks like its working. The problem is that is not the proper way I guess. But as long it works....
0

Try this way:

$stmt = sqlsrv_query($conn, $tsql);
sqlsrv_execute($stmt);

if ($stmt !== false) {
    while ($table = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        print_r($table);
    }
} else {
    echo '<pre>';
    print_r(sqlsrv_errors());
    echo '</pre>';
}
Improve this answer

2 Comments

I have to communicate with an external SQL Server database, WP_Query doesn't help
same thing... I have to use 2 sqlsrv_fetch_array
0

If I were you I would use the tools I already have - in case of WordPress it's wpdb. Normally when you use wpdb you're fetching from the WordPress databse but in our case we connect to a different database before using its methods:

$conn = new wpdb( $uid, $pwd, $databaseName, $serverName );

$results = $conn->get_results( 'SELECT * FROM eshopItemsTable' );

This handles the timeout stuff too - normally. Try out by logging out the results and tell me if it works. If yes, I think you can continue working with this.

Improve this answer

6 Comments

the thing is that the external database is MSSQL not mysql so I dont think that I am able to use wpdb
Have you tried it? And please add this is a new requirement to your question...
@Mr.Jo, it's already there: I am trying to communicate to a SQL Server database with PHP from a woocommerce website ...
So you have tried it out and it don't works - right?
If you have tried it and it don't works I would create a table eshopItemsTable on your WordPress database table. After this I would configure a database job on your MSSQL table which syncs with the table every day. This way you have a much faster way to get the results.
|
0

Please check with this code, where you can print errors step by step.

$serverName = "(local)";
$uid = "Enter Username";
$pwd = "Enter Password for SQL User";
$databaseName = "Enter name of the Database";
$connectionInfo = array( "UID"=>$uid,
                         "PWD"=>$pwd,
                         "Database"=>$databaseName);

$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )  
{  
     echo "Could not connect.\n";  
     die( print_r( sqlsrv_errors(), true));  
}   

$tsql = "SELECT * FROM eshopItemsTable";
$stmt = sqlsrv_query($conn, $tsql);
if( $stmt === false)  
{  
     echo "Error in query preparation/execution.\n";  
     die( print_r( sqlsrv_errors(), true));  
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))  
{  
      echo $row['ItemsName'].", ".$row['ItemsPrice']."\n";  
}
sqlsrv_free_stmt( $stmt);  
sqlsrv_close( $conn);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.