funbinod Posted July 19, 2018 Share Posted July 19, 2018 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.. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 19, 2018 Share Posted July 19, 2018 What specifically happens when you run that? Link to comment Share on other sites More sharing options...
niche Posted July 19, 2018 Share Posted July 19, 2018 Why not use LOAD DATA? Link to comment Share on other sites More sharing options...
funbinod Posted July 20, 2018 Author Share Posted July 20, 2018 4 hours ago, niche said: Why not use LOAD DATA? because i want to filter data supplied in the csv file. Link to comment Share on other sites More sharing options...
funbinod Posted July 20, 2018 Author Share Posted July 20, 2018 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!"; } } Link to comment Share on other sites More sharing options...
niche Posted July 20, 2018 Share Posted July 20, 2018 Great point! Link to comment Share on other sites More sharing options...
justsomeguy Posted July 20, 2018 Share Posted July 20, 2018 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. Link to comment Share on other sites More sharing options...
funbinod Posted July 21, 2018 Author Share Posted July 21, 2018 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. Link to comment Share on other sites More sharing options...
niche Posted July 22, 2018 Share Posted July 22, 2018 this might help https://stackoverflow.com/questions/18342730/import-csv-file-with-php-using-pdo Link to comment Share on other sites More sharing options...
justsomeguy Posted July 23, 2018 Share Posted July 23, 2018 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. Link to comment Share on other sites More sharing options...
webtrickshome Posted July 30, 2018 Share Posted July 30, 2018 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now