Jump to content

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

suma237

By suma237
in PHP Coding Help

 Share

Recommended Posts

suma237 Member

suma237

Posted
Posted

Why this error?

 

'ÐÏࡱá',create table temp1(id int(20) not null auto_increment,ÐÏࡱá VARCHAR (200) not null,extra varchar(200) not null, primary key(id))could not create

 

The code is:

<?

//include("../mysqlconnect.php");

$link = mysql_connect("localhost","root","")

      or die("Could not connect: " . mysql_error());

mysql_select_db("suma");

 

include("include/upload.inc.php");

if(isset($_POST['upload']))

{

$deli=$_POST['delimeter'];

 

$userfile=$_FILES['userfile']['name'];

$userfile_tmp=$_FILES['userfile']['tmp_name'];

$userfile_type=$_FILES['userfile']['type'];

$userfile_size=$_FILES['userfile']['size'];

$dir="upload";

$filename=basename($userfile);

move_uploaded_file("$userfile_tmp","$dir/$filename");

$row= 1;

$fp = fopen("$dir/$userfile", "r");

$s="";

$s1="";

$s2="";

 

while (($data = fgetcsv($fp, 100000, "$deli")) !== FALSE)

{

    $num = count($data);

    $row++;

if($row==2)

{

    for ($c=0; $c < $num; $c++)

{

        $s.=$data[$c]." VARCHAR (200) not null,";

$s2.=$data[$c].",";

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

}

$sql="create table temp1(id int(20) not null auto_increment,".$s."extra varchar(200) not null, primary key(id))";

mysql_query($sql)or die("could not create");

 

$sql1="insert into temp1 (".$s2."extra) values (".$s1."'choose')";

mysql_query($sql1)or die("could not insert");

}

 

if($row > 2)

{

$s1="";

    for ($c=0; $c < $num; $c++)

{

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

}

$sql2="insert into temp1 (".$s2."extra) values (".$s1."'choose')";

mysql_query($sql2)or die("could not insert");

}

}

 

fclose($fp);

}

 

?>

 

<table>

 

<form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">

 

<tr class="greyBG">

      <td width="60%" height="33"> <div align="left">Please Enter the delimeter

        </div></td>

      <td width="40%"><input type="text" name="delimeter"> </td>

    </tr>

<tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td>

 

 

 

 

</tr>

<tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>

</form>

</table>

 

 

Link to comment
Share on other sites
redarrow Advanced Member

redarrow

Posted
Posted

use mysql statement to get the infomation in the database ok.

 

take a look at a prevous post

 

<?php
$row = 1;
$handle = fopen("jos_muse_users1.csv", "are");
while (($data = fgetcsv($handle, 5774, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");

//---- database
$dbhost = 'localhost';
$dbusername = 'username';
$dbpasswd = 'password';
$database_name = 'MCJoomla';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

$query = "INSERT INTO jos_muse_users (MemberID, ContactID, GivenName, FamilyName, Email, AddressAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth ,Address1, Address2, PostalCode, SourceForm, NRIC, Race, Nationality, ChildName, ChildDOB, ChildGender, City, State, Country, SourcePortal) VALUES ($SQLq)";
echo $query;
$sql = mysql_query($query) or die (mysql_error());
//--- database
} //<--this one to close the while loop
fclose($handle);
?>

Link to comment
Share on other sites
matto Newbie

matto

Posted
Posted

To answer your question: How can I check if the file is reading or not....

 

$fp = fopen("$dir/$userfile", "r") or die("<tt>unable to read file</tt>\n");

 

Also, when you are creating a "temp" table you really should use the following.

 

create temporary table if not exists temp1...

 

If your code above is run more than once your table "temp1" will already exist unless you drop the table before the script has finished executing. Looks like you may be better to have a permanent table and delete all rows at the start of your script and then load the cvs data in....

 

:)

Link to comment
Share on other sites
aniesh82 Member

aniesh82

Posted
Posted

 

Hi Suma,

 

Are you sure that the csv file uploaded and copy to the directory?

Please make sure that first.

 

If the file is moved to teh directory in the server, then please go through the following piece of code. I used this to read the cotenst from a file and insert into the table.

 

$handle = fopen($city,"r");

while(($data = fgetcsv($handle, 1000, ",")) !== FALSE)

{

$countryCode = $data[0];

$cityName = $data[2];

$latitude = $data[3];

$longitude = $data[4];

 

$sql = 'INSERT INTO nameCity (countryCode , cityName , latitude, longitude  ) VALUES ("'.$countryCode.'", "'.$cityName.'","'.$latitude.'","'.$longitude.'")';

mysql_query($sql);

 

}

 

 

 

Link to comment
Share on other sites
suma237 Member

suma237

Posted
  • Author
Posted

one field (name) is inserting in the table .how to insert second field?

 

code:

 

$row = 1;

$handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

    $num = count($data);

    echo "<p> $num fields in line $row: <br /></p>\n";

    $row++;

$SQLq ="";

if($row==2)

{

    for ($c=0; $c < $num; $c++) {

        echo $data[$c] . "<br />\n";

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

    }

$SQLq = trim($SQLq, ",");

 

 

$query = "INSERT INTO test(name,address) VALUES ($SQLq)";

echo $query;

$sql = mysql_query($query) or die (mysql_error());

//--- database

 

 

}

} //<--this one to close the while loop

