I'm trying to do something that seems relatively simple, and probably is for those more experienced, but I'm still new at this and could use a little help.

I want to pull table data from another server on our LAN using ODBC and dump it into a table on my local machine(localhost). I have no problem connecting using ODBC and can run queries with no problems. I just don't know how to get it working. Here's what I've tried so far.

/* ************* CONNECTIONS ************* */
//connection variables
$username	="server";
$password	="s70van";
$ODBC_dsn	="13540";
$LOCAL_host	="localhost";
$LOCAL_database ="conduit";

//ODBC connection for the accounting database
$ODBC_conn	= odbc_connect($ODBC_dsn,$username,$password);

//Check ODBC connection for errors
if (!$ODBC_conn)
{
exit("Could not connect to ODBC database");
}

//LOCAL connection for the localhost database
$LOCAL_conn	= mysql_connect($LOCAL_host,$username,$password);

//Check LOCAL connection for errors
if (!$LOCAL_conn)
{
exit("Could not connect to localhost");
}

//Select LOCAL database --- */
mysql_select_db($LOCAL_database, $LOCAL_conn) or die("Can't select LOCAL database.");




/* ************* TABLE DATA TRANSFER ************* */
//Insert into query
$sql="INSERT INTO companies (sCompName)

SELECT sCompName FROM tcompany";

//Execute the insert into query
$result=odbc_exec($ODBC_conn,$sql);

//Check insert into query for errors
if (!$result)
{exit("Error in SQL");}



//Close odbc connection
odbc_close_all();

Any help would be greatly appreciated🙂 I've googled like an animal and can't find anything on this.

    You're not going to be able to do this in one query across two connections. You should do the query from odbc and collect the results into an array. Then loop through the results to insert them locally.

      Thank's LordShryku for pointing me in the right direction! I really really appreciate it!!

      I've got data moving from the odbc data source and populating my local table, but it only inserts one line. It's a definitely a total noob thing because I'm a total noob🙂

      I know its a prob with the loop but I'm totally lost. Any suggestion would be awesome.
      Here's the code...

      //query and results
$ODBC_query  ="
SELECT sPONum 
FROM tpurordr 
WHERE sPONum > 1";

$ODBC_result =odbc_exec($ODBC_conn,$ODBC_query)
or die("Could not execute the ODBC_query". mysql_error());

//insert loop
foreach ($ODBC_row = odbc_fetch_array($ODBC_result) as $value)
{
$insert= "INSERT INTO test_purchase_orders (sPONum) VALUES ('$value')";
mysql_query($insert)
or die("Could not execute the insert query - ". mysql_error());

      Thanks in advance to anyone that can help.

        Try a while loop instead

        while($row = odbc_fetch_array($ODBC_result)) {
   $insert= "INSERT INTO test_purchase_orders (sPONum) VALUES ('".$row['sPONum']."')";
   mysql_query($insert) or die("Could not execute the insert query - ". mysql_error()); 
}

          LordShryku you ROCK!!!!!

          I've been really struggling with this project for weeks and this was the final piece.

          Thank You!

          Here's the final working code...

          //connection variables
$username	="server";
$password	="s70van";
$ODBC_dsn	="13540";
$LOCAL_host	="localhost";
$LOCAL_database ="conduit";

/* ********************* LOCAL AND ODBC CONNECTIONS START ********************* */
//connection to the accounting database
$ODBC_conn	= odbc_connect($ODBC_dsn,$username,$password);

//check ODBC connection for errors
if (!$ODBC_conn)
{exit("Could not connect to $ODBC_dsn database - ". mysql_error());}

//connection to localhost
$LOCAL_conn	= mysql_connect($LOCAL_host,$username,$password);

//check LOCAL connection for errors
if (!$LOCAL_conn)
{exit("Could not connect to $LOCAL_host - ". mysql_error());}

//select localhost database
mysql_select_db($LOCAL_database, $LOCAL_conn) 
or die("Could not select $LOCAL_database database - ". mysql_error());
/* ********************* LOCAL AND ODBC CONNECTIONS END ********************* */

/* ********************* INSERT FROM ODBC START ********************* */
//query and results
$ODBC_query  ="
SELECT sPONum 
FROM tpurordr 
WHERE sPONum > 1";

$ODBC_result =odbc_exec($ODBC_conn,$ODBC_query)
or die("Could not execute the ODBC_query". mysql_error());

//insert loop
while($row = odbc_fetch_array($ODBC_result)) {
   $insert= "INSERT INTO test_purchase_orders (sPONum) VALUES ('".$row['sPONum']."')";
   mysql_query($insert) or die("Could not execute the insert query - ". mysql_error());
} 
/* ********************* INSERT FROM ODBC END ********************* */

/* ********************* CLOSE ODBC AND MYSQL CONNECTIONS ********************* */
mysql_close();
odbc_close_all();
            Write a Reply...