zetastreak Posted March 19, 2019 Share Posted March 19, 2019 (edited) i was working on a mini project that imports csv file to the database through ajax and it's working fine <?php if(!empty($_FILES["marks_file"]["name"])) { $connect = mysqli_connect("localhost", "root", "", "dbname"); $output = ''; $allowed_ext = array("csv"); $extension = end(explode(".", $_FILES["marks_file"]["name"])); if(in_array($extension, $allowed_ext)) { $file_data = fopen($_FILES["marks_file"]["tmp_name"], 'r'); fgetcsv($file_data); while($row = fgetcsv($file_data)) { $name = mysqli_real_escape_string($connect, $row[0]); $Physics = mysqli_real_escape_string($connect, $row[1]); $Maths = mysqli_real_escape_string($connect, $row[2]); $Chemistry = mysqli_real_escape_string($connect, $row[3]); $Biology = mysqli_real_escape_string($connect, $row[4]); $SST = mysqli_real_escape_string($connect, $row[5]); $query = " INSERT INTO csv (name, Physics, Maths, Chemistry, Biology, SST) VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST') "; mysqli_query($connect, $query); } $select = "SELECT * FROM csv ORDER BY id DESC"; $result = mysqli_query($connect, $select); $output .= ' <table class="table table-bordered"> <tr> <th width="25%" >name</th> <th width="15%" >Physics</th> <th width="15%" >Maths</th> <th width="15%" >Chemistry</th> <th width="15%" >Biology</th> <th width="15%" >SST</th> </tr> '; while($row = mysqli_fetch_array($result)) { $output .= ' <tr> <td>'.$row["name"].'</td> <td>'.$row["Physics"].'</td> <td>'.$row["Maths"].'</td> <td>'.$row["Chemistry"].'</td> <td>'.$row["Biology"].'</td> <td>'.$row["SST"].'</td> </tr> '; } $output .= '</table>'; echo $output; } else { echo 'errorx'; } } else { echo "errory"; } ?> however the imported csv files inserts null values in the tables because the format of all csv files assigned to me are in the exact same format: ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 i changed my code a bit modified the functions to espace blank spaces and not return null values in the data tables while (($row = fgetcsv($file_data, 1000, ",")) !== FALSE) { if ((string) $row[0] != '0' and empty($row[0])) { continue; } $name = mysqli_real_escape_string($connect, $row[0]); $Physics = mysqli_real_escape_string($connect, $row[1]); $Maths = mysqli_real_escape_string($connect, $row[2]); $Chemistry = mysqli_real_escape_string($connect, $row[3]); $Biology = mysqli_real_escape_string($connect, $row[4]); $SST = mysqli_real_escape_string($connect, $row[5]); $query = " INSERT INTO csv (name, Physics, Maths, Chemistry, Biology, SST) VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST') "; mysqli_query($connect, $query); } But it doesn't work with the specified csv files i think the ajax call breaks and that's why nothing happens.However it works fine with csv files without blank spaces Edited March 19, 2019 by zetastreak Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/ Share on other sites More sharing options...
Barand Posted March 19, 2019 Share Posted March 19, 2019 Can you post the first few lines of your input CSV file so we can see what you are trying to process? Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565332 Share on other sites More sharing options...
Barand Posted March 19, 2019 Share Posted March 19, 2019 I asked because the data you posted does not match your code ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 | | $row[3] $row[8] The name is in index position 3, not 0. Also, use prepared statements and use PDO instead of mysqli (it makes life easier) $db = pdoConnect(); $db->exec("CREATE TABLE IF NOT EXISTS zeta ( id int not null auto_increment primary key, name varchar(30), physics int, maths int, chemistry int, biology int, sst int ) "); $stmt = $db->prepare("INSERT INTO zeta (name,physics,maths,chemistry,biology,sst) VALUES (?,?,?,?,?,?) "); $fp = fopen('zeta.csv', 'r'); while ($row = fgetcsv($fp)) { if ($row[3]=='Fields' || $row[3]=='') continue; $stmt->execute(array_slice($row,3)); } fclose($fp); Giving mysql> select * from zeta; +----+-------+---------+-------+-----------+---------+------+ | id | name | physics | maths | chemistry | biology | sst | +----+-------+---------+-------+-----------+---------+------+ | 1 | Name1 | 10 | 25 | 35 | 42 | 62 | | 2 | Name2 | 80 | 45 | 45 | 45 | 25 | | 3 | Name3 | 63 | 25 | 63 | 36 | 36 | | 4 | Name4 | 82 | 36 | 75 | 48 | 42 | | 5 | Name5 | 45 | 45 | 78 | 25 | 24 | | 6 | Name6 | 36 | 36 | 15 | 75 | 36 | | 7 | Name7 | 99 | 45 | 24 | 24 | 45 | | 8 | Name8 | 45 | 85 | 85 | 85 | 96 | +----+-------+---------+-------+-----------+---------+------+ 1 Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565333 Share on other sites More sharing options...
zetastreak Posted March 19, 2019 Author Share Posted March 19, 2019 (edited) 43 minutes ago, Barand said: I asked because the data you posted does not match your code ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 | | $row[3] $row[8] The name is in index position 3, not 0. Also, use prepared statements and use PDO instead of mysqli (it makes life easier) $db = pdoConnect(); $db->exec("CREATE TABLE IF NOT EXISTS zeta ( id int not null auto_increment primary key, name varchar(30), physics int, maths int, chemistry int, biology int, sst int ) "); $stmt = $db->prepare("INSERT INTO zeta (name,physics,maths,chemistry,biology,sst) VALUES (?,?,?,?,?,?) "); $fp = fopen('zeta.csv', 'r'); while ($row = fgetcsv($fp)) { if ($row[3]=='Fields' || $row[3]=='') continue; $stmt->execute(array_slice($row,3)); } fclose($fp); Giving mysql> select * from zeta; +----+-------+---------+-------+-----------+---------+------+ | id | name | physics | maths | chemistry | biology | sst | +----+-------+---------+-------+-----------+---------+------+ | 1 | Name1 | 10 | 25 | 35 | 42 | 62 | | 2 | Name2 | 80 | 45 | 45 | 45 | 25 | | 3 | Name3 | 63 | 25 | 63 | 36 | 36 | | 4 | Name4 | 82 | 36 | 75 | 48 | 42 | | 5 | Name5 | 45 | 45 | 78 | 25 | 24 | | 6 | Name6 | 36 | 36 | 15 | 75 | 36 | | 7 | Name7 | 99 | 45 | 24 | 24 | 45 | | 8 | Name8 | 45 | 85 | 85 | 85 | 96 | +----+-------+---------+-------+-----------+---------+------+ Thanks For the help Barand, pointing out the index errors, i have similiar csv files that are given to me as a part of the project with exact same format . (first 9 rows are blank) and i've to escape them so they don't insert null values. i tried using fgetcsv function and other methods using loops and continue statements however they break the ajax call and nothing happens. p.s. i'll make the switch to PDO. Edited March 19, 2019 by zetastreak Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565335 Share on other sites More sharing options...
taquitosensei Posted March 19, 2019 Share Posted March 19, 2019 You could use a counter and look for the 10th row $counter = 0; while ($row = fgetcsv($fp)) { if ($counter > 9) continue; $stmt->execute(array_slice($row,3)); $c++; } Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565340 Share on other sites More sharing options...
Barand Posted March 19, 2019 Share Posted March 19, 2019 @taquitosensei - "continue" means continue to the next row, not continue processing this row. (The exact opposite of the way you are using it ) Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565347 Share on other sites More sharing options...
taquitosensei Posted March 19, 2019 Share Posted March 19, 2019 (edited) 1 hour ago, Barand said: @taquitosensei - "continue" means continue to the next row, not continue processing this row. (The exact opposite of the way you are using it ) I just didn't read it close enough and copied and pasted and adjusted. Edited March 19, 2019 by taquitosensei Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565364 Share on other sites More sharing options...
taquitosensei Posted March 19, 2019 Share Posted March 19, 2019 $counter = 0; while ($row = fgetcsv($fp)) { if ($counter > 9) { $stmt->execute(array_slice($row,3)); } $c++; } Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565365 Share on other sites More sharing options...
Barand Posted March 20, 2019 Share Posted March 20, 2019 - Now you have impoerted your data you should nomalize the data, so you would have +---------------+ +---------------+ | student | | subject | +---------------+ +---------------+ | student_id |---+ +-----| subject_id | | name | | +---------------+ | | name | | etc | | | score | | +---------------+ +---------------+ | +---------------+ | +---<| student_id | | | subject_id |>---+ | score | | exam_date | +---------------| and the data in those tables would look like this +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | Id | name | | student_id | subject_id | score | exam_date | | subject_id | name | +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | 1 | Name1 | | 1 | 1 | 10 | 2019-03-01 | | 1 | Physics | | 2 | Name2 | | 1 | 2 | 25 | 2019-03-01 | | 1 | Maths | +-----+----------+ | 1 | 3 | 35 | 2019-03-01 | | 1 | Chemistry | | 2 | 1 | 80 | 2019-03-01 | | | | | 2 | 2 | 45 | 2019-03-01 | | 3 | 3 | 45 | 2019-03-01 | | ... | ... | ... | ... | Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565458 Share on other sites More sharing options...
zetastreak Posted March 24, 2019 Author Share Posted March 24, 2019 (edited) On 3/20/2019 at 11:26 PM, Barand said: - Now you have impoerted your data you should nomalize the data, so you would have +---------------+ +---------------+ | student | | subject | +---------------+ +---------------+ | student_id |---+ +-----| subject_id | | name | | +---------------+ | | name | | etc | | | score | | +---------------+ +---------------+ | +---------------+ | +---<| student_id | | | subject_id |>---+ | score | | exam_date | +---------------| and the data in those tables would look like this +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | Id | name | | student_id | subject_id | score | exam_date | | subject_id | name | +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | 1 | Name1 | | 1 | 1 | 10 | 2019-03-01 | | 1 | Physics | | 2 | Name2 | | 1 | 2 | 25 | 2019-03-01 | | 2 | Maths | +-----+----------+ | 1 | 3 | 35 | 2019-03-01 | | 3 | Chemistry | | 2 | 1 | 80 | 2019-03-01 | | | | | 2 | 2 | 45 | 2019-03-01 | | 3 | 3 | 45 | 2019-03-01 | | ... | ... | ... | ... | sure! will work on it, this will enable me to use ajax calls in subjectwise google charts too Edited March 25, 2019 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565586 Share on other sites More sharing options...
NotionCommotion Posted March 25, 2019 Share Posted March 25, 2019 On 3/24/2019 at 7:49 AM, zetastreak said: sure! will work on it, this will enable me to use ajax calls in subjectwise google charts too To do so, consider using https://www.mysql.com/products/workbench/ Quote Link to comment https://forums.phpfreaks.com/topic/308480-import-csv-in-mysql-db-using-ajax-and-php/#findComment-1565595 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.