Drop and Create table based on text file

[Twitch]

I was asked by my employer to come up with a way to verify a text field using a list of account numbers.

The list of numbers are in a comma delimited text file that I want to create a table from.  Each time the text file is generated, it has a few more account numbers, so I figure the easy way is the drop the table and create a new one from the text file.

 

I've found many scripts that create a table from a text file, or have all this confusing code to update a table but I haven't found the right combination I'm looking for.  Thought I'd find millions of examples doing exactly what I want, but they are either overkill or not quite what I need.

Basically a text file called "numbers.txt" with lines of text like below

123122,NameOfSomething

234544,NameOfSomethingElse

 

Just two columns is all the table needs.

 

I finally gave up and came here because in the past when I've asked a question here, someone brilliant has always had the perfect (and usually simplest) code example as my php skills are decent, but still have some learning to do...haha

 

Thanks in advance,

Twitch

[laffin]

are account numbers unique?

if so use the first as a primary key field non null unique field.

 

you dont need to recreate the db, if new accts are added, but if removed, you will (or u will be building a system with lots of extra code checking db against txt)

 

when u insert items into the db, since the acct number is set as a unique field, it will fail to insert those duplicate accts. u can verify with the sql_error code.

 

 

[Twitch]

Thanks for the quick reply laffin, yes they are unique, but I'm not sure if I follow what you're saying.  Course it could be that I am just now getting around to having my coffee.  I'm definitely like the guy in the commercial who can't function till he's had his coffee...ha ha

[laffin]

CREATE TABLE accts (
acct INTEGER NOT NULL PRIMARY KEY UNIQUE,
name VARCHAR(45) NOT NULL DEFAULT ''
);

 

since accts will be unique, you dont need an autoincrementing id. just use the account number,

when inserting accts to the db, any duplicate will fail the UNIQUE contraint, so wont get inserted, and returns an error code.

 

[Twitch]

Ah ok, awesome, I'll give this a whirl.  Once again this site doesn't disappoint.  Hopefully, my skills will be good enough soon to contribute more.

 

Thanks again my friend.