ODBC / MYSQL将查询结果从ODBC插入MYSQL中的数据库 [英] ODBC/MYSQL Insert a Query Result from ODBC to a databse in MYSQL

问题描述

我通过ODBC连接连接到云数据库：

I'm connecting to a cloud database through an ODBC connection:

$conn = odbc_connect('MYDATABASE','','');
if (!$conn) {
    exit("Connection Failed: " . $conn);
}

$sql = "SELECT DATETIME_ID, NAME, Sum(CNDROP) AS DATA
        FROM   MY_TABLE
        WHERE DATETIME_ID>='2014-09-28:00:00:00'
          and DATETIME_ID<='2014-09-28 23:00:00'
          and NAME IN ('CC2')
        GROUP BY DATETIME_ID, NAME ORDER BY DATETIME_ID, NAME";

$rs = odbc_exec($conn,$sql);
if (!$rs) {
    exit("Consulta fallida");
}

$result = odbc_exec($conn,$sql) or die(exit("Error en odbc_exec"));
print odbc_result_all($result,"border=1");

odbc_close($conn);

我可以获取数据并打印数据，但现在我需要将数据插入MySQL数据库导入我的电脑。

I can get the data, and print the data, but now I need insert that data into a MySQL database into my computer.

我不知道该怎么做，所以我需要帮助一个例子。我尝试在google上搜索，但没有什么有用的。

I don't have any idea how to do it, so I need help with an example. I tried to search on google but nothing was helpful.

推荐答案

选项1：

$ b b

SELECT的功能

function get_data_from_cloud(){
    $conn=odbc_connect('CLOUD','','');
    if (!$conn) {
        exit("Connection Failed: " . $conn);
    }
    $sql="SELECT DATETIME, NAME, CNDROP 
          FROM TABLE1 
          WHERE DATETIME>='2014-09-28 00:00:00' and 
                DATETIME<='2014-09-28 23:00:00' and 
                NAME IN ('PETER') 
          GROUP BY DATETIME, NAME 
          ORDER BY DATETIME, NAME";

    $result=odbc_exec($conn,$sql)or die(exit("Error en odbc_exec"));

    $data = array();
    while (odbc_fetch_row($result)) {
        $data[]=array('DATETIME' => odbc_result ($result, "DATETIME"), 
                      'NAME'=> odbc_result ($result, "NAME"), 
                      'CNDROP'=> odbc_result ($result, "CNDROP"));
    }
    return $data;   
}

INSERT功能
$ b

Function to INSERT

function insert_cloud_data($cloud_data=array()){
    $conn=odbc_connect('LOCAL','','');
    if (!$conn) {
        exit("Connection Failed: " . $conn);
    }
    foreach($cloud_data as $data){
        $sql = sprintf("INSERT INTO Prueba (DATIME, NAME, CNDROP)
                       VALUES ( '%s','%s','%s')",
                      $data['DATETIME'], $data['NAME'], $data['CNDROP']);
        $rs = odbc_exec($conn,$sql);    

        if (!$rs) {
            error_log("Consulta fallida");
        }   
    }

    odbc_close($conn);
}

---

SELECT的功能

function get_data_from_cloud(){
    $conn=odbc_connect('CLOUD','','');
    if (!$conn) {
        exit("Connection Failed: " . $conn);
    }
    $sql="SELECT DATETIME, NAME, CNDROP 
          FROM TABLE1 
          WHERE DATETIME>='2014-09-28 00:00:00' and 
                DATETIME<='2014-09-28 23:00:00' and 
                NAME IN ('PETER') 
          GROUP BY DATETIME, NAME 
          ORDER BY DATETIME, NAME";

    $result=odbc_exec($conn,$sql)or die(exit("Error en odbc_exec"));

    $data = array();
    while (odbc_fetch_row($result)) {
        $data[]=array(odbc_result ($result, "DATETIME"),
                      odbc_result ($result, "NAME"),
                      odbc_result ($result, "CNDROP"));
    }
    return $data;   
}

INSERT功能
$ b

Function to INSERT

function insert_cloud_data($cloud_data=array()){
    $conn=odbc_connect('LOCAL','','');
    if (!$conn) {
        exit("Connection Failed: " . $conn);
    }

    $sql = "INSERT INTO Prueba (DATIME, NAME, CNDROP)
            VALUES (?, ?, ?)";
    $stmt = odbc_prepare($conn, $sql);
    if(!$stmt) die("could not prepare statement ".$sql);

    foreach($cloud_data as $data){
        odbc_execute($stmt, $data);  
    }

    odbc_close($conn);
}

USAGE

$cloud_data = get_data_from_cloud();
insert_cloud_data($cloud_data);

这篇关于ODBC / MYSQL将查询结果从ODBC插入MYSQL中的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！