Link to home
Start Free TrialLog in
Avatar of gingera

asked on

How to add data to an existing MySQL database?



QUESTION: How do I import data into an existing MySQL database?

For example...

(1) co_code
(2) co_name
(3) co_sector
(4) co_phone


ABC, Tin Tin Publishing Inc., Publisher, NULL
DEF, Spiderman Drinks Inc., Beverage, NULL
FGH, Tummyache Inc., Medical, NULL
IJK, Expert Programming Inc., IT, NULL
LMN, Alcohol Overdose Inc., Medical, NULL

* Note that the existing table does not contain any value for "co_phone"

Now, I would like to add "co_phone" values into the existing table. I have a CSV text file with 2 fields (co_code & co_phone). However, the text file DOES NOT contain an entry for all the companies in the existing table. That is, the text file only has:

ABC, 512-8461843
IJK, 512-2315246
LMN, 512-6645879

How do I import the CSV text file into the existing table? What is the correct mysql query?

Thanks in advance.

Avatar of Bernard Savonet
Bernard Savonet
Flag of France image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is the MySQL form of data can do it wil PHP also through walking an array of data as well in a while loop or a foreach loop.

If in windows;
LOAD DATA INFILE C:/bobsfile.txt INTO TABLE mydatabase.mytable

If on a unix flavor;
LOAD DATA INFILE /your/unix/path/to/file INTO TABLE mydatabase.mytable
WARNING: do NOT import the "CoPhone data" directly into your main "Mytable" table, otherwise for those records that already exist, you will replace the complete record with ONLY the code an co_phone, just wiping away any pre-existing data.

So, another recommendation we forgot: DO BACKUP your complete base before experimenting any of our suggestions (but I presume you already knew that)
Coming back to your problem:
Some points which are not explicit, hence we were trying to guess directions:
- Is your problem just a "one-shot" problem, or something that is expeted to happen regularly?
== if regularly: you need to automate things along the lines indicated here, using php to read the csv data, populate a temp table and update the reference table; this should be the preferred solution if you have regular large batches of updates, so that updating the main table is handled effciciently by MySQL with a single SQL command; if you have just a few updates, you might probably go without the intermediate table, just updating record after record whil you read the csv data.
== if one-shot (or rare): you might consider simply using directly phpmyadmin to handle the import and update process (and of course doing backups before and after the update process!). IF you have backupped your data, you mmight consider doing within php an "optimize" for your main table or maybe all of your tables (don't do that without a safe backup)

- although in your example you explicitly state that the phone field is empty, are you going to erase then reload "mytable" frequently? the SQL query I gave does in fact ignore that the field co_phone is NULL, and simply updates it
  (if needed, you might add a WHERE clause to handle that
  WHERE (MyTable.co_code=CoPhone.co_code) AND (ISNULL(MyTable.co_phone)
Avatar of gingera


fibo, thanks a bunch!

Exactly what I am looking for!