Import data from CSV file using PHP in MySQL

Some time you need to import bulk data to application, for that CSV is preferred format. You can also use PHPMyAdmin to import CSV file, however it’s cool to use PHPMyAdmin as a developer but you may not want to allow Client/User to import via it, because there are large no. of operations can perform it, even user may delete entire database!

I am created below script using which user can import CSV file via PHP page that you created. I was ising it as API, so all data is retrieve from post request’s parameters, and it will return all data in JSON format. I am also using below 2 functions for that import operation.

is_subset

This method is used to check uploaded CSV file contains all of the fields that required by our method, it does not see only counts, if fields are in different sequence still we allow importing, and also if contains some extra fields!

/**
* Method: is_subset
* Check whether 2nd array is subset of 1st or not
* @return Boolean stauts
*/
public function is_subset($arr1, $arr2)
{
	$len = count($arr2);
	$sub = $len;

	for($i = 0; $i < $len; $i++)
	{
		if(in_array($arr2[$i], $arr1))
			$sub--;
	}
	return ($sub==0)? true:false;
}

get_csv_as_object

This method takes file name as parameter and create a JSON object which contains data and a headers from CSV file in different fields, so we can perform operation on it.

/**
* Method: get_csv_as_object
* Return entire css data as JSON object
* @param $file_name - string
*/
public function get_csv_as_object($file_name)
{
	// Default JSON Structure
	$arr = array();
	$arr["status"] = false;
	$arr["errors"] = array();
	$arr["messages"] = array();
	$arr["data"] = null;
	$arr["headers"] = null;

	// Retrieve headers
	$csv_data = file_get_contents($file_name);
	$csv_data = preg_replace('/^[ \t]*[\r\n]+/m', '', $csv_data);
	$data = explode("\r\n", $csv_data);
	$headers = array_map('trim', explode(',', array_shift($data)));
	$arr["headers"] = $headers;
	// get the values and use array combine to use the headers as keys for associative array
	foreach($data as $values)
	{
		$pieces = explode(',', $values);
		if(sizeof($headers) == sizeof($pieces))
		{
			$arr["data"][] = array_combine($headers, $pieces);
			$arr["status"] = true;
		}
	}

	// Return JSON structure
	return json_encode($arr);
}

import_data

This is the method originally perform operation on CSV file. In this example i am allow user to perform insert or update operation, if data is exist for same id then it will update it otherwise insert new data. And also validate data. so at the end user can know howmuch record inserted/updated/invalid/same.
First of all we are retrieving data from CSV file into a JSON object and decode it to PHP. Then check for this CSV file contains all required fields. And loop through each item in data that we are obtain from CSV file. In loop we will validate data, and perform insert or update operation.

