phpMyAdmin question

[likeglue]

Anyone know how to load a text file to populate a mySQL table with phpMyAdmin? Thanks...

[kkobashi]

Check out the LOAD DATA statement:

http://dev.mysql.com/doc/mysql/en/load-data.html

You can paste the sql statement in the sql tab of phpmyAdmin and execute it.

Good luck and let me know if this works!

[likeglue]

Supposedly there's a feature in phpMyAdmin that does this automatically, just naming the file without having to write the code. I just can't seem to find it on the interface or in the documentation.

[kkobashi]

. I just checked the current version of phpMyAdmin I downloaded. I am assuming what you mean is a text file containing entirely data, and no SQL statements. I can't think of why it would work anyway since there would not be enough information to create the table. For example, how is phpMyAdmin supposed to know the data type of the field, as well as the lengths?

I think what you are referring to is this:

If you create a SQL query file (a text file containing the schema) and in it there are INSERT statements, you can load the file.

Let's take an example to make this more concrete.

Suppose I have a SQL file called mySqlSchema.sql that contains this:

CREATE DATABASE MyDb DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;

USE MyDb;

CREATE TABLE Users (
username VARCHAR(20) NOT NULL,
pword VARCHAR(10) NULL,
email VARCHAR(40) NULL,
firstVisit TIMESTAMP NULL,
lastVisit TIMESTAMP NULL,
idUserGroup INTEGER UNSIGNED NULL,
language VARCHAR(10) NULL,
PRIMARY KEY(username)
) Type=MYISAM;

insert into users
(username, pword, email, firstvisit, lastvisit, idUserGroup)
values
('kerry', 'blah1', '[e-mail]', 20050101120000, 20050101120000, 1);

insert into users
(username, pword, email, firstvisit, lastvisit, theme, idUserGroup)
values
('helen', 'blah2', '[e-mail]', 20050101120000, 20050101120000, 1);

insert into users
(username, pword, email, firstvisit, lastvisit, theme, idUserGroup)
values
('john', 'blah3', '[e-mail]', 20050101120000, 20050101120000, 1);

insert into users
(username, pword, email, firstvisit, lastvisit, theme, idUserGroup)
values
('mitzi', 'blah4', '[e-mail]', 20050101120000, 20050101120000, 1);

You can load mySqlSchema.sql in phpMyAdmin. Click on the SQL tab, then look towards the bottom where you can load the file, and execute.

I realize this will require a lot of work to get the data into database. But there is also another solution you can try - write a PHP program to open up your text file and do the inserts.

OR

LOAD DATA using the SQL statement I gave you above.

[likeglue]

Thanks!