Jump to content

import csv in mysql db using ajax and php


zetastreak

Recommended Posts

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.