KN1V3S Posted September 15, 2021 Share Posted September 15, 2021 Hello everyone, as described above, whenever I upload a CSV doc with more than 600 rows of data, some of those rows went missing. I thought it was maybe the format of the data itself, but that was not the case. I couldn't identify the problem. Unfortunately I can't share the CSV file since it contains sensitive information. I need help with writing a specific PHP code that tells me which row of the CSV file couldn't be uploaded into the database. My current php code is below. Let me know if you need more information. Kind regards Knives <?PHP $con = mysqli_connect("localhost", "root", "","csvupload"); if(ISSET($_POST['import'])) { $filename = $_FILES["file"]["tmp_name"]; if(ISSET($_POST['checkbox'])) { $skipRow = 0; } else { $skipRow = 1; } if($_FILES["file"]["size"] > 0); { $file = fopen($filename, "r"); } while(($column = fgetcsv($file, 10000, ";")) !== FALSE) { $skipRow++; if ($skipRow == 1) { continue; } $sqlInsert = "INSERT INTO `adressen_neu` (`KNr.`, `Anrede`, `Vorname`, `Name`, `Name2`, `Name3`, `Land`, `PLZ`, `Ort`, `Strasse`, `Telefon`, `Telefax`, `Email`) VALUES ('$column[0]', '$column[1]', '$column[2]', '$column[3]', '$column[4]', '$column[5]', '$column[6]', '$column[7]', '$column[8]', '$column[9]', '$column[10]', '$column[11]', '$column[12]')"; $result = mysqli_query($con, $sqlInsert); } if(!empty($result)) { echo "Ein Fehler ist aufgetreten."; } else { echo "Erfolgreich in die Datenbank eingetragen."; } } ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 You are inserting using the slowest method possible. Are the missing rows always from the end of the CSV file, or is it skipping rows from the middle? Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 14 minutes ago, Barand said: You are inserting using the slowest method possible. Are the missing rows always from the end of the CSV file, or is it skipping rows from the middle? That's what I'm currently trying to find out. But since there are 623 lines, it's a bit difficult to see which ones are missing. I compared the amount of rows in the database and in the original CSV file. In the original CSV file, there are 623 rows of data. In the database, there are 613 entries. That means that 10 rows are gone. Unfortunately I can't find out which ones are not being added into the db. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 (edited) What is your table's structure? SHOW CREATE TABLE adressen_neu; Edited September 15, 2021 by Barand Quote Link to comment Share on other sites More sharing options...
Phi11W Posted September 15, 2021 Share Posted September 15, 2021 1 hour ago, KN1V3S said: That's what I'm currently trying to find out. But since there are 623 lines, it's a bit difficult to see which ones are missing. I compared the amount of rows in the database and in the original CSV file. In the original CSV file, there are 623 rows of data. In the database, there are 613 entries. That means that 10 rows are gone. Unfortunately I can't find out which ones are not being added into the db. Pull the data back out of the database and copy it into your favourite spreadsheet program (e.g. MS Excel). Then do the same with the data from the original file. Put the two lists side by side on a single worksheet, sort the two lists and compare visually. It shouldn't take more than a few minutes to find the ones that appear in one list and not the other. Regards, Phill W. Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 (edited) 22 minutes ago, Barand said: What is your table's structure? SHOW CREATE TABLE adressen_neu; This is my table's structure (I'm still a beginner at working with databases so if there are major mistakes, please tell me!) Edited September 15, 2021 by KN1V3S Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 13 minutes ago, Phi11W said: Pull the data back out of the database and copy it into your favourite spreadsheet program (e.g. MS Excel). Then do the same with the data from the original file. Put the two lists side by side on a single worksheet, sort the two lists and compare visually. It shouldn't take more than a few minutes to find the ones that appear in one list and not the other. Regards, Phill W. Hi Phill, thanks for your suggestion, could you please tell me how I can copy all the entries of the database into a worksheet? Kind regards, Knives Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 I was hoping to see which column was the primary key and if you had any other keys defined on any columns. That was why I requested the the output from SHOW CREATE TABLE. I can't see anything defined there, so is there anything in those records that uniquly identifies a record? Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 4 minutes ago, Barand said: I was hoping to see which column was the primary key and if you had any other keys defined on any columns. That was why I requested the the output from SHOW CREATE TABLE. I can't see anything defined there, so is there anything in those records that uniquly identifies a record? This is the output from SHOW CREATE TABLE adressen_neu Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 Those ... tell us sweet FA (Why is phpmyadmin so useless?). What about my other question about unique identifiers> Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 Just now, Barand said: Those ... tell us sweet FA (Why is phpmyadmin so useless?). What about my other question about unique identifiers> I dont understand your question Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 (edited) I've just spotted the "key" icon next to KNr ( I was expecting a separated to column to say which columns were keys) so it looks like that is the primary key. That makes your checking for missing records simple. You just need to see which KNr values are not in the db table but are in the csv file. Edited September 15, 2021 by Barand Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 1 minute ago, Barand said: I've just spotted the "key" icon next to KNr ( I was expecting a separated to column to say which columns were keys) so it looks like that is the primary key. That makes your checking for missing records simple. You just need to see which KNr values are not in the db table but are in the csv file. Is there a fast method of checking that? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 (edited) I'd create and array of KNr's from the CSV and a second array of KNr's from your table then compare the arrays for differencess (array_diff() ) EDIT: Something like this $array1 = []; $array2 = []; $file = fopen($filename, "r"); while (($column = fgetcsv($file, 10000, ";")) !== FALSE) { $array1[] = $column[0]; } $res = $con->query("SELECT KN2 from adressen_neu"); while ($row = $res->fetch_num() ) { $array2[] = $row[0]; } echo '<pre>' . print_r(array_diff($array1, $array2), 1) . '</pre>'; Edited September 15, 2021 by Barand Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 24 minutes ago, Barand said: I'd create and array of KNr's from the CSV and a second array of KNr's from your table then compare the arrays for differencess (array_diff() ) I know it looks silly, but I implemented "var_dump(count($column) < 1);" to check if there are some arrays that have a value of 0. But it turned out that there was not a single array that had the value 0. Doesn't that mean that there might be issues from the db? Correct me if I'm wrong! Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 47 minutes ago, Barand said: I'd create and array of KNr's from the CSV and a second array of KNr's from your table then compare the arrays for differencess (array_diff() ) EDIT: Something like this $array1 = []; $array2 = []; $file = fopen($filename, "r"); while (($column = fgetcsv($file, 10000, ";")) !== FALSE) { $array1[] = $column[0]; } $res = $con->query("SELECT KN2 from adressen_neu"); while ($row = $res->fetch_num() ) { $array2[] = $row[0]; } echo '<pre>' . print_r(array_diff($array1, $array2), 1) . '</pre>'; It says that fetch_num isn't defined. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 Sorry. Not used to mysqli (I always use PDO). It should be fetch_row(); Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 16 minutes ago, Barand said: Sorry. Not used to mysqli (I always use PDO). It should be fetch_row(); I'm really sorry for bothering you so much, but now I'm getting this error: Fatal error: Uncaught Error: Call to a member function fetch_row() on bool in C:\xampp\htdocs\array_comparison.php:19 Stack trace: #0 {main} thrown in C:\xampp\htdocs\array_comparison.php on line 19 <?php $con = mysqli_connect("localhost", "root", "","csvupload"); if(ISSET($_POST['import'])) { $filename = $_FILES["file"]["tmp_name"]; $array1 = []; $array2 = []; $file = fopen($filename, "r"); while (($column = fgetcsv($file, 10000, ";")) !== FALSE) { $array1[] = $column[0]; } $res = $con->query("SELECT KNr. FROM adressen_neu"); while ($row = $res->fetch_row() ) { $array2[] = $row[0]; } echo '<pre>' . print_r(array_diff($array1, $array2), 1) . '</pre>'; } ?> <!DOCTYPE html> <html> <head> <title>CSV Upload-Page</title> </head> <body> <form class="form" name="uploadCSV" action="array_comparison.php" method="post" enctype="multipart/form-data"> <div> <label>Select CSV File</label> <input type="file" name="file" accept=".csv"> <button type="submit" name="import">Import</button> <input type="checkbox" name="checkbox" value="checked"> </div> </form> </body> </html> Quote Link to comment Share on other sites More sharing options...
dodgeitorelse3 Posted September 15, 2021 Share Posted September 15, 2021 Is it really knr. in query? Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 15, 2021 Author Share Posted September 15, 2021 13 minutes ago, dodgeitorelse3 said: Is it really knr. in query? KNr. is the name of the column in the db Quote Link to comment Share on other sites More sharing options...
Barand Posted September 15, 2021 Share Posted September 15, 2021 (edited) If you want to use silly names like that with the "." at the end then you need the column name inside backticks. SELECT `KNr.` FROM .... From MySQL manual Quote Permitted characters in unquoted identifiers: ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore) Edited September 15, 2021 by Barand 2 Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 16, 2021 Author Share Posted September 16, 2021 (edited) 18 hours ago, Barand said: If you want to use silly names like that with the "." at the end then you need the column name inside backticks. SELECT `KNr.` FROM .... From MySQL manual Thanks for the advice. I've been able to find out which Arrays haven't been registered into the database. It turned out, every Array with '' in them couldn't be uploaded. The reason was sql query. It couldn't handle these characters since they were already implemented in the code. That's why I've turned the arrays into real escape strings with "mysqli_real_escape_string()". Now everything is working flawlessy. Thanks for your help! Edited September 16, 2021 by KN1V3S Quote Link to comment Share on other sites More sharing options...
Barand Posted September 16, 2021 Share Posted September 16, 2021 The correct fix would be to use prepared queries. CSV files are expected to have "..." around some string values and fgetcsv() should allow for them. Perhaps there is something weird regarding their placement in your data. Quote Link to comment Share on other sites More sharing options...
KN1V3S Posted September 16, 2021 Author Share Posted September 16, 2021 (edited) 2 hours ago, Barand said: The correct fix would be to use prepared queries. CSV files are expected to have "..." around some string values and fgetcsv() should allow for them. Perhaps there is something weird regarding their placement in your data. True, I've heard about prepared queries but as I said, I really am a beginner and don't have the know-how yet. But I will look into that! Thank you Oh and I meant '...' not "..." 😅 Edited September 16, 2021 by KN1V3S Quote Link to comment Share on other sites More sharing options...
gizmola Posted September 19, 2021 Share Posted September 19, 2021 One of the several advantages to using prepared queries is that you don't have to escape special characters. Since you are using mysqli_ * api: Quick example from this Mysqli guide, that is well worth reading through... # replace your php parameters in the VALUES section with '?' for each column you are inserting $sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)"; # Now prepare the statement. It is ready for binding. $stmt= $conn->prepare($sql); #bind the values. The first parameter is a type for each column. 's' is for a string. 'i' is for integer. 'd' is for double/float. 'b' is for binary/blobs $stmt->bind_param("sss", $name, $email, $password); $stmt->execute(); Another thing that is beneficial is that once you bind variables, you can simply set them to new values and do another execute. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.