In this article, we will discuss How to insert and update CSV file data into the database by using a PHP script. By using this logic we can avoid a large amount of data manual entries and time consuming, Especially eCommerce website bulk product information upload to the database. for that kind of website builder, I strongly suggest following this script you can easily achieve bulk data insert to the database.
For using this you must create one example CSV sheet based on your database column. Here I have created a sample shopping cart website product table.
Table structure
We need to create 'products' table into the database with the following structure.
CREATE TABLE IF NOT EXISTS `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sku_id` int(11) NOT NULL, `name` varchar(200) NOT NULL, `qty` int(11) NOT NULL, `descriptions` varchar(200) NOT NULL )
CSV File
Create sample csv file based on the products table column name and insert product information data to that csv file.
Id - Autoincrement so no need gives that column data.
Sku_id - Unique id (like product unique identify id).
Database (config.php)
Create config.php file write database connection code to this file.
<?php $user = 'root'; $password = ''; $database = 'Your_database'; $db = new mysqli('localhost',$user,$password,$database); ?>
HTML
<div align="center"> <h3>Bulk Data upload</h3> <form action="insert_data.php" enctype="multipart/form-data" method="post"/> <label>Import csv file :</label> <input type="file" name="csv" id="csv" class="large"/> <input type="submit" name="form_submit" value="import file" /> </form> </div>
The above form action file insert_data.php and enctype="multipart/form-data" it will encrypt the form data to post on another page.
Bulk data Insert code (insert_data.php)
<?php include("config.php"); if(isset( $_FILES['csv'] )) : $csv_file = $_FILES['csv']['tmp_name']; if(is_file( $csv_file)) : if(($handle = fopen($csv_file,"r")) !== FALSE) : while (($csv_data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($csv_data); for ($c=0; $c < $num; $c++): $colum[$c] = $csv_data[$c]; endfor; $inserted=$db->query("INSERT INTO products(sku_id,name,qty,descriptions)
VALUES('$colum[0]','$colum[1]','$colum[2]','$colum[3]')"); } $msg = "Data uploaded successfully."; fclose($handle); else : $msg = "unable to read the format try again"; endif; else : $msg = "CSV format File not found"; endif; else : $msg = "Please try again."; endif; echo $msg; ?>
fgetcsv($handle,1000) - Count the total keys in the row.
The output of the products table.
Update bulk data (update_data.php)
Create a new file name called update_data.php write and follow the below code.<div align="center"> <h3>Bulk Data upload</h3> <form enctype="multipart/form-data" method="post"/> <label>Import csv file :</label> <input type="file" name="csv" id="csv" class="large"/> <input type="submit" name="submit" value="import file" /> </form> </div> <?php include("config.php"); if(isset( $_FILES['csv'] )) : $csv_file = $_FILES['csv']['tmp_name']; if(is_file( $csv_file)) : if(($handle = fopen($csv_file,"r")) !== FALSE) : while (($csv_data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($csv_data); for ($c=0; $c < $num; $c++): $colum[$c] = $csv_data[$c]; endfor; if($colum[0]!="") : $sql="SELECT sku_id FROM products WHERE sku_id ='$colum[0]'"; $db_check=$db->query($sql); $count=mysqli_num_rows($db_check); // checking exist data if($count==1) : $update=$db->query("UPDATE products SET qty = '$colum[2]',
descriptions = '$colum[3]' WHERE sku_id='$colum[0]'"); else : echo '<pre> wrong sku id data<br>'; print_r($colum); exit; endif; else : $msg = "Some data is missing"; exit; endif; } $msg = "Data uploaded successfully."; fclose($handle); else : $msg = "unable to read the format try again"; endif; else : $msg = "CSV format File not found"; endif; else : $msg = "Please submit the form"; endif; echo $msg; ?>
mysqli_num_rows() - Returns the number of rows in the result set. if the $count==1 the data already exist.
Related Topics
- Simple Subscribe And Unsubscribe Script In PHP
- Auto Load More Data Dynamically On Page Scroll Using Jquery And PHP
- Product Search Filtering Using Php And Ajax
- New User Registration With Email Verification Using PHP And MySQLi
- Get All Child, Grandchild Etc Nodes Under Parent Array Using Php With MySQLi
- Convert PHP Data To JSON API Url
- Login With Facebook Using Codeigniter
- Remove Row From Table With FadeOut Effect Using Jquery And PHP
No comments:
Post a Comment