Upload Csv Files to Mysql Php Website

A CSV (comma-separated values) file stores the tabular information in obviously text format. Basically, the CSV file format is used to import to or export from the table data. Each line of the CSV file is a information record that consists of 1 or more than fields. When there is needed to add together huge information into the MySQL database, it will very time-consuming to add data one by one. In that situation, the import feature helps to insert a agglomeration of data in the database with a single click.

Bulk Import is a very useful characteristic to add multiple records in the database without insert manually. Using the CSV file you tin can shop the data and import the CSV file information into the database at once using PHP and MySQL. Import CSV into MySQL helps to save the user fourth dimension and avoid repetitive work. In this tutorial, we will testify you how to upload CSV file and import data from CSV file to MySQL database using PHP.

In the example script, we will import the fellow member'due south data from a CSV file and insert it into the database using PHP and MySQL. According to this script functionality, the user would be able to upload a CSV file of members details, and members data will exist inserted into the MySQL database using PHP.

Create Database Table

To store the fellow member's information, a table needs to be created in the database. The post-obit SQL creates a members table with some bones fields in the MySQL database.

          CREATE          TABLE          `members`          (          `id`          int(xi)          NOT          Zippo          AUTO_INCREMENT,          `name`          varchar(50)          COLLATE          utf8_unicode_ci          Not          NULL,          `email`          varchar(l)          COLLATE          utf8_unicode_ci          Not          NULL,          `phone`          varchar(15)          COLLATE          utf8_unicode_ci          NOT          NULL,          `created`          datetime          NOT          NULL,          `modified`          datetime          NOT          Goose egg,          `status`          enum('Active','Inactive')          COLLATE          utf8_unicode_ci          Non          Naught          DEFAULT          'Agile',  Chief          Primal          (`id`) )          ENGINE=InnoDB          DEFAULT          CHARSET=utf8          COLLATE=utf8_unicode_ci;

CSV File Format

Based on the database table construction, the CSV file should have these fields – Name, Email, Telephone, and Status. To import the data from CSV file, the format will similar the following screen.

Database Configuration (dbConfig.php)

The dbConfig.php is used to connect the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and proper noun ($dbName) as per your MySQL database credentials.

          <?php                    // Database configuration                    $dbHost          =          "localhost"          ;                    $dbUsername          =          "root"          ;                    $dbPassword          =          "root"          ;                    $dbName          =          "codexworld"          ;                    // Create database connection                    $db          = new          mysqli          (          $dbHost          ,          $dbUsername          ,          $dbPassword          ,          $dbName          );                    // Cheque connectedness                    if (          $db          ->          connect_error          ) {     dice(          "Connection failed: "          .          $db          ->          connect_error          ); }        

CSV File Upload (alphabetize.php)

Initially, the members data is listed with CSV file import option.

• Existing members data are fetched from the database and listed on the webpage.
• An Import push button is placed at the summit of the list.
• By clicking the Import push, an HTML form is appeared to select and upload a CSV file.
• On submission, the form is submitted to the importData.php file for importing the CSV information to the database.
• formToggle() – It is a JavaScript function that helps to Show/Hide the CSV upload form. This function is triggered on click outcome of the Import button.

If the grade is already submitted,

• The status message is retrieved from the URL and the import status is displayed on the web page.

          <?php                    // Load the database configuration file                    include_once          'dbConfig.php'          ;                    // Get status bulletin                    if(!empty(          $_GET          [          'status'          ])){     switch(          $_GET          [          'condition'          ]){         case          'succ'          :          $statusType          =          'alert-success'          ;          $statusMsg          =          'Members data has been imported successfully.'          ;             suspension;         example          'err'          :          $statusType          =          'alert-danger'          ;          $statusMsg          =          'Some problem occurred, delight try over again.'          ;             suspension;         case          'invalid_file'          :          $statusType          =          'warning-danger'          ;          $statusMsg          =          'Please upload a valid CSV file.'          ;             suspension;         default:          $statusType          =          ''          ;          $statusMsg          =          ''          ;     } }                    ?>                      <?php            if(!empty(            $statusMsg            )){            ?>            <div              class="col-xs-12">            <div              class="alert                <?php                echo                $statusType                ;                ?>">            <?php            repeat            $statusMsg            ;            ?>            </div>            </div>            <?php            }            ?>            <div              class="row">                        <div              class="col-md-12 caput">            <div              form="float-right">            <a              href="javascript:void(0);"              class="btn btn-success"              onclick="formToggle('importFrm');">            <i              class="plus">            </i>            Import</a>            </div>            </div>                        <div              class="col-doc-12"              id="importFrm"              style="display: none;">            <class              activity="importData.php"              method="post"              enctype="multipart/course-data">            <input              type="file"              name="file"              />            <input              type="submit"              grade="btn btn-primary"              name="importSubmit"              value="IMPORT">            </form>            </div>                        <table              class="table table-striped table-bordered">            <thead              class="thead-dark">            <tr>            <th>#ID</thursday>            <th>Name</thursday>            <th>Email</th>            <th>Telephone</thursday>            <thursday>Status</th>            </tr>            </thead>            <tbody><?php            // Become member rows            $result            =            $db            ->            query            (            "SELECT * FROM members Gild BY id DESC"            );         if(            $result            ->            num_rows            >            0            ){             while(            $row            =            $consequence            ->            fetch_assoc            ()){            ?>            <tr>            <td>            <?php            echo            $row            [            'id'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'proper name'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'email'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'telephone'            ];            ?>            </td>            <td>            <?php            repeat            $row            [            'status'            ];            ?>            </td>            </tr>            <?php            } }else{            ?>            <tr>            <td              colspan="5">No member(s) found...</td>            </tr>            <?php            }            ?>            </tbody>            </table>            </div>                    <!-- Evidence/hibernate CSV upload form -->          <script>                                    function              formToggle(ID){            var            chemical element =            document.getElementById(ID);            if(element.style.display ===            "none"){         element.style.brandish =            "cake";     }else{         element.style.display =            "none";     } }          </script>        

