Android Question Upload records(50k rows) to MySQL via php script

microbox

Active Member
Licensed User
Longtime User
What will be the best approach to save records from sqlite table to MySql? The table consist of 6 columns and at least 50k rows.
My initial idea is to convert the records into map or json file and use php script, but not really sure how to. Hope anyone here can help out.

Thanks,
Joe
 

JohnC

Expert
Licensed User
Longtime User
That sounds like a LOT of data. If it is a high megabytes worth of data and if you have control over the backend pages (meaning you can develop your own pages on the server), one thought might be to zip the data on the device, then send/FTP the ZIP file to the server and have the backend unzip and import the data.
 
Upvote 0

microbox

Active Member
Licensed User
Longtime User
That sounds like a LOT of data. If it is a high megabytes worth of data and if you have control over the backend pages (meaning you can develop your own pages on the server), one thought might be to zip the data on the device, then send/FTP the ZIP file to the server and have the backend unzip and import the data.
Thanks for the help, I found that the size of the file when converted to csv to almost 1Mb.
 
Last edited:
Upvote 0

JohnC

Expert
Licensed User
Longtime User
Thanks for the help, I found that the size of the file when converted to csv to almost 1Mb.
That's not very big, so zipping it wont help much.
 
Upvote 0

drgottjr

Expert
Licensed User
Longtime User
you don't say how much access you have to either
database. in theory, you can dump your sqlite db to
a text file, zip it, upload the file to your php server and create
a table or db from the file. an sqlite dump should be readable
by mysql. sqlite is relatively compliant with sql standards. there
are lots of other possibilities, but they depend on where you
are in the chain of command.

if you only have access to a few canned php scripts from
which to choose, and if your only access to the sqlite db is
your phone, you're pretty reduced to uploading 50k records
1 by 1 to php.

what are we looking at here?
 
Upvote 0

microbox

Active Member
Licensed User
Longtime User
you don't say how much access you have to either
database. in theory, you can dump your sqlite db to
a text file, zip it, upload the file to your php server and create
a table or db from the file. an sqlite dump should be readable
by mysql. sqlite is relatively compliant with sql standards. there
are lots of other possibilities, but they depend on where you
are in the chain of command.

if you only have access to a few canned php scripts from
which to choose, and if your only access to the sqlite db is
your phone, you're pretty reduced to uploading 50k records
1 by 1 to php.

what are we looking at here?
To dump the selected sqlite table as text file and upload it to the server sounds okay. Just need to search the script for the php part. My idea was to convert the table records json file and upload it to the server and php code( I have access) will save it to the Mysql database but don't sure how to start and if it is better solution. I think uploading 1 by 1 will take too much time.
 
Upvote 0

drgottjr

Expert
Licensed User
Longtime User
if this is a one-shot deal, 1 by 1 is probably the easiest for you. i'm guessing you already have a php script which allows create (and/or insert) from an app. this will entail nothing or very little to learn. take a nap, and it's over.

otherwise, you have to figure out how to "dump" sqlite to json (all on you to learn and test). then you have to figure out how to take a big json file and turn it into something php can convert to mysql. (again, on you to learn and test).

i don't have my slide rule handy, but i think it might take you longer to research, learn and test the necessary new tricks than if you just started uploading the records right now. just sayin'...
 
Upvote 0

microbox

Active Member
Licensed User
Longtime User
if this is a one-shot deal, 1 by 1 is probably the easiest for you. i'm guessing you already have a php script which allows create (and/or insert) from an app. this will entail nothing or very little to learn. take a nap, and it's over.

otherwise, you have to figure out how to "dump" sqlite to json (all on you to learn and test). then you have to figure out how to take a big json file and turn it into something php can convert to mysql. (again, on you to learn and test).

i don't have my slide rule handy, but i think it might take you longer to research, learn and test the necessary new tricks than if you just started uploading the records right now. just sayin'...
Thanks for the time...I'll do some reading try out some test. I appreciate the effort and response.
 
Upvote 0

Biswajit

Active Member
Licensed User
Longtime User
After uploading the CSV file run a foreach loop for every row and create a query string like this,
SQL:
INSERT INTO `table_name`
(`field_1`, `field_2`, `field_3`)
VALUES
/*generate this from php foreach loop*/
('data','data','data'),
('data','data','data'),
('data','data','data'),
...
('data','data','data');

After the foreach just run the query. So all of your data will be inserted from a single query.
 
Upvote 0

microbox

Active Member
Licensed User
Longtime User
After uploading the CSV file run a foreach loop for every row and create a query string like this,
SQL:
INSERT INTO `table_name`
(`field_1`, `field_2`, `field_3`)
VALUES
/*generate this from php foreach loop*/
('data','data','data'),
('data','data','data'),
('data','data','data'),
...
('data','data','data');

After the foreach just run the query. So all of your data will be inserted from a single query.
I will give it a try to search and learn for it....thank you for helping out.
 
Upvote 0
Top