Woocommerce Question posted in
The official Woocommerce documentation can be found here.

PHP query to SQL server database (wordpress) – Woocommerce

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?

Tags:

4

Answers


  1. 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>';
}
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
  3. 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);
    Login or Signup to reply.
  4. 0

    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…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search