Import CSV data into MySQL database using PHP

Here is some PHP code:

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

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $import="INSERT into info(tazkira,gender,province,category,clin,training) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";

    mysql_query($import) or die(mysql_error());
}

fclose($handle);

print "Import process completed";

I am facing an issue with the performance of uploading a large CSV file (around 10 MBs) to MySQL using PHP script in my XAMP server. It takes approximately 10 to 15 minutes for the transfer to complete. This poses a problem as I plan to deploy this application online where it would be impractical for users to wait that long. Currently, I am using MySQL-Front which completes the task within 1 second. I would greatly appreciate any guidance on how to improve the speed of transferring CSV data to MySQL via PHP.

phpmysqlcsv

Answer №1

if(isset($_POST["Import"]))
{

$host='localhost'; // Host Name.

$db_user= 'abcd1234'; //User Name

$db_password= 'efgh5678';

$db= 'ijklmnop'; // Database Name.

$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());

$csv = $_FILES["file"]["tmp_name"];

if($_FILES["file"]["size"] > 0)

{    
$csvfile = fopen($csv, 'r');

$field_csv = array();

$i = 0;

while (($csv_data = fgetcsv($csvfile, 1024, ",")) !== FALSE)
{

    if($i==0) { $i++; continue; }  // to exclude first line in the csv file.

    $field_csv['list_catagory_id'] = $csv_data[0];  // first csv field
    $field_csv['list_name'] = $csv_data[1]; // second csv field
    $field_csv['list_tamil'] = $csv_data[2]; // third csv field
    $field_csv['list_hindi'] = $csv_data[3]; // third csv field
    $field_csv['list_botanical'] = $csv_data[4]; 
    $field_csv['list_image'] = $csv_data[5]; 
    $field_csv['list_cook_time'] = $csv_data[6]; 
    $field_csv['list_summary'] = $csv_data[7]; 
    $field_csv['list_ingredients'] = $csv_data[8]; 
    $field_csv['list_instruction'] = $csv_data[9]; 

    $query = "INSERT INTO list SET list_catagory_id = '".$field_csv['list_catagory_id']."',list_name = '".$field_csv['list_name']."',list_tamil = '".$field_csv['list_tamil']."',list_hindi = '".$field_csv['list_hindi']."',list_botanical = '".$field_csv['list_botanical']."',list_image = '".$field_csv['list_image']."',list_cook_time = '".$field_csv['list_cook_time']."',list_summary = '".$field_csv['list_summary']."',list_ingredients = '".$field_csv['list_ingredients']."',list_instruction = '".$field_csv['list_instruction']."' ";
    mysql_query($query);    
}

fclose($csvfile);

}

echo "Data Successfully Imported To Table!!";

}
}

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

PHP - Understanding the structure of strings

Looking to retrieve information from anchor URLs on a website: require 'simple_html_dom.php'; $html = file_get_html('http://www.example.com'); foreach($html->find('a') as $element) { $href= $element->href; $nam ...

php

Retrieving information from a database using PHP PDO to populate a dropdown list

On my HTML page, I have a form that allows users to insert data into the database. Some fields in the form require dropdown lists, and I want to populate these dropdowns with data from the database. Previously, I had hardcoded the dropdown options like th ...

phphtmlpdo

CORS problem in Chrome when using AngularJS and Symfony 3

I've been dealing with a bug on my backend and frontend for the past week. I created a REST API (php/symfony) to manage books and authors. Initially, I had trouble with cross-origin requests on DELETE, which has been resolved. However, now I am facin ...

javascriptphpangularjssymfonygoogle-chrome

Tips for setting up a system where PHP serves as the backend and Angular acts as the

I am working on a project that utilizes Angular as the front end and PHP as the back end. Both are installed in separate domains, with the PHP project fully completed and operational. I have created an API in PHP which I plan to call from Angular. My ques ...

phpmysqlangularjsangularapi

"Concealing Categories in Joomla Search Results: A Guide for Maintaining Privacy

When using the Joomla Search module, it will list all categories that match the search query. For instance, if "iPhone" is searched on the website, it shows a list of categories first before articles. I want to find a way to hide these categories from the ...

phpsearchjoomla

"Shopping just got easier with our new drag and drop feature! Simply drag items

I want to develop a Virtual Shopping Cart system. Items will be retrieved from a database. A virtual basket will be available. Users can drag items and drop them into the basket, similar to shopping in a mall. Once the user clicks the save button, the s ...

phpjavascripthtmlcodeigniter

Multilingual Joomla emblem

I have been working on customizing an HTML template for Joomla! 3.7.2, and everything seems to be working fine except for the multilanguage redirect issue when clicking on the logo. 1) I inserted the logo in my index.php file as: <a id="t-logo" href=" ...

