WordPress / Web Development Tutorials
(Best WordPress Tutorials)

CSSHTMLJavaScriptjQueryMySQLPHPSilvaTechnologiesWooCommerceWordpress
Silva Web Designs - Blog

How to Upload a CSV file into a MySQL Database Using PHP

Are you wondering how you can upload a CSV file into a MySQL database using PHP? Well if you are, you are in the right place!

In our example, we are going to be using Bootstrap and mysqli for the database connection. See below the full script needed in order to complete this action:


<div id="primary" class="content-area">
	<main id="main" class="site-main" role="main">

    <div class="container">

      <div class="section">

        <?php

        $db = new mysqli('localhost', 'username', 'password', 'databasename');

        if($db->connect_errno > 0){
            die('Unable to connect to database [' . $db->connect_error . ']');
        } 


          // File Upload

          if (isset($_POST['submit'])) {
             
                   
              // Import the uploaded file to the database

              $handle = fopen($_FILES['filename']['tmp_name'], "r");

               while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

                  $sql = "INSERT INTO `tablename` (`field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`) VALUES ($data[0], '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', $data[6]);";

                  if(!$result = $db->query($sql)){
                      die('There was an error running the query [' . $db->error . ']');
                  }
              }

              fclose($handle);

              echo "Import Completed Successfully"; 
              mysqli_close($db);
          }

          else {
                
             echo "<center>";
              echo "<div class='col-md-12'>";

              echo "<h1>Upload Vouchers CSV File. </h1><br />\n"; 
              echo "<form enctype='multipart/form-data' action='/voucher-import/' method='post'>"; 
              echo "File name to import:<br /><br />\n";
              echo '<input type="file" class="filestyle" name="filename" data-iconName="glyphicon-inbox" required><p>&nbsp;&nbsp;</p><p></p>';
              echo "<input class='btn'  type='submit' name='submit' value='Import'><p></p></form>";
          }
             echo "</div>";
             echo "</center>";
          ?>
          </div>

      </div>


	</main><!-- #main -->
</div><!-- #primary -->

In the example provided above, we are inserting a CSV file with 7 columns where all rows within the CSV are looped and inserted into the database.

The main things that will need editing are first the database connection, so you will need to replace the following line:


$db = new mysqli('localhost', 'username', 'password', 'databasename');

The other part that will need changing is INSERT statement shown below:


$sql = "INSERT INTO `tablename` (`field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`) VALUES ($data[0], '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', $data[6]);";

So firstly, you would change the tablename to match the table you would like to insert the data to. If you have less/more columns, you will have to remove/add fields and values to match how many columns you have in your database.

If you are inserting NULL values, you can replace the $data[x] value with NULL. Let’s say your first column is an ID (auto-increment), you can simply replace $data[0] with NULL.

Do remember, for this to work, you will need to upload CSV files that are comma-separated.

And just like that, you now have an awesome CSV upload script!

If this has helped you out, leave a comment below. If for some reason you cannot get this to work, get in touch, we always love helping people!

 

Nathan da Silva - Profile

Posted by: Nathan da Silva

Nathan is the Founder of Silva Web Designs. He is passionate about web development, website design and basically anything digital-related. His main expertise is with WordPress and various other CMS frameworks. If you need responsive design, SEO, speed optimisation or anything else in the world of digital, you can contact Silva Web Designs here; [email protected]

It’s good to share

One thought on “How to Upload a CSV file into a MySQL Database Using PHP

  1. I really appreciate this article, thank you for sharing this to us. Actually I’ve been apply some of those design ideas to my previous projects but the other are new to me. So, I have to utilize those new to me so I can come up an upgraded one.

Join the discussion

Related Posts

Related - Bootstrap 4 – Mobile Nav Bar Slide from Left / Right

CSS / 23rd June 2021

Bootstrap 4 – Mobile Nav Bar Slide from Left / Right

Read More Related - How to Change Breakpoints with Bootstrap

CSS / 3rd October 2020

How to Change Breakpoints with Bootstrap

Read More