skip first line on insert

[flemingmike]

hello, how would i skip the first line on inserting the csv?

 

<?php
include '../style.php';

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

   { 

     $filename=$_POST['filename']; 

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

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

     {    

       $import="INSERT into goaliestats(id,name,gp,w,so,gaa,date) values(NULL,'$data[0]','$data[2]','$data[8]','$data[26]','$data[18]','$data[28]')";

       mysql_query($import) or die(mysql_error()); 

     } 

     fclose($handle); 

     print "Import done"; 

  

   } 

   else 

   { 

  
      print "<form method='post'>"; 

      print "Type file name to import:<br>"; 

      print "<input type='file' name='filename' size='20'><br>"; 

      print "<input type='submit' name='submit' value='submit'></form>"; 

   }  


   
   ?>

[Zane]

Not sure what you mean by skip the first line, but if you want to skip the first entry.. then you'd do this

 

Also, you should never put a mysql_query inside a loop.  Create your query first and then query it.. It will run so so much smoother.

To fix this, I have used a counter to skip the first loop.

     $handle = fopen("$filename", "r"); 
     $x = 0;
     while(($data = fgetcsv($handle, 1000, ",")) !== FALSE) 

     {    
       if(!$x) continue;
       $import .= "INSERT into goaliestats(id,name,gp,w,so,gaa,date) values(NULL,'$data[0]','$data[2]','$data[8]','$data[26]','$data[18]','$data[28]')" . "\n";
       $x++;
     }
     mysql_query($import) or die(mysql_error()); 
     fclose($handle); 

[flemingmike]

thanks for the help.  im getting an error now

 

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in E:\Website\hockeypool\admin\import.php on line 12

 

Line 12 is

 

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

[flemingmike]

also getting

 

Warning: fopen(NHL Goalies.csv) [function.fopen]: failed to open stream: No such file or directory in E:\Website\hockeypool\admin\import.php on line 10

[Zane]

fgetcsv() expects parameter 1 to be resource, boolean given

 

Q : Now if $handle is the resource, why would it be a boolean?

A : It would be boolean if the $handle is false..

 

In other words, the error isn't on line 12, but it is on the line where you create $handle.

[flemingmike]

ok, i get my $handle from

 

     $filename=$_POST['filename']; 

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

 

 

[Zane]

Yep, now the only way that could fail is if the file does not exist.. or doesn't have read permissions, which I doubt.

 

The most likely cause is that $_POST['filename'] has no value.

The best way to check this is to echo $filename after you create it.

[flemingmike]

it echo's the file name that im posting

[Zane]

it echo's the file name that im posting

So are you saying that the filename you see displayed is ALSO in the same directory as your php file?

[flemingmike]

no, im posting it in a form

[Zane]

yes, but are you positive that this file exists?

 

Currently your code looks in the same directory for that filename.  The only way fopen would return false is if it didn't exist.

[flemingmike]

ya, this is way advanced for me.  thanks for the help anyways.  ill just get the users to email me the csv and ill use phpmyadmin.

[TOA]

Don't give up so easily. Are your php file and the csv in the same folder? If not, then you need to add the path to $handle. That's all Zane's saying (I think, not trying to put words in his mouth).

[flemingmike]

the file isnt on the server, thats the issue i think.  i want a user to be able to upload their csv file and have it insert into the table

[TOA]

So Zane is right, the file doesn't exist because that isn't what you described before. Your form takes a string and tries to open a file with that name. If youre allowing an upload, you need to handle it from it's temp location.

Read this on handling file uploads

[xyph]

He's uploading the files through a form. It's actually quite easy

 

Check out my example here

<?php 

// Make sure the file has been uploaded
if( !empty($_FILES['csv']) ) {
// So we don't have to type $_FILES['csv'] all the time - used for neatness
$file = $_FILES['csv'];
// Check for errors
if( $file['error'] ) trigger_error( "Error #{$file['error']} while uploading", E_USER_ERROR );
// Verify extension
if( end(explode('.',$file['name'])) != 'csv' ) trigger_error( 'Bad extension error', E_USER_ERROR );
// Load CSV into array
$handle = fopen( $file['tmp_name'], 'r' ) or trigger_error( 'Could not fopen file', E_USER_ERROR );
// Initiate the array that will hold our data
$data = array();
// Loop through lines
while( $line = fgetcsv($handle, $file['size']) )
	// Assign each line to a new key in $data
	$data[] = $line;
// Show data
echo '<pre>';
print_r( $data );
echo '</pre>';
}

?>
<form action="#" method="post" id="thisForm" enctype="multipart/form-data">
<label>Place a text file here: <input type="file" name="csv"></label><br>
<input type="submit">
</form>

[TOA]