fclose($handle);

 

 

Link to comment
Share on other sites
suma237 Member

suma237

Posted
  • Author
Posted

code is..

 

 

<?
//---- database
$dbhost = 'localhost';
$dbusername = 'root';
$dbpasswd = '';
$database_name = 'suma';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

include("include/upload.inc.php");
if(isset($_POST['upload']))
{


$userfile=$_FILES['userfile']['name'];
$userfile_tmp=$_FILES['userfile']['tmp_name'];
$userfile_type=$_FILES['userfile']['type'];
$userfile_size=$_FILES['userfile']['size'];
$dir="upload";
$filename=basename($userfile);
move_uploaded_file("$userfile_tmp","$dir/$filename");


//echo "$dir"."/"."$userfile";


$row = 1;
$handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
if($row==2)
{
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");


$query = "INSERT INTO test(name,address) VALUES ($SQLq)";
echo $query;
$sql = mysql_query($query) or die (mysql_error());
//--- database
} //<--this one to close the while loop
fclose($handle);




}//end of isset



?>

<table>

<form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">


<tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td>




</tr>
<tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
</form>
</table>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Link to comment
Share on other sites
Yesideez Advanced Member

Yesideez

Posted
Posted

When writing scripts its actually quite important to try and tabulate the code to make it easier to read. If in say a month or even a year's time you come back to your script what do you reckon the chances are that you'd be able to read it quickly without having to step through it figuring out what has been done?

 

<?php
//---- database
  $dbhost = '192.168.1.60';
  $dbusername = 'root';
  $dbpasswd = '';
  $database_name = 'suma';
  $connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") or die ("Couldn't connect to server.");
  $db = mysql_select_db("$database_name", $connection) or die("Couldn't select database.");
  include("include/upload.inc.php");
  if (isset($_POST['upload'])) {
    $userfile=$_FILES['userfile']['name'];
    $userfile_tmp=$_FILES['userfile']['tmp_name'];
    $userfile_type=$_FILES['userfile']['type'];
    $userfile_size=$_FILES['userfile']['size'];
    $dir="upload";
    $filename=basename($userfile);
    move_uploaded_file("$userfile_tmp","$dir/$filename");
    //echo "$dir"."/"."$userfile";
    $row = 1;
    $handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");
    while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
      $num = count($data);
      echo "<p> $num fields in line $row: <br /></p>\n";
      $row++;
      $SQLq ="";
      if ($row==2) {
        for ($c=0; $c < $num; $c++) {
          echo $data[$c] . "<br />\n";
          $SQLq .= "$data[$c],";
        }
        $SQLq = trim($SQLq, ",");
        $query = "INSERT INTO test (name,address) VALUES ($SQLq)";
        echo 'SQL='.$query;
        //$sql = mysql_query($query) or die (mysql_error());
        //--- database
    } //<--this one to close the while loop
    fclose($handle);
  }//end of isset
?>
<table>
  <form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">
    <tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td></tr>
    <tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
  </form>
</table>

 

If you can try that and post here the line you get starting with SQL= we'll take it from there and see what happens. It won't actually run the query as I've disabled it temporarily so we can concentrate on debugging the INSERT query.

