Everyday I receive a csv file with phone numbers. I've built an array that grabs the data from the csv. What I need to do is loop through the array and search a mysql database for matches. Can one of your gurus be so kind as to point me in the right direction?
Add a comment
|
2 Answers
I would suggest to create temporary table, then load your numbers into this table and then just simply select & join to your table. You can insert your phones in program (multilple rows in one INSERT or if you has access to server shell, you can do some script like this:
-- you can use TEMPORARY table in one transaction or you can just
-- make sure that the table exists and flush it before import
CREATE TABLE IF NOT EXISTS phonenumbers (
number varchar(10) primary key
);
TRUNCATE TABLE phonenumbers;
LOAD DATA LOCAL INFILE 'numbers.csv' INTO TABLE phonenumbers;
-- here in script or call it from your program to work with data
-- also you can add INTO OUTFILE 'output.csv' to export filtered data
-- into outfile
SELECT addressbook.* FROM addressbook LEFT JOIN phonenumbers on (addressbook.phone = phonenumbers.number);
Comments
If you're able to, import the csv into a temporary table using LOAD DATA and write a query that matches the table against your phone numbers.
1 Comment
Kermit
It's a better process all around as this only requires database interaction. You do not have to worry about arrays or loops.
