How do I grab data from table on serverA and insert into table in serverB?

[sonnieboy]

    //Grab the WORKORDERID of the just closed work order. We will attach this to the user survey.
	$tsql = "SELECT [WORKORDERID] FROM [WORKORDER] WHERE STATUS='CLOSED' ORDER BY WORKORDERID DESC";
    $stmt = sqlsrv_query( $conn, $tsql);
       if( $stmt === false )
         {
          echo "Error in executing query.</br>";
          die( print_r( sqlsrv_errors(), true));
         }
         while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC))
			{
		     $idworkorder=$row["workOrderID"];
	        }
	        echo $idworkorder;
     // This customer just closed this work order
     $strSQL = "INSERT INTO CustomerFeedback(workOrderID, promptness,timleycompletion,courteousfriendly,cleanworkarea,issueresolved,performance,comments)
     VALUES ('". ms_escape_string($idworkorder)."','". ms_escape_string($promptness)."', '". ms_escape_string($timleycompletion)."', '". ms_escape_string($courteousfriendly)."', '". ms_escape_string($cleanworkarea)."', '". ms_escape_string($issueresolved)."', '". ms_escape_string($performance)."', '". ms_escape_string($comments)."')";
		$objQuery = sqlsrv_query($conn, $strSQL);

        if( $objQuery === false ) {
         die( print_r( sqlsrv_errors(), true));
        }
        else
        {
      ?>
	   <br><br><br><br><br>
		<div class="error_button">
		<div style="float: right; font-size: 18px; font-weight: bold; color: #FFF;" onClick="DisplayErrorTag('close')">x</div>
		 <?php render_error(array("title"=>"Thank you for your feedback!","body"=>'Go back to <a href="default.php">home</a>')); ?>
	    </div>

<?php
}
sqlsrv_close($conn);

?>

Greetings again experts,

 

I have never done this before and hope to get your generous response.

 

I have the following connections strings below:

// Connect to SQL Server database
include("../../connections/Prod.php");
include("../connections/Dev.php");

Example connection string for prod.php is shown below:

$connectionInfo = array( "UID"=>"user",
                         "PWD"=>"pass",
                         "Database"=>"myDB");

//echo $_SERVER['SERVER_NAME'];

switch (strtolower($_SERVER['SERVER_NAME'])) {
    case 'server1':
        $dbserver = 'serverName';
        break;

}


/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $dbserver, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
	 
}

?>

Dev.php has similar connection strings except they have different username/password and point to different servers and databases.

 

What we are trying to accomplish is grab the ID from WorkOrder table connecting to the server and database in prod.php.

 

Then insert the ID along with other fieldnames into another table in another server in dev.php.

 

So far, when I run the very first code above, I am getting an error that tablename in prod.php is invalid.

 

Am I using the connections strings incorrectly?

 

Thanks in advance

Edited July 20, 2015 by sonnieboy

[mac_gyver]

Dev.php has similar connection strings except they have different username/password and point to different servers and databases.

 

 

yes, but are you assigning the database connection resource to two different variables (or perhaps array elements) and using those two variables (or array elements) in the correct places in the code?

[sonnieboy]

// Connect to SQL Server database
include("../../connections/Prod.php");
include("../connections/Dev.php");

function render_error($settings = array("title"=>"Failed","body"=>"Sorry, your submission failed. Please go back and fill out all required information."))
	{ ?>
<h2><?php echo (isset($settings['title']))? $settings['title']:"Error"; ?></h2>
<p><?php echo (isset($settings['body']))? $settings['body']:"An unknown error occurred."; ?></p>
<?php
	}

// this function is used to sanitize code against sql injection attack.
function ms_escape_string($data) {
        if ( !isset($data) or empty($data) ) return '';
        if ( is_numeric($data) ) return $data;

        $non_displayables = array(
            '/%0[0-8bcef]/',            // url encoded 00-08, 11, 12, 14, 15
            '/%1[0-9a-f]/',             // url encoded 16-31
            '/[\x00-\x08]/',            // 00-08
            '/\x0b/',                   // 11
            '/\x0c/',                   // 12
            '/[\x0e-\x1f]/'             // 14-31
        );
        foreach ( $non_displayables as $regex )
            $data = preg_replace( $regex, '', $data );
        $data = str_replace("'", "''", $data );
        return $data;
    }

// Construct query
$workOrderID = $_GET['workOrderID'];
$promptness = $_REQUEST['promptness'];
$timleycompletion = $_REQUEST['timleycompletion'];
$courteousfriendly = $_REQUEST['courteousfriendly'];
$cleanworkarea = $_REQUEST['cleanworkarea'];
$issueresolved = $_REQUEST['issueresolved'];
$performance = $_REQUEST['performance'];
$comments = $_REQUEST['comments'];
//echo $comments;

    //Grab the WORKORDERID of the just closed work order. We will attach this to the user survey.
	$tsql = "SELECT [WORKORDERID] FROM [WORKORDER] WHERE STATUS='CLOSED' ORDER BY WORKORDERID DESC";
    $stmt = sqlsrv_query( $conn, $tsql);
       if( $stmt === false )
         {
          echo "Error in executing query.</br>";
          die( print_r( sqlsrv_errors(), true));
         }
         while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC))
			{
		     $idworkorder=$row["workOrderID"];
	        }
	        echo $idworkorder;
     // This customer just closed this work order
     $strSQL = "INSERT INTO CustomerFeedback(workOrderID, promptness,timleycompletion,courteousfriendly,cleanworkarea,issueresolved,performance,comments)
     VALUES ('". ms_escape_string($idworkorder)."','". ms_escape_string($promptness)."', '". ms_escape_string($timleycompletion)."', '". ms_escape_string($courteousfriendly)."', '". ms_escape_string($cleanworkarea)."', '". ms_escape_string($issueresolved)."', '". ms_escape_string($performance)."', '". ms_escape_string($comments)."')";
		$objQuery = sqlsrv_query($conn, $strSQL);

        if( $objQuery === false ) {
         die( print_r( sqlsrv_errors(), true));
        }
        else
        {
      ?>
	   <br><br><br><br><br>
		<div class="error_button">
		<div style="float: right; font-size: 18px; font-weight: bold; color: #FFF;" onClick="DisplayErrorTag('close')">x</div>
		 <?php render_error(array("title"=>"Thank you for your feedback!","body"=>'Go back to <a href="default.php">home</a>')); ?>
	    </div>

<?php
}
sqlsrv_close($conn);

?>

Thanks for your response.

 

Sorry but if I understand correctly, you are asking if the SELECT statement is pointing to the correct database connection resource and whether INSERT statement is doing same?

 

Here is the code that I am using.

 

The SELECT statement with starts with the comment:

 

//Grab the WORKORDERID of the just closed work order is using the ../../prod.php connection string

 

while the insert statement is using the ../dev.php connection string

 

So, if you don't mind, what do I need to change in the code here?

[sonnieboy]

Ok, I got it now.

 

Thanks for your help.

[mikosiko]

In your code you are using the variable $conn as your database connection for both your SELECT and for the INSERT. as mac_gyver mentioned before you should be using 2 different variable names for your database connections and use them in the right place