phpjoomla

Is it advisable to use an autosubmit form for processing online payments?

Situation: In the process of upgrading an outdated PHP 4 website, I am tasked with implementing an online payment system. This will involve utilizing an external payment platform/gateway to handle transactions. After a customer has completed their order ...

phpjavascriptpayment-gateway

What steps can I take to prevent BeautifulSoup from interpreting commas as tab characters?

My recent project involved creating a web scraping code to extract information from a local news website. However, I have encountered two issues with the current code. One problem is that when the code retrieves paragraph data and saves it to a CSV file ...

pythonhtmlcsvweb-scrapingbeautifulsoup

Incorporating numerous dropdown menus to a table filled with data retrieved from a database

I have implemented a dynamic table that adds a new row at the end when a button is clicked. One of the columns in the table contains a dropdown list, and I want this dropdown list to display values from a database. This is how I am constructing my table ...

phpjavascript

Opting for Fetch API over Ajax for requests that involve Allow-Credentials and require POST methods

In this particular scenario, the utilization of Access-Control-Allow-Credentials accompanied by the POST method is key in managing server-side PHP session variables that need to remain stable. To provide some context, the front-end aspect involves a creat ...

phpajaxreactjscorsfetch

What is the best way to send JSON data from a DooPHP Controller?

Currently, I am in the process of setting up an AJAX system and I need to create a controller that returns JSON data. While going through examples, I noticed that all controllers end with a call to the view: $this->renderc( 'interest', $d ...

phpjqueryajaxdoophp

All fields in the form are showing the same error message during validation

This is the HTML code: The form below consists of two fields, firstname and lastname. Validation Form <form action="" method="post"> Firstname : <input type="text" placeholder="first name" class="fname" name="fname" /> <span class= ...

javascriptphpjqueryhtmlforms

The hidden "key" value is lost when an array is passed to a form

Below is an example of my array: $array = array('foo' => 'bar', 'baz', 'bat' => 2); <form method='POST' action='test.php' onsubmit='return validateForm()' > echo '&l ...

php

How can I convert a PHP date to a JavaScript date that works across all

I am attempting to create a JavaScript countdown timer that relies on the server time, but I'm facing difficulties in transferring PHP time to JavaScript seamlessly across different browsers. While it functions correctly in modern browsers, older ones ...

phpjavascript

What is the best way to retrieve a specific line from a PHP array containing multiple strings?

After setting a PHP variable named $user to $e->getUser(), when I try to echo $user, I receive an array with the following data: Array ( [user_id] => 1 [login] => test [pass] => *** [remember_key] => [pass_dattm] => 201 ...

phparraysvariables

The data in my MySQL table is not appearing on an Angular Material table when using Node.js

HTML file content <table mat-table [dataSource]="dataSource" class="mat-elevation-z8"> <ng-container matColumnDef="id"> <th mat-header-cell *matHeaderCellDef> No. </th> <td mat-cell *matCellDef="let element"> {{ele ...

mysqlnode.jsangularexpresspostman

A proven method for distinguishing between desktop and mobile browsers

Similar Question: Exploring Browser Detection Methods in Javascript I am interested in finding an efficient way to differentiate between desktop and mobile browsers, either using JavaScript or PHP. if (desktop browser) { do x; } else { // mobi ...

phpjavascriptcross-browserbrowser-detection

A guide on utilizing jQuery to select a checkbox by accessing a Laravel variable

I am seeking advice on how to automatically select the checkbox linked to a student identified through my jQuery search function. To provide context, here is an example of my "Ajax" request. Currently, it showcases the search result in an HTML 'span&a ...

phpjqueryajaxlaravel

Ways to run Node.js scripts within PHP

I've been diving into a project that involves using purify-css https://github.com/purifycss/purifycss After successfully installing purify-css on the server with npm, I've been attempting to run the command below through PHP using the exec() ...

phpnpmexecnodespurifycss