Link to comment
Share on other sites
suma237 Member

suma237

Posted
  • Author
Posted

please check the code with proper alignment.Thanks for your advice

<?
//---- database
$dbhost = '192.168.1.60';
$dbusername = 'root';
$dbpasswd = '';
$database_name = 'suma';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

include("include/upload.inc.php");
if(isset($_POST['upload']))
{


$userfile=$_FILES['userfile']['name'];
$userfile_tmp=$_FILES['userfile']['tmp_name'];
$userfile_type=$_FILES['userfile']['type'];
$userfile_size=$_FILES['userfile']['size'];
$dir="upload";
$filename=basename($userfile);
move_uploaded_file("$userfile_tmp","$dir/$filename");



$row = 1;
$handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
			{

			$num = count($data);
			echo "<p> $num fields in line $row: <br /></p>\n";
			$row++;
		$SQLq ="";
			if($row==2)
					{
					for ($c=0; $c < $num; $c++)
						{
							echo $data[$c] . "<br />\n";
							$SQLq .= "$data[$c],";
						}
			$SQLq = trim($SQLq, ",");


			$query = "INSERT INTO test(name,address) VALUES ($SQLq)";
			echo $query;
			$sql = mysql_query($query) or die (mysql_error());
			//--- database

					}
		} //<--this one to close the while loop
fclose($handle);








}//end of isset



?>

<table>

<form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">


<tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td>




</tr>
<tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
</form>
</table>

Link to comment
Share on other sites
Yesideez Advanced Member

Yesideez

Posted
Posted

It takes long time to replace the spaces and tabs - I've added some proper error checking:

<?php
//---- database
  $dbhost='192.168.1.60';
  $dbusername='root';
  $dbpasswd='';
  $database_name='suma';
  $connection=mysql_pconnect("$dbhost","$dbusername","$dbpasswd") or die ("Couldn't connect to server.");
  $db=mysql_select_db("$database_name", $connection) or die("Couldn't select database.");
  include("include/upload.inc.php");
  if (isset($_POST['upload'])) {
    $userfile=$_FILES['userfile']['name'];
    $userfile_tmp=$_FILES['userfile']['tmp_name'];
    $userfile_type=$_FILES['userfile']['type'];
    $userfile_size=$_FILES['userfile']['size'];
    $dir="upload";
    $filename=basename($userfile);
    if (move_uploaded_file("$userfile_tmp","$dir/$filename")) {
      //echo "$dir"."/"."$userfile";
      $row = 1;
      if ($handle=fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n")) {
        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>1) {
            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='.$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';}
    } else {echo 'Error uploading file';}
  }//end of isset
?>
<table>
  <form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">
    <tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td></tr>
    <tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
  </form>
</table>

 

If you save that out as a new file and upload it see if it works. I've changed the way the routine builds the INSERT query slightly.

Link to comment
Share on other sites
Yesideez Advanced Member

Yesideez

Posted
Posted

A line in the above code is echo'ing a line to your browser which starts with "SQL=" and this is the contents of the INSERT query which is being called. If you show me this line we will be able to see what is going wrong.

 

If you can also show the first couple lines of your CSV file we can then compare that with what is being used in the query.

Link to comment
Share on other sites
suma237 Member

suma237

Posted
  • Author
Posted

you r right...insert statement is showing error

 

error

 

2 fields in line 1:

 

 

Address

SQL=INSERT INTO test(name,address) VALUES (Name,Address)

Error at line 1

 

2 fields in line 2:

 

 

shanthalayam

SQL=INSERT INTO test(name,address) VALUES (suma,shanthalayam)

Error at line 2

 

2 fields in line 3:

 

 

21 Flat

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

Error at line 3

 

2 fields in line 4:

 

 

23 tower road

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

Error at line 4

 

 

Link to comment
Share on other sites
Yesideez Advanced Member

Yesideez

Posted
Posted

Now we're getting somewhere.

 

In your table have you got `name` and `address` and are these fields either VARCHAR or TEXT?

 

Next change these lines:

          $SQLq ="'".$data[0]."'";

 

            $query = "INSERT INTO test (`name`,`address`) VALUES (".$SQLq.")";

 

See if that works and show the browser output.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
 Share
Go to topic listing
×
×
  • 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.