Jump to content

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


sonnieboy
Go to solution Solved by mac_gyver,

Recommended Posts

    //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 by sonnieboy
Link to comment
Share on other sites

  • Solution
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?

Link to comment
Share on other sites

// 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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.