He's uploading the files through a form.

 

Oh snap! I read "text" for the input type for some reason..my bad 

[flemingmike]

ok, so this is what i have come up with.  i can get the file to upload, move, and delete.  i still cannot get the info to insert into the database.

 

<form action="import.php" method="post"
enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file" /> 
<br />
<input type="submit" name="submit" value="Submit" />
</form>


<?php

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

   { 

   if ($_FILES["file"]["error"] > 0)
     {
     echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
     }
   else
     {

    if (file_exists("uploads/" . $_FILES["file"]["name"]))
       {
    echo $_FILES["file"]["name"] . " already exists. ";

       }
     else
       {
       move_uploaded_file($_FILES["file"]["tmp_name"],
       "uploads/" . $_FILES["file"]["name"]);
       
   
   $filename=$_FILES["file"]["name"]; 

     $handle = fopen("uploads/$filename", "r");
 $x = 0;
     while(($data = fgetcsv($handle, 1000, ",")) !== FALSE)      {
 if(!$x) continue;
 $import .= "INSERT into goaliestats(id,name,gp,w,so,gaa,date) values(NULL,'$data[0]','$data[2]','$data[8]','$data[26]','$data[18]','$data[28]')" . "\n";
 $x++;
 }
     
     ysql_query($import) or die(mysql_error());

 fclose($handle); 


     print "Import done"; 


 unlink("uploads/" . $_FILES["file"]["name"]);
   
       }
     } 



}

?> 

[flemingmike]

oops.. i didnt see your replies everyone.  let me retract my previous statement.

[flemingmike]

i get an Undefined variable: import in E:\Website\hockeypool\admin\import.php on line 33

 

here are the first 4 lines of my csv

 

 Name,Team,GP,,GS,,MIN,,W,,L,,OTL,,EGA,,GA,,GAA,,SA,,SV,,SV%,,SO,,Date
Carey Price,MON,72,,70,,4206,,38,,28,,6,,7,,165,,2.35,,2147,,1982,,0.923,,8,,9/29/2011
Roberto Luongo,VAN,60,,60,,3590,,38,,15,,7,,3,,126,,2.11,,1753,,1627,,0.928,,4,,9/29/2011
Cam Ward,CAR,74,,74,,4318,,37,,26,,10,,7,,184,,2.56,,2375,,2191,,0.923,,4,,9/29/2011

 

<form action="import.php" method="post" enctype="multipart/form-data"> 
<label for="file">Filename:</label> 
<input type="file" name="file" id="file" /> <br /> 
<input type="submit" name="submit" value="Submit" /> 
</form>  

<?php
include '../config.php';


if(isset($_POST['submit']))    {
    
if ($_FILES["file"]["error"] > 0)     {
echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
}   else     {

	if (file_exists("uploads/" . $_FILES["file"]["name"]))       {
	echo "Please Try Again. ";
	unlink("uploads/" . $_FILES["file"]["name"]);

	}     else       {

move_uploaded_file($_FILES["file"]["tmp_name"],       "uploads/" . $_FILES["file"]["name"]);
$filename=$_FILES["file"]["name"];
$handle = fopen("uploads/$filename", "r");
$x = 0;
while(($data = fgetcsv($handle, 1000, ",")) !== FALSE)      {
if(!$x) continue;
$import .= "INSERT into goaliestats(id,name,gp,w,so,gaa,date) values(NULL,'$data[0]','$data[2]','$data[8]','$data[26]','$data[18]','$data[28]')" . "\n";
$x++;
}

mysql_query($import) or die(mysql_error());
fclose($handle);
print "Import done";
unlink("uploads/" . $_FILES["file"]["name"]);
}
}
}


?> 

[flemingmike]

is it a problem with this?

 

$import .= "INSERT into goaliestats(id,name,gp,w,so,gaa,date) values(NULL,'$data[0]','$data[2]','$data[8]','$data[26]','$data[18]','$data[28]')" . "\n";	

[flemingmike]

ok, so i removed the part about $x and i now get just the first record to insert.  here is my error:

 

Notice: Undefined variable: import in E:\Website\hockeypool\admin\import.php on line 29
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT into goaliestats(id,name,gp,w,so,gaa,date) values(NULL,'Carey Price','72'' at line 2

 

here is my code mod

 

	$handle = fopen("uploads/$filename", "r");	

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

$import .= "INSERT into goaliestats(id,name,gp,w,so,gaa,date) values(NULL,'$data[0]','$data[2]','$data[8]','$data[26]','$data[18]','$data[28]')" . "\n";
$x++;

mysql_query($import) or die(mysql_error());
}


fclose($handle);

[flemingmike]

is it because there is a double comma after 72?

 

Carey Price,MON,72,,70,,4206