Importing CSV File to MySQL using PHP with Bootstrap Templates

Submitted by alpha_luna on Tuesday, September 27, 2016 - 13:52.

If you are looking for Importing CSV File to MySQL using PHP with Bootstrap Templates then you are at the right place. We all know that CSV file format used to import or to export the table data. Every line from the CSV file format indicates one field for the data table in the database. For inserting data one by one to the MySQL Table, it's very time consuming to do that, instead of this procedure you can use the importing file using CSV File Format to insert a bundle of data just in one process in your web page. In this tutorial, we are going to show or to learn on how to import data using CSV File Format to MySQL Database Table using the PHP Programming Language. Let's start to create our database table.

Open the PHPMyAdmin.
Create a database.
After creating a database. Kindly click the SQL below, copy and paste to your PHPMyAdmin to create a table.

CREATE TABLE `user` 
  ( 
     `user_id`    INT(11) NOT NULL, 
     `user_name`  VARCHAR(100) NOT NULL, 
     `first_name` VARCHAR(100) NOT NULL, 
     `last_name`  VARCHAR(100) NOT NULL, 
     `date_added` DATETIME NOT NULL 
  ) 
engine=innodb 
DEFAULT charset=latin1; 

Database Connection This simple PHP query helps us to connect and select the database. Save it as "database.php".

<?php
mysql_select_db('import_file', mysql_connect('localhost', 'root', '')) or die(mysql_error());
?>

Create Markup We have to create simple markup for the importing data. Take a look the source code below and study.

<form action="import_query.php" method="post" name="upload_excel" enctype="multipart/form-data">
   <div>
      <div class="col-xs-6">
         <label>Import CSV/Excel File:</label>
         <input type="file" class="form-control" multiple name="filename" id="filename">
         <br />
         <button type="submit" id="submit" class="btn btn-primary" name="submit" data-loading-text="Loading...">Upload</button>
      </div>
   </div>
</form>

To view the data from the database, we have simple source code that we construct to display to your web page. Copy and paste it to the BODY tag of your web page.

<table>
   <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>User Name</th>
   </tr>
   <?php
      include ('database.php');
      $result= mysql_query("select * from user order by user_id ASC") or die (mysql_error());
      while ($row= mysql_fetch_array ($result) ){
      $id=$row['user_id'];
      ?>
   <tr>
      <td><?php echo $row['user_name']; ?></td>
      <td><?php echo $row['first_name']; ?></td>
      <td><?php echo $row['last_name']; ?></td>
   </tr>
   <?php } ?>
</table>

PHP INSERT Statement This is the simple query for Importing CSV File to the MySQL Database. Take a look the source code below and try to study it.

<?php
 
if (isset($_POST['submit']))
        {
        include ('CSV_row_Database.php');
 
        // Import uploaded file to CSV_row_Database
 
        $file_CSV = fopen($_FILES['filename']['tmp_name'], "r");
        while (($CSV_row_Data = fgetcsv($file_CSV, 1000, ",")) !== FALSE)
                {
                mysql_query("INSERT into user (user_name, first_name, last_name, date_added)
values('$CSV_row_Data[0]', '$CSV_row_Data[1]', '$CSV_row_Data[2]', NOW())");
                }
 
        fclose($file_CSV);
 
        // print "Import done";
 
        echo "<script type='text/javascript'>alert('Successfully Imported a CSV File for User!');</script>";
        echo "<script>document.location='index.php'</script>";
 
        // view upload form
 
        }
 
?>

Output

For the beginners who really want to learn how to codes in PHP, this tutorial will help you to your future projects and you can implement the Importing Data Function in your web projects.

Add new comment

1084 views