Techie Titbits

How do I import a CSV file into a MySQL database using phpMyAdmin?
Writing sql code is not required. Just use the phpMyAdmin interface for uploading the csv file.

1. I assume that you have created
   1. a mysql database and within it a table with appropriate fields
   2. a csv file with entries arranged in the same order as the fields in the mysql database table (eg Suppose you have a table with fields name, age, city. Your csv file might look like -
      
   smith,12,washinton
   willy,13,chicago
   maria,11,tokyo) assuming you use ',' as field separator and '\n' as row separator
   
   
   
2. Go to phpMyAdmin (It should be located in your host account control panel under database section)
3. Click on the mysql database instance you are interested in.
4. Click on the table to which you want to import the data. (Remember this step, just clicking on the database is not good enough.)
5. Click on the 'import' tab.
6. This will open an interface as shown below -
1. Click 'Browse' button to select the csv file on your system which you want to upload
   
2. Choose 'CSV using LOAD DATA'
3. Fields terminated by: ,
4. Fields enclosed by: [delete default value, leave blank]
5. Fields escaped by: [delete default value, leave blank]
   
6. Lines terminated by: [leave default value]
   
7. Column names: name,age,city
   





7. Click the 'GO' button and you are done. phpMyAdmin will show you exactly what sql query was executed to upload the csv file into your mysql database table. You can now browse your database to check proper insertion of the csv file.

How do I connect to a MySQL server using PHP?

If you are planning to do some PHP coding, most likely you will need an answer to this question. Here is how you do it.

<?php
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'books';
mysql_select_db($dbname);

//Do whatever you have to do with the database eg query, update, insert etc

mysql_close($conn);
?>

Hopefully that works like a breeze. If you are still having issues, please note that,

• 'localhost' is used as the server address when your php script is running on the same server which is often the case. However, if your database is located on a different server, you will have to use "$dbhost = 'othername.otherdomain.com:3306';" where 3306 is the default port number for mysql. It might be different, check with the host.
• Often your webhost prepend your database name and username with your username with the webhost. Suppose you use host your database with xyzwebhost.com using a username 'abc'. You log in using your 'abc' username and corresponding password and create a database named 'mydb' and create an user 'mydbuser'. The xyzwebhost will actually create a database named 'abc_mydb' and an user named 'abc_mydbuser'. In your php script you have to use 'abc_mydb' and 'abc_mydbuser'.
• Remember creating a database and a dbuser is not enough, you have to add the user to the appropriate database with proper permission.
• Selecting a database after you connect to the server is important otherwise your queries have no effect.
• Remember to close the connection once you are done.