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:
Easy

Other People Are Reading

Instructions

  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 (

    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,

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

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

    ) ENGINE = MYISAM;

  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:

    <?php

    $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:

    fclose($fh);

    ?>

  6. 6

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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