ODBC/MYSQL Insert a Query Result from ODBC to a databse in MYSQL

Question

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);

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

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.

Did you Google your question or looked at the manual? http://php.net/manual/en/function.odbc-exec.php — Funk Forty Niner, Oct 06 '14 at 20:13
@Fred-ii- Yes, i did. But i don't see any information to migrate a query into a table in a mysql database. — Luis Sira, Oct 06 '14 at 20:26
So Luis, the real question is, you want to use your query's results and then insert it based on that, correct? — Funk Forty Niner, Oct 06 '14 at 20:28
@Fred-ii- Yes, i want insert the query result in a another database (localhost mysql database), — Luis Sira, Oct 06 '14 at 20:33
See this Q&A on Stack http://stackoverflow.com/q/4241621/ and Google "insert in other table from query mysql" if you wish to further your research. — Funk Forty Niner, Oct 06 '14 at 20:40
@Fred-ii-Thanks Fred, but i already saw some of those comments/threads. My doubt is, in those examples/case, the tables are in the same "database" or in the same connection. I have 2 "connection", one in ODBC (the database from i get the data) and the other in mysql (the database where I wanna send the data). And if i do like "INSERT INTO THEOTHERTABLE (DATETIME_ID, NAME, CNDROP)", it gives me a error that it can't found THEOTHERTABLE in the database. — Luis Sira, Oct 06 '14 at 20:46
You're welcome Luis. I won't be able to help any further, sorry; those are types of queries I don't do. I will upvote the question though, and hope someone else will pick up on it. *Cheers* — Funk Forty Niner, Oct 06 '14 at 20:48
@Fred-ii-Thanks Fred, if i find the question i'll post it here. — Luis Sira, Oct 06 '14 at 20:52

meda · Accepted Answer · 2014-10-07T16:02:49.883

1

Option 1:

Function to 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;   
}

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);
}

Option 2:

Function to 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;   
}

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);

edited Oct 07 '14 at 16:02

answered Oct 07 '14 at 13:48

meda

45,103
14
92
122

Thanks a lot, i'm understanding the logic now, but i get those error "Undefined variable: Query_ID" and "odbc_result() expects parameter 1 to be resource, null given" Thanks a lot. – Luis Sira Oct 07 '14 at 15:40
love you man, Thanks a lot, it seem to be working now, by the way is "sprintf" not "sprint" :). Can i ask you another last thing? – Luis Sira Oct 07 '14 at 15:54
i'll vote for sure when i get more reputation. Ok, my last doubt is the DATETIME is in timestamp format wich reflect something like 2014-09-04 00:00:00, i'm ussing Microsoft Access and i need change that to format "Date" string cause it give me a error (Data type mismatch in criteria expression.), but i wanna show in that new "date string" the date exactly how timestamp "2014-09-04 00:00:00", I do not know if i explained well. Thanks meda. – Luis Sira Oct 07 '14 at 16:10
@LuisSira `INSERT INTO Prueba (DATIME, NAME, CNDROP) VALUES ( #%s#,'%s','%s'` – meda Oct 07 '14 at 16:14
Syntax error in date in query expression '#2014-09-28 00:00:00.000000' – Luis Sira Oct 07 '14 at 16:20
@LuisSira where exactly are you getting this error, in access or while inserting – meda Oct 07 '14 at 16:26
on line 45 wich is in function insert_cloud_data. this is the line 45 $rs = odbc_exec($conn,$sql); – Luis Sira Oct 07 '14 at 16:29
no @LuisSira `Data type mismatch in criteria expression.` that one – meda Oct 07 '14 at 16:31
Oh, while inserting, cause i access and get a timestamp value, and my column in Microsfot Access isn't in a timestamp format, i can put it in "string" or "date" format, so is while i'm inserting the data, cause exist a mismatch. – Luis Sira Oct 07 '14 at 16:35
@LuisSira do you still need help, did you figure this out. let me know – meda Oct 08 '14 at 18:10
i change it to a mysql database through ODBC, and mysql support timestamp format, but maybe i should have to change the format later, not sure, but if i get in trouble i can ask you again? Thanks by the way, helped me so much. – Luis Sira Oct 08 '14 at 18:19
Hello meda, you know about jquery $_POST/$_GET? – Luis Sira Oct 12 '14 at 01:16
Yes I saw you asked a question but did not say what didnt work – meda Oct 12 '14 at 01:20
i asked a new question where i explain it better, i can send now the var to one page to another page without refreshing, i need is get the result data and load it in a javascript var in the first page. – Luis Sira Oct 13 '14 at 01:41

score 0 · Answer 2 · answered Oct 06 '14 at 23:34

0

Here is different approach.

Create $conn1 (cloud) and $conn2 (localhost). Query the $conn1 then use php while and insert command into $conn2.

$conn1 = (cloud);
$conn2 = (localhost);

$query = "SELECT ...";
$result = odbc_exec($conn1,$query);
while( fetch result data ) {
   $query = "INSERT ....";
   odbc_exec($conn2,$query);
}

answered Oct 06 '14 at 23:34

Li Kia Chiu

213
1
6

@Luis Sira For your [INSERT](http://www.w3schools.com/php/php_mysql_insert.asp) statement you need to add the VALUES behind by using [ODBC_RESULT](http://php.net/manual/en/function.odbc-result.php). – Li Kia Chiu Oct 07 '14 at 19:23

ODBC/MYSQL Insert a Query Result from ODBC to a databse in MYSQL

2 Answers2

Option 1:

Option 2:

Linked