import csv in mysql db using ajax and php

zetastreak · March 19, 2019

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

Barand · 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?

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

zetastreak · March 19, 2019

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

taquitosensei · 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++; 
}

Barand · 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 )

taquitosensei · March 19, 2019

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

taquitosensei · March 19, 2019

$counter = 0; 
while ($row = fgetcsv($fp)) {
    if ($counter > 9) {
         $stmt->execute(array_slice($row,3));
    }
$c++; 
}

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

zetastreak · March 24, 2019

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

NotionCommotion · 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/

Sign In

import csv in mysql db using ajax and php

Recommended Posts

zetastreak

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

zetastreak

Link to comment

Share on other sites

taquitosensei

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

taquitosensei

Link to comment

Share on other sites

taquitosensei

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

zetastreak

Link to comment

Share on other sites

NotionCommotion

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information