The Bootstrap library is used to styling the Table, Class, and Buttons. Then, include the Bootstrap four library file and custom stylesheet file (if any). If you don't want to use Bootstrap structure, omit to include this library file.

            <link            rel="stylesheet"            href="assets/bootstrap/bootstrap.min.css">                    <link            rel="stylesheet"            href="assets/css/manner.css">        

Import CSV Data to Database (importData.php)

The importData.php file handles the file upload and CSV data import procedure using PHP and MySQL.

• Validate the posted file whether it is a valid .csv file.
• Check whether the CSV file is uploaded using is_uploaded_file() function.
• Open the CSV file in read-only style using fopen() function.
• Read and Parse data from the opened CSV file using fgetcsv() role.
• Call back the CSV data line by line.
• Insert/Update member data in the database based on the email address.
• Redirect to the listing page with import status code.

          <?php                    // Load the database configuration file                    include_once          'dbConfig.php'          ;  if(isset(          $_POST          [          'importSubmit'          ])){          // Allowed mime types          $csvMimes          = array(          'text/x-comma-separated-values'          ,          'text/comma-separated-values'          ,          'application/octet-stream'          ,          'application/vnd.ms-excel'          ,          'application/ten-csv'          ,          'text/x-csv'          ,          'text/csv'          ,          'awarding/csv'          ,          'application/excel'          ,          'application/vnd.msexcel'          ,          'text/patently'          );          // Validate whether selected file is a CSV file          if(!empty(          $_FILES          [          'file'          ][          'name'          ]) &&          in_array          (          $_FILES          [          'file'          ][          'blazon'          ],          $csvMimes          )){          // If the file is uploaded          if(          is_uploaded_file          (          $_FILES          [          'file'          ][          'tmp_name'          ])){          // Open uploaded CSV file with read-only way          $csvFile          =          fopen          (          $_FILES          [          'file'          ][          'tmp_name'          ],          'r'          );          // Skip the starting time line          fgetcsv          (          $csvFile          );          // Parse data from CSV file line past line          while((          $line          =          fgetcsv          (          $csvFile          )) !==          Imitation          ){          // Go row data          $name          =          $line          [          0          ];          $email          =          $line          [          1          ];          $phone          =          $line          [          2          ];          $status          =          $line          [          three          ];          // Cheque whether fellow member already exists in the database with the same e-mail          $prevQuery          =          "SELECT id FROM members WHERE email = '"          .          $line          [          1          ].          "'"          ;          $prevResult          =          $db          ->          query          (          $prevQuery          );                                  if(          $prevResult          ->          num_rows          >          0          ){          // Update member data in the database          $db          ->          query          (          "UPDATE members SET name = '"          .          $name          .          "', phone = '"          .          $phone          .          "', status = '"          .          $status          .          "', modified = NOW() WHERE electronic mail = '"          .          $email          .          "'"          );                 }else{          // Insert member data in the database          $db          ->          query          (          "INSERT INTO members (proper name, email, phone, created, modified, status) VALUES ('"          .          $proper name          .          "', '"          .          $electronic mail          .          "', '"          .          $phone          .          "', NOW(), NOW(), '"          .          $status          .          "')"          );                 }             }          // Close opened CSV file          fclose          (          $csvFile          );          $qstring          =          '?status=succ'          ;         }else{          $qstring          =          '?status=err'          ;         }     }else{          $qstring          =          '?status=invalid_file'          ;     } }                    // Redirect to the listing page                    header          (          "Location: index.php"          .          $qstring          );        

Export Data to CSV File using PHP and MySQL

Determination

Here, we take tried to make the CSV import process simple. The example lawmaking volition help you to implement the import functionality in the web application. You tin can easily enhance our CSV Import to MySQL script functionality, to add more than fields or restrictions on information import based on your requirement.

Are you lot want to become implementation assist, or modify or enhance the functionality of this script? Submit Paid Service Request

If you lot accept any questions well-nigh this script, submit it to our QA community - Enquire Question

rivetgoor1989.blogspot.com

Source: https://www.codexworld.com/import-csv-file-data-into-mysql-database-php/

Share this post