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, Magento, Shopify as well as many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share

How to Increase the Maximum File Upload Size in WordPress

Do you have a large file that you can’t upload to WordPress due to limitations? Do you want to increase the maximum file upload size in WordPress? Some times low file upload size limit can stop you from uploading files via media uploader, or install plugins and themes. In this article, we will show you how to easily increase the maximum file upload size in WordPress to correct that issue.

How to Check Your Maximum File Upload Size Limit in WordPress?

WordPress will automatically show the maximum file upload size limit when you are uploading images or media. To check this, you can simply go to Media > Add New page and you will see the maximum file upload size limit for your WordPress site.

So how do we fix this?

Method 1

In some cases you can just add the following code in theme’s functions.php file to increase the upload size:


@ini_set( 'upload_max_size' , '64M' );
@ini_set( 'post_max_size', '64M');
@ini_set( 'max_execution_time', '300' );

Method 2

Create or Edit an existing PHP.INI file. For this method you will need to access your WordPress site’s root folder by using FTP or File Manager app in your hosting account’s cPanel/Plesk dashboard.

In most cases, if you are on a shared host, then you will not see a php.ini file in your directory. If you do not see one, then create a file called php.ini and upload it in the root folder. In that file add the following code:-


upload_max_filesize = 64M
post_max_size = 64M
max_execution_time = 300

This method is reported to work for many users. Remember if 64 doesn’t work, then try 10MB (sometimes that work).

Method 3

For some, it works by updating the .htaccess file which you can find in the root directory. You can increase the maximum upload size in WordPress. Edit the .htaccess file in your WordPress site’s root folder and add the following code:-


php_value upload_max_filesize 64M
php_value post_max_size 64M
php_value max_execution_time 300
php_value max_input_time 300

Again, do note that if you are on a shared hosting package then these techniques may not work. In that case, you would have to contact your web hosting provider to increase the limit for you.

We hope this article helped you increase the maximum file upload size in WordPress.

If you need any further assistance, just drop me a message in the comments below!

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, Magento, Shopify as well as many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share

How to Add Additional File Types to be Uploaded in WordPress

Due to security reasons, WordPress only allows you to upload the most commonly used file types. By default, you can upload commonly used image formats, audio/video and documents using the default media uploader. But what if you wanted to upload a file type that is not allowed? In this article, we will show you how to add additional file types to be uploaded in WordPress.

If the above error looks familiar to you, then you’re at the right place.

File Types Allowed for Upload in WordPress

WordPress allows you to upload most common image files, audio/ video, PDF, Microsoft Office and OpenOffice documents. WordPress codex has a full list of allowed file types and extensions.

Adding Exceptions for Additional File Types

Security is the main reason behind the limitation on file types that users can upload. However, this does not mean that users cannot change this. Using a bit of code, you can add a new file type and extension to WordPress.

For example, add this code in your theme’s functions.php file or a site-specific plugin to allow SVG file type to be uploaded:-


function my_myme_types($mime_types){
    $mime_types['svg'] = 'image/svg+xml'; //Adding SVG extension
    return $mime_types;
}
add_filter('upload_mimes', 'my_myme_types', 1, 1);

Notice that the file extension goes as the key in $mime_types associated array and the mime type goes as its value.

In this example, SVG file extension represents files with the mime type image/svg+xml. You can find out mime types of several common file extensions on this page.

You can also add multiple file types in one code snippet, like this:-


function my_myme_types($mime_types){
    $mime_types['svg'] = 'image/svg+xml'; //Adding svg extension
    $mime_types['psd'] = 'image/vnd.adobe.photoshop'; //Adding photoshop files
    return $mime_types;
}
add_filter('upload_mimes', 'my_myme_types', 1, 1);

Awesome, now you can upload as many different file types as you like!

One other issue you may encounter is that you get a warning saying that the file “exceeds the maximum upload size for this site” as shown below:-

To resolve this issue please see the following article; WordPress troubleshooting guide to fix it.

If you need help with anything WordPress related, just drop me an email at [email protected] or leave a comment below.

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, Magento, Shopify as well as many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share