Jump to content
zetastreak

import csv in mysql db using ajax and php

Recommended Posts

Posted (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 by zetastreak

Share this post


Link to post
Share on other sites

Can you post the first few lines of your input CSV file so we can see what you are trying to process?

Share this post


Link to post
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 |
+----+-------+---------+-------+-----------+---------+------+

 

  • Great Answer 1

Share this post


Link to post
Share on other sites
Posted (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 by zetastreak

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

@taquitosensei - "continue" means continue to the next row, not continue processing this row. (The exact opposite of the way you are using it :) )

Share this post


Link to post
Share on other sites
Posted (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 by taquitosensei

Share this post


Link to post
Share on other sites
$counter = 0; 
while ($row = fgetcsv($fp)) {
    if ($counter > 9) {
         $stmt->execute(array_slice($row,3));
    }
$c++; 
}

Share this post


Link to post
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     |
                           |     ...     |       ...      |    ...  |      ...        |
  

 

Share this post


Link to post
Share on other sites
Posted (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 by Barand

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


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