Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

Import Data - Remove Records - Export Data

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

Import Data - Remove Records - Export Data

Postby nevodj » Sun Aug 10, 2014 5:17 pm

Hello,

I am a bit of a newbie to php but I am wondering the best way to achieve this and would be glad of any tips.

I want a php page where I can import a CSV or paste into a multiline box, then push a button which checks each imported line against a mysql database "blacklist" and removes any records that exist in the database, and then exports / displays the list without the blacklisted records that existed in the database.

Should I import the entire CSV to a temp table then do the removal using SQL queries... i.e delete from temptable where id in blacklisttable, then export temp to csv?

This may slow it down if the file has to be inserted to sql. Is there a better way?
nevodj
New php-forum User
New php-forum User
 
Posts: 11
Joined: Thu Jun 21, 2012 3:43 pm

Re: Import Data - Remove Records - Export Data

Postby landi » Mon Aug 11, 2014 12:00 am

Hi,
1) The hard work here is the import of the CSV - handling the various formats, with and without quotes requires a bit of thought. In particular, do not use explode(",", $sLine) to break up a line into columns as it will not work if any of the columns contain embedded commas. Either parse the line yourself or try the various php functions e.g. str_getcsv().

2) Where you import the data to is somewhat secondary. The choices are:
a) Into a table as you suggest
b) Into an array. [BTW, php handles multi dimension arrays as arrays of arrays so in this option, the array will be an array of 'lines' and each 'line' will be an array of columns.]

Which is best? I think it depends on the number of rows. If you import into an array you save the INSERT time, but for each array element (line) you have to do a SQL lookup. If you import into a table then you pay the INSERT cost, but only 1 sql query is needed to delete the backlist (and 1 delete matching N rows is way faster than N lookups).

There are MANY php array functions and I'm sure you could find one that would let you import the csv into an array, then import the blacklist into an array and then 'subtract' the two. Such an approach would need lots of comments IMHO whereas the sql approach is much more self-documenting.

If you want to re-display the data to a human and maybe allow them to 'tweak' which rows are deleted etc, then the SQL approach wins hands-down as it can preserve the data across page calls.

E.g. instead of the DELETE query you could have a 'delete_me' column in your data and on the first pass you set to 'Y' according to the blacklist, (update temptable where id in blacklisttable). Then you display the results to a human. They can tick or untick rows. Then press delete and you do "delete from temptable where delete_me = 'Y' "

Remember Rule 1: Make it work before you make it work fast. And Rule 2: If it works, but no one can understand your code, then it doesn't work. :)

Hope that helps.

-A
landi
New php-forum User
New php-forum User
 
Posts: 246
Joined: Thu Mar 15, 2012 3:59 pm


Return to PHP coding => General

Who is online

Users browsing this forum: Google [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron