How to Import From CSV to MySQL With PHP Script

Written by steve mcdonnell Google
  • Share
  • Tweet
  • Share
  • Email

After you create a MySQL database, you need to populate it with data. The data for the database often are contained in a comma-delimited, or CSV, file and need to be imported into PHP and written out to the MySQL database. This is a very straightforward task using the built-in functions that PHP has that make it easy both to import from a CSV file and to output to a MySQL database.

Skill level:


  1. 1

    Navigate to a MySQL command prompt or use a utility such as phpMyAdmin to issue a SQL command. Create the MySQL database. For example, type:

    CREATE DATABASE capitals DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

  2. 2

    Create the MySQL table to hold the data you will import from the CSV file. Mirror the structure of the CSV file if practical. For example, type:

    CREATE TABLE capitals.capitals (


    state VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,

    city VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL


  3. 3

    Create a MySQL user to access the database and grant privileges to the database you created. For example, type:

    GRANT ALL PRIVILEGES ON capitals.* TO capitals@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;

  1. 1

    Create a new PHP file using an editor or the Notepad. Open the CSV file you are going to import. For example, type:


    $fh = fopen("data.csv") || die("Could not open data file!");

  2. 2

    Use the "fgetcsv" function to read each line of the file as comma-delimited data and receive an array of the comma-delimited values. For example, type:

    while (($data = fgetcsv($fh))) {

  3. 3

    Create a MySQL query statement using the comma-delimited values. For example, type:

    $query = sprintf('INSERT INTO capitals (id, state, city) VALUES (null, "%s", "%s")', $data[0], $data[1]);

  4. 4

    Run the MySQL query to insert the data into the table. For example, type:

    $result = mysql_query($query);

    if (!$result) die("Error inserting data into MySQL!");


  5. 5

    Close the import file when all the data has been inserted. For example, type:



  6. 6

    Save the file and run it in a browser. Check the MySQL database to ensure the data imported correctly.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.