[SOLVED] loop problem while uploading csv file into the database

[suma237]

now error is showing the value is not inserting into the database

ERROR

2 fields in line 1:

 

 

52gghg

SQL=INSERT INTO test (`name`,`address`) VALUES ('suma',52gghg)

Error at line 1

 

2 fields in line 2:

 

 

21 Flat

SQL=INSERT INTO test (`name`,`address`) VALUES ('shilpa',21 Flat)

Error at line 2

 

2 fields in line 3:

 

 

23 tower road

SQL=INSERT INTO test (`name`,`address`) VALUES ('kumar',23 tower road)

Error at line 3

 

[Yesideez]

Sorry I forgot to add this line that needs changing as well:

              $SQLq .= ",'".$data[$c]."'";

 

What I've done is enclose the data within single quotes as I think it's their absence that is causing problems.

[suma237]

type of the field is varchar

 

error is

 

2 fields in line 1:

 

 

52gghg

SQL=INSERT INTO test (`name`,`address`) VALUES ('suma',52gghg)

Error at line 1

 

2 fields in line 2:

 

 

21 Flat

SQL=INSERT INTO test (`name`,`address`) VALUES ('shilpa',21 Flat)

Error at line 2

 

2 fields in line 3:

 

 

23 tower road

SQL=INSERT INTO test (`name`,`address`) VALUES ('kumar',23 tower road)

Error at line 3

 

[Yesideez]

Did you change that third line I just posted? I can't see single quotes surrounding the second piece of data yet!

[suma237]

i changed the code

 

ERROR

2 fields in line 1:

 

 

52gghg

SQL=INSERT INTO test (`name`,`address`) VALUES ('suma','52gghg')

Error at line 1

 

2 fields in line 2:

 

 

21 Flat

SQL=INSERT INTO test (`name`,`address`) VALUES ('shilpa','21 Flat')

Error at line 2

 

2 fields in line 3:

 

 

23 tower road

SQL=INSERT INTO test (`name`,`address`) VALUES ('kumar','23 tower road')

Error at line 3

 

[suma237]

i will try to rectify this error and let you know the progress..please help me

now i can't do this code...tomorrow i will try..any way thanks a lot.Thanks

[Yesideez]

If I've changed your code in any way you don't understand say and I'll do my best to explain it.

 

Do you only have "name" and "address" in your CSV file?

[Yesideez]

I've created the following table on one of my databases:

  CREATE TABLE `test` (
  `testid` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 50 ) NOT NULL ,
  `address` VARCHAR( 100 ) NOT NULL
  ) ENGINE = MYISAM ;

 

I then created the following text file and uploaded it as "file.csv":

name,address
suma,52gghg
shilpa,21 Flat
kumar,23 tower road

 

I then took your script and the only things I edited was to make it read from the file I uploaded rather than usign an upload form. You're getting data from the CSV shown in the browser so we know your CSV file is read read perfectly fine:

<?php
  $dbh=mysql_connect("localhost","zeb_misc","EDITED") or die('I cannot connect to the database because: '.mysql_error());
  mysql_select_db("zeb_misc");
  $row = 1;
  if ($handle=fopen("file.csv", "r")) {
    while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
      $num = count($data);
      echo "<p> $num fields in line $row: <br /></p>\n";
      $row++;
      $SQLq ="'".$data[0]."'";
      if ($row>2) {
        for ($c=1; $c < $num; $c++) {
          echo $data[$c] . "<br />\n";
          $SQLq .= ",'".$data[$c]."'";
        }
        $SQLq = trim($SQLq, ",");
        $query = "INSERT INTO test (`name`,`address`) VALUES (".$SQLq.")";
        echo 'SQL line '.($row-1).'='.$query.'<br />';
        if (!mysql_query($query)) {echo 'Error at line '.($row-1).'<br />';}
      }
      //--- database
    } //<--this one to close the while loop
    fclose($handle);
  } else {echo 'Error opening file';}
?>

 

Here's a screenshot of your data sitting in my table after I ran the above script:

 

As you can see it's sat there. The only mistake I made was to make it ignore the first line which is the column names (but this is fixed in the above script I've shown). I'm thinking the problem actually lies with your database.

 

Check if your user has permissions set properly and that your table structure is capable of holding the data by having correct datatypes and field names.

[suma237]

hi Yesideez ..  thank u very much for your help ..  got it  solved ultimately..

 

thank u all ..