Jump to content
funbinod

insert csv into mysql problem

Recommended Posts

hello all!

i am trying to upload and insert data on a csv file to mysql. it uploads but could not insert data to mysql. following is the work i've done.

				$handle = fopen($targetpath, 'r');
				
				$firstline = true;
				while (($data = fgetcsv($handle, 5000, ',')) !== FALSE) {
					if (!$firstline) {
						$continue = true;
						if ($data[0] == "" && $data[1] == "" && $data[2] == "" && $data[3] == "" && $data[4] == "" && $data[5] == "" && $data[6] == "" && $data[7] == ""){
							$continue = false;
						}
						if (strlen($data[0]) != strlen(utf8_decode($data[0]))) {
							$prjname = ($data[0]);
						} else {
							$prjname = strtoupper($data[0]);
						}
						if ($data[1] == "CONTRACTOR") {
							$ctrtype = 1;
						} else if ($data[1] == "USER COMMITTEE") {
							$ctrtype = 2;
						} else {
							$continue = false;
						}
						if (!is_numeric($data[2])) {
							$continue = false;
						}
						if ($continue == true) {
							$q = $mysqli->query("INSERT INTO projects (subdate, name, ctrtype, budhead, code, wardno, govallo, startdate, enddate, fy, cid, uid) VALUES ('$subdate', '$prjname', '$ctrtype', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$ay', '$cid', '$uid')");
							if (!$q) {
								echo "<div id='success'>NO</div>";
								echo "<div id='msg'>Error: $mysqli->error</div>";
							} else {
								$q->bind_param("siisidssiii", $subdate, $prjname, $ctrtype, $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $ay, $cid, $uid);
								if (!$q->execute()) {
									echo "<div id='success'>NO</div>";
									echo "<div id='msg'>Error: $q->error</div>";
								} else {
									echo "<div id='success'>YES</div>";
									echo "<div id='msg'>SUCCESSFULLY INSERTED DATA IN FILE!</div>";
									$_SESSION["msg"] = "SUCCESSFULLY INSERTED DATA IN FILE!";
								}
							}
						}
					}
					$firstline = false;
				}
				unlink($path);

can u please guide where i did mistake???

 

thank u in advance..

Share this post


Link to post
Share on other sites

Why not use LOAD DATA?

Share this post


Link to post
Share on other sites
4 hours ago, niche said:

Why not use LOAD DATA?

because i want to filter data supplied in the csv file.

Share this post


Link to post
Share on other sites
12 hours ago, justsomeguy said:

What specifically happens when you run that?

i've posted the screenshot. it just displays "Aq, Snap!" DEV TOOLS also disconnects.

 

and yes! the file upload is done through jQuery ajax. when i check manually, the file uploads but the mysql inserting part is not executed.

 

and i found some mistakes on my code. i corrected it as following. but it still is not performing the action.

 

							$q = $mysqli->prepare("INSERT INTO projects (name, ctrtype, budhead, code, wardno, govallo, startdate, enddate, fy, cid, uid) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
							if (!$q) {
								echo "<div id='success'>NO</div>";
								echo "<div id='msg'>Error: $mysqli->error</div>";
							} else {
								$q->bind_param("siisidssiii", $prjname, $ctrtype, $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $ay, $cid, $uid);
								if (!$q->execute()) {
									echo "<div id='success'>NO</div>";
									echo "<div id='msg'>Error: $q->error</div>";
								} else {
									echo "<div id='success'>YES</div>";
									echo "<div id='msg'>SUCCESSFULLY INSERTED DATA IN FILE!</div>";
									$_SESSION["msg"] = "SUCCESSFULLY INSERTED DATA IN FILE!";
								}
							}

 

screenshot.jpg

Share this post


Link to post
Share on other sites

Great point!

 

 

Share this post


Link to post
Share on other sites

If you're not seeing a PHP error message in the browser, then that's probably disabled.  Make sure you're using an error log and check the log to see the error message.

Share this post


Link to post
Share on other sites
On 7/20/2018 at 10:46 PM, justsomeguy said:

If you're not seeing a PHP error message in the browser, then that's probably disabled.  Make sure you're using an error log and check the log to see the error message.

PHP error is enabled. and there is nothing added on error log of my cpanel.

the only thing happens when i post the csv file is - "Aw, Snap" page.

its so much confusing. it works very fine when i use LOAD DATA method.

Share this post


Link to post
Share on other sites

the only thing happens when i post the csv file is - "Aw, Snap" page.

That's just what Chrome is showing you, PHP is not creating and sending that page to the browser.  It's probably doing that because there is a 500 response from the server, which means a server error, which means there will be an actual error message somewhere if you have it configured correctly and know where to look.

Share this post


Link to post
Share on other sites
public function importExcel()
  {
  $file = $_FILES[&#39;uploads&#39;][&#39;tmp_name&#39;];
  $handle = fopen($file, &quot;r&quot;);
   
  while($filesop = fgetcsv($handle, 1000, &quot;,&quot;)){
  $name = $filesop[0];
  $gender = $filesop[1];
  $address = $filesop[2];
  $age = $filesop[3];
  $education = $filesop[4];
  $contact = $filesop[5];
   
  DB::table(&#39;visitors&#39;)-&gt;insert([
  &#39;name&#39;=&gt;$name,
  &#39;age&#39;=&gt;$age,
  &#39;gender&#39;=&gt;$gender,
  &#39;address&#39;=&gt;$address,
  &#39;education&#39;=&gt;$education,
  &#39;contact&#39;=&gt;$contact,
  ]);
  }
 

}

At first, we retrieved the file from the temporary location and we opened it in reading mode.

Using the while loop, we stored the values from the excelsheet rows as indexed arrays to some variables.

Finally, we added those variables in the insert query and executed while remaining inside the loop. That makes the function run until the last row of excelsheet and insert each of those rows individually in database.

Once completed, you can store a success message in session and display it to the user while redirecting the user.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×