public function import_data()
{
	// Extract & Trim Array
	array_walk($_REQUEST,'trim');
	extract($_REQUEST);
	// Default JSON Structure
	$arr = array();
	$arr["status"] = false;
	$arr["errors"] = array();
	$arr["messages"] = array();
	$arr["result"] = array(
						"inserted" => array(),
						"updated" => array(),
						"invalid" => array(),
						"same" => array()
					);

	if(!isset($file_name) || strlen($file_name) == 0)
		$arr["errors"][] = "Missing file name.";
	else
	{
		// Retriev CSV data
		$csv_data = json_decode(get_csv_as_object($file_name));

		// Validate All Required field are exist or not
		if(is_subset($csv_data->headers, explode(",", REQ_FIELDS_FOR_MENTORS)))
		{
			// Loop through all rows
			foreach ($csv_data->data as $row)
			{
				// Validate details
				if( !isset($row->user_first_name) || strlen($row->user_first_name) == 0 || strlen($row->user_first_name) > 20
					|| !isset($row->user_last_name) || strlen($row->user_last_name) == 0 || strlen($row->user_last_name) > 20
					|| !isset($row->user_designation) || strlen($row->user_designation) == 0 || strlen($row->user_designation) > 20
					|| !isset($row->user_mob) || strlen($row->user_mob) == 0 || !preg_match(PATTERN_MOBILE_NO, $row->user_mob)
					|| !isset($row->user_mail) || strlen($row->user_mail) == 0 || !filter_var($row->user_mail, FILTER_VALIDATE_EMAIL)
					|| !isset($row->user_name) || strlen($row->user_name) == 0 || strlen($row->user_name) > 20 || !preg_match(PATTERN_USERNAME, $row->user_name)
					|| !isset($row->user_pass) || strlen($row->user_pass) == 0
					|| strlen($row->user_pass) < 8 || strlen($row->user_pass) > 20 || !preg_match(PATTERN_ONE_NUMERIC, $row->user_pass) || !preg_match(PATTERN_ONE_LOWER, $row->user_pass) || !preg_match(PATTERN_ONE_CAPITAL, $row->user_pass) || !preg_match(PATTERN_ONE_SYMBOL, $row->user_pass)
				)
					$arr["result"]["invalid"][] = $row->user_name;

				// If there is no errors
				else
				{
					// Connect to database
					$db_connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
					if (self::$db_connection->connect_errno)
					{
						die("Error occurred to connect database: " . self::$db_connection->connect_error);
					}
					
					// Check whether username/mailid is in use or not?
					$sql = sprintf("SELECT user_name, user_mail
									FROM %s
									WHERE user_name = ? OR user_mail = ?;",
									TABLE_USERS);
					if($stmnt = $db_connection->prepare($sql))
					{
						$stmnt->bind_param('ss', $row->user_name, $row->user_mail) or die($db_connection->error);
						$stmnt->execute() or die($db_connection->error);
						$result_of_exist_user = $stmnt->get_result();

						// Remove special characters
						$first_name = $db_connection->real_escape_string($row->user_first_name);
						$last_name = $db_connection->real_escape_string($row->user_last_name);
						$designation = $db_connection->real_escape_string($row->user_designation);
						$mob = $db_connection->real_escape_string($row->user_mob);
						$mail_id = $db_connection->real_escape_string($row->user_mail);
						$username = $db_connection->real_escape_string($row->user_name);
						$password = $db_connection->real_escape_string(strip_tags($row->user_pass));
						$password = md5($password);

						// If any row exist with same mail/username update it
						if ($result_of_exist_user->num_rows >= 1)
						{
							// Update in DB
							$sql = sprintf("UPDATE %s SET user_pass = ?,
														user_first_name = ?,
														user_last_name = ?,
														user_designation = ?,
														user_mob = ?
														WHERE user_name = ? OR user_mail = ?",
														TABLE_USERS);
							if($stmnt = $db_connection->prepare($sql))
							{
								$stmnt->bind_param('sssssss', $password, $first_name, $last_name, $designation, $mob, $username, $mail_id) or die($db_connection->error);
								$stmnt->execute() or die($db_connection->error);
								if($db_connection->affected_rows)
									$arr["result"]["updated"][] = $row->user_name;
								else
									$arr["result"]["same"][] = $row->user_name;
							}
							else
								$arr["errors"][] = "Error occurred: " . $db_connection->error;
						}
						else
						{
							// Insert in DB
							$sql = sprintf("INSERT INTO %s
											VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, NULL, ?, CURRENT_TIMESTAMP, 1);",
											TABLE_USERS);
							if($stmnt = $db_connection->prepare($sql))
							{
								$type = USER_TYPE_MENTOR;
								$stmnt->bind_param('ssssssss', $username, $password, $mail_id, $first_name, $last_name, $designation, $mob, $type) or die($db_connection->error);
								$stmnt->execute() or die($db_connection->error);
								$quert_for_insert_mentor = $stmnt->get_result();

								// If inserted
								if ($db_connection->affected_rows)
									$arr["result"]["inserted"][] = $row->user_name;
								else
									$arr["result"]["same"][] = $row->user_name;
							}
							else
								die("Error occurred in database: " . $db_connection->error);
						}
					}
					else
						die("Error occurred in database: " . $db_connection->error);
				}
			}
			$arr["messages"][] = "Import operation perform Successfully.";
			$arr["status"] = true;
		}
		else
			$arr["errors"][] = "Some required field are not exist in CSV file.";
	}
	// Return JSON Structure
	return json_encode($arr);
}

Leave a Reply

Your email address will not be published. Required fields are marked *