Upload Csv File Php to Localhost Mysql

A CSV (comma-separated values) file stores the tabular data in evidently 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 data tape that consists of one or more fields. When at that place is needed to add together huge data into the MySQL database, it will very time-consuming to add together information ane by one. In that situation, the import feature helps to insert a bunch of data in the database with a unmarried click.

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

In the case script, nosotros will import the member'southward 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 information will be inserted into the MySQL database using PHP.

Create Database Tabular array

To store the member's data, a table needs to be created in the database. The post-obit SQL creates a members tabular array with some basic fields in the MySQL database.

          CREATE          TABLE          `members`          (          `id`          int(11)          NOT          NULL          AUTO_INCREMENT,          `proper noun`          varchar(l)          COLLATE          utf8_unicode_ci          NOT          NULL,          `e-mail`          varchar(50)          COLLATE          utf8_unicode_ci          Non          NULL,          `phone`          varchar(15)          COLLATE          utf8_unicode_ci          Not          Nada,          `created`          datetime          Non          NULL,          `modified`          datetime          NOT          Aught,          `status`          enum('Active','Inactive')          COLLATE          utf8_unicode_ci          NOT          NULL          DEFAULT          'Active',  Master          Central          (`id`) )          ENGINE=InnoDB          DEFAULT          CHARSET=utf8          COLLATE=utf8_unicode_ci;

CSV File Format

Based on the database tabular array structure, the CSV file should have these fields – Name, Email, Phone, and Condition. 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 name ($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          );                    // Check connection                    if (          $db          ->          connect_error          ) {     die(          "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 peak of the list.
• Past clicking the Import button, 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 data to the database.
• formToggle() – It is a JavaScript part that helps to Show/Hide the CSV upload form. This function is triggered on click event of the Import push.

If the form 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'          ;                    // Become status message                    if(!empty(          $_GET          [          'status'          ])){     switch(          $_GET          [          'status'          ]){         case          'succ'          :          $statusType          =          'warning-success'          ;          $statusMsg          =          'Members data has been imported successfully.'          ;             break;         instance          'err'          :          $statusType          =          'alert-danger'          ;          $statusMsg          =          'Some trouble occurred, please try again.'          ;             suspension;         instance          '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="warning                <?php                echo                $statusType                ;                ?>">            <?php            echo            $statusMsg            ;            ?>            </div>            </div>            <?php            }            ?>            <div              class="row">                        <div              grade="col-md-12 head">            <div              class="float-correct">            <a              href="javascript:void(0);"              class="btn btn-success"              onclick="formToggle('importFrm');">            <i              class="plus">            </i>            Import</a>            </div>            </div>                        <div              form="col-doctor-12"              id="importFrm"              way="brandish: none;">            <form              action="importData.php"              method="post"              enctype="multipart/form-data">            <input              type="file"              name="file"              />            <input              type="submit"              class="btn btn-chief"              name="importSubmit"              value="IMPORT">            </grade>            </div>                        <table              class="table table-striped table-bordered">            <thead              class="thead-dark">            <tr>            <th>#ID</th>            <thursday>Proper noun</thursday>            <th>Email</th>            <th>Phone</th>            <th>Status</th>            </tr>            </thead>            <tbody><?php            // Get member rows            $consequence            =            $db            ->            query            (            "SELECT * FROM members ORDER BY id DESC"            );         if(            $effect            ->            num_rows            >            0            ){             while(            $row            =            $result            ->            fetch_assoc            ()){            ?>            <tr>            <td>            <?php            echo            $row            [            'id'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'proper noun'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'e-mail'            ];            ?>            </td>            <td>            <?php            repeat            $row            [            'phone'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'condition'            ];            ?>            </td>            </tr>            <?php            } }else{            ?>            <tr>            <td              colspan="v">No member(s) found...</td>            </tr>            <?php            }            ?>            </tbody>            </table>            </div>                    <!-- Evidence/hide CSV upload grade -->          <script>                                    function              formToggle(ID){            var            element =            document.getElementById(ID);            if(chemical element.style.display ===            "none"){         chemical element.style.display =            "block";     }else{         element.style.display =            "none";     } }          </script>        

The Bootstrap library is used to styling the Tabular array, Form, and Buttons. So, include the Bootstrap 4 library file and custom stylesheet file (if whatsoever). 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/style.css">        

Import CSV Information to Database (importData.php)

The importData.php file handles the file upload and CSV information import process 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-merely way using fopen() function.
• Read and Parse information from the opened CSV file using fgetcsv() function.
• Retrieve 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 condition code.

          <?php                    // Load the database configuration file                    include_once          'dbConfig.php'          ;  if(isset(          $_POST          [          'importSubmit'          ])){          // Immune mime types          $csvMimes          = array(          'text/ten-comma-separated-values'          ,          'text/comma-separated-values'          ,          'application/octet-stream'          ,          'application/vnd.ms-excel'          ,          'application/ten-csv'          ,          'text/x-csv'          ,          'text/csv'          ,          'application/csv'          ,          'application/excel'          ,          'application/vnd.msexcel'          ,          'text/apparently'          );          // Validate whether selected file is a CSV file          if(!empty(          $_FILES          [          'file'          ][          'proper noun'          ]) &&          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-just mode          $csvFile          =          fopen          (          $_FILES          [          'file'          ][          'tmp_name'          ],          'r'          );          // Skip the kickoff line          fgetcsv          (          $csvFile          );          // Parse data from CSV file line by line          while((          $line          =          fgetcsv          (          $csvFile          )) !==          FALSE          ){          // Become row information          $name          =          $line          [          0          ];          $email          =          $line          [          1          ];          $phone          =          $line          [          ii          ];          $status          =          $line          [          iii          ];          // Bank check whether member already exists in the database with the same email          $prevQuery          =          "SELECT id FROM members WHERE electronic mail = '"          .          $line          [          1          ].          "'"          ;          $prevResult          =          $db          ->          query          (          $prevQuery          );                                  if(          $prevResult          ->          num_rows          >          0          ){          // Update fellow member data in the database          $db          ->          query          (          "UPDATE members Set up proper noun = '"          .          $name          .          "', phone = '"          .          $telephone          .          "', status = '"          .          $status          .          "', modified = At present() WHERE email = '"          .          $electronic mail          .          "'"          );                 }else{          // Insert fellow member information in the database          $db          ->          query          (          "INSERT INTO members (name, email, telephone, created, modified, status) VALUES ('"          .          $name          .          "', '"          .          $electronic mail          .          "', '"          .          $phone          .          "', NOW(), NOW(), '"          .          $status          .          "')"          );                 }             }          // Close opened CSV file          fclose          (          $csvFile          );          $qstring          =          '?status=succ'          ;         }else{          $qstring          =          '?condition=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

Conclusion

Hither, nosotros have tried to make the CSV import process elementary. The example lawmaking volition aid you to implement the import functionality in the web application. Yous can easily enhance our CSV Import to MySQL script functionality, to add together more fields or restrictions on data import based on your requirement.

Are you want to go implementation help, or modify or heighten the functionality of this script? Submit Paid Service Asking

If you have any questions about this script, submit it to our QA customs - Ask Question

greenfrass1954.blogspot.com

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

Share this post