insert csv into mysql problem

[funbinod]

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..

[justsomeguy]

What specifically happens when you run that?

[niche]

Why not use LOAD DATA?

[funbinod]

4 hours ago, niche said:

Why not use LOAD DATA?

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

[funbinod]

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!";
								}
							}

 

[niche]

Great point!

 

 

[justsomeguy]

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.

[funbinod]

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.

[niche]

this might help

https://stackoverflow.com/questions/18342730/import-csv-file-with-php-using-pdo

[justsomeguy]

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.

[webtrickshome]

public function importExcel()
	{
	$file = $_FILES['uploads']['tmp_name'];
	$handle = fopen($file, "r");
	while($filesop = fgetcsv($handle, 1000, ",")){
	$name = $filesop[0];
	$gender = $filesop[1];
	$address = $filesop[2];
	$age = $filesop[3];
	$education = $filesop[4];
	$contact = $filesop[5];
	DB::table('visitors')->insert([
	'name'=>$name,
	'age'=>$age,
	'gender'=>$gender,
	'address'=>$address,
	'education'=>$education,
	'contact'=>$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.