sqlite to mysql using php

Question

I have an application that writes it's data to SQLite everyday. I now want to upload this data into my webhost to use. So I have a scheduled sFTP script to upload the .sqlite file to my webhost and then run another scheduled task to run the php code. I know that I can use SQLite with PDO but all my other data on the host is in MySQL, so I want to keep everything in the MySQL format. I have tried writing some php code that will read the sqlite file via PDO and then insert into the MySQL table again with PDO. The script will hopefully run on multiple tables to import so using foreach with Key/Values so I do not have to hardcode every single column in each table (about 30 columns per table)

However I have tried various things but just can not seem to find why the above code does not work problem so asking for help from much better minded users. Please view my code.

Thanks

<?php
ini_set('max_execution_time', 480); //480 seconds = 8 minutes
try {
// Connect to MySQL database 
$host = '127.0.0.1';
$db   = 'coredb';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdomysql = new PDO($dsn, $user, $pass, $opt);

// Connect to SQLite database in file
$file_db = new PDO('sqlite:frp.sqlite');
$file_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * from frp_teamdata';
$stmt = $file_db->prepare($sql);
$stmt->execute();
$sqliteresults = $stmt->fetch(PDO::FETCH_ASSOC);
foreach($sqliteresults as $results){
    foreach($results as $key => $value){
    $stmt = $pdomysql ->prepare("INSERT INTO frp_teamdata (name, value) VALUES (:name, :value)");
    $stmt->bindParam(':name', $key);
    $stmt->bindParam(':value', $value);
    $stmt->execute();
}
}

}
catch (PDOException $e) {
print 'Exception : ' . $e->getMessage();
}
?>

What kind of issue that exactly you got. Is there any errors? — Dharma Saputra
– Dharma Saputra, Commented May 17, 2018 at 16:31
"Doesn't work" is not a diagnostic. Can you better describe what it is you're trying to achieve, what output you're getting, if any, with particular attention paid to any errors generated by this code? What is this code supposed to do? It's very important to be as specific as you can, and this may require doing some additional investigation as to the root cause of your problem before posting a question. — tadman
– tadman, Commented May 17, 2018 at 18:07

Barns · Accepted Answer · 2018-05-17 19:11:33Z

1

Give this a try:

<?php
    ini_set('max_execution_time', 480); //480 seconds = 8 minutes
    try {
        // Connect to MySQL database 
        $host = '127.0.0.1';
        $db   = 'coredb';
        $user = 'root';
        $pass = 'password';
        $charset = 'utf8mb4';
        $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
        $opt = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ];

        $pdomysql = new PDO($dsn, $user, $pass, $opt);

        // Connect to SQLite database in file
        $file_db = new PDO('sqlite:frp.sqlite');
        $file_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = 'SELECT * from frp_teamdata';
        $stmt = $file_db->prepare($sql);
        $stmt->execute();
        $sqliteresults = $stmt->fetch(PDO::FETCH_ASSOC);

        //Prepare the statement only once!
        $stmtmysql = $pdomysql ->prepare("INSERT INTO frp_teamdata (name, value) VALUES (:name, :value)");
        //Bind the params only once -- I assume the keys and values are strings
        $stmtmysql->bindParam(':name', $key, PDO::PARAM_STR);
        $stmtmysql->bindParam(':value', $value, PDO::PARAM_STR);

        foreach($sqliteresults as $results){
            foreach($results as $key => $value){
                $stmtmysql->execute();
            }
        }
    }
    catch (PDOException $e) {
        print 'Exception : ' . $e->getMessage();
    }
?>

Note: I have not tried the code, so let me know if you have any issues.

edited May 17, 2018 at 19:11

answered May 17, 2018 at 19:02

Barns

4,8393 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter_Brown_USA Over a year ago

Thanks Barns, I see that I had the bindParam in the wrong place. However I have run your code and it works better than what I had however there is an error Exception : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'field list'?> because one of the key/value is an auto inc ID and not a string so I just need to chance the code a bit

Collectives™ on Stack Overflow

sqlite to mysql using php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related