I have been working hard lately to try to understand how MySQL databases work. MySQL is a very efficient way of storing data for your web pages and calling the data with simple php routines.

Here are some of my notes on getting mysql to work:

I had some text files with data that I needed to import to MySQL. You may have some csv files, too.

how to import a text file database (flat file) to a mysql database:

you should have a text file that has delimiters for fields. often the delimiter is TAB, or comma. csv files use a comma.

for instance, a row in your flat file should look like.
john doe,5557359,elm street,tinseltown

you need to create a mysql database, and within the database create a table that has the exact number of columns that are in the text file. this you can do using the graphical interface phpMyAdmin, or an sql query.

for instance:
CREATE TABLE mycustomers(customername VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY(customername), phonenumber INT NOT NULL, address VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, INDEX(phonenumber), city VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

if you need to create multiple tables, you can also make a php script to do this for you. First, you need a php script to connect to your database:

save that as connectdb.php

then make another file, e.g. create_table.php with the following code in it:

you can copy-paste that line as many times as you want, then just use a text editor to find/replace the table name with another table name, so you can create multiple tables from the same php script.

next, you need to import the file. i recommend that you name your text file exactly the same as the table you want to import it into. for instance, if you want to import to table mycustomers, name the file mycustomers.txt. if you want to import the file from your local server, use the following:

if you dont have file privileges, or if you don't know what file privileges are, then you need to use this mysql query:
LOAD DATA LOCAL INFILE '/home/path/path1/path2/mycustomers.txt' INTO TABLE mycustomers FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

if you want to have file privileges, do the following:
using ssh, logon to your server shell as root. type mysql. if you access is denied then you don't have root privileges. check with your hosting provider.

at the mysql prompt type:
GRANT FILE ON *.* TO 'username'@'localhost';
note, that in the above line, replace username with the username you want to grant the file privileges and leave localhost as is.

then this works:
LOAD DATA INFILE '/home/yourdir/dir2/dir3/dir4/file.txt'
INTO TABLE yourtablename FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

but you need these permissions for this to worK
files 644
all directories leading to the file 755

remember to set them back to their original ones after working.

if your query only loads one row into the MySQL database, check the that the line endings are correct, i.e. is it \n or \r ?

you need to use the absolute path to the file:
to find out a your absolute path, create a file absolute.php into the directory that you want to know the absolute path to, and paste the following code and run it:

before you create a table, plan it carefully. big tables are slow to alter afterwards. have an id auto_increment row so you can get adjacent rows in searches.

how do you add fields to existing tables? with this mysql query:
ALTER TABLE r ADD customer INT NOT NULL;
ALTER TABLE r ADD phonenumber INT NOT NULL;
ALTER TABLE r ADD id MEDIUMINT NOT NULL;
ALTER TABLE r ADD INDEX (id) AUTO_INCREMENT;

hope this helps!