9 Replies - 886 Views - Last Post: 14 March 2016 - 10:14 AM Rate Topic: -----

#1 redrooster36   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 21-December 14

Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 12 March 2016 - 03:36 PM

**sorry about the error in the title, dont know how to change it :(**

Hello people,

I currently have code that uploads a CSV file but I am using the PATHINFO_EXTENSION to prevent any other file types being submitted (file types other than .csv)

This is the code (with html):
<!DOCTYPE html>
<html>
<body>

<?php
$con = mysqli_connect("localhost","root","","DBName"); 

 if ($_FILES['csv']['size'] > 0) { 

		//get the csv file 
		$file = $_FILES['csv']['tmp_name']; 
		$handle = fopen($file,"r"); 
		
		$file_type = pathinfo($file,PATHINFO_EXTENSION);
		
		//loop through the csv file and insert into database 
		while ($data = fgetcsv($handle,1000,",")) { 
			if ($data[0]) { 
				mysqli_query($con, "INSERT INTO users (username, first_name, last_name) VALUES 
					( 
						'".addslashes		($data[0])."',  
						'".addslashes		($data[1])."', 
						'".addslashes		($data[2])."'
					) 
				"); 
				
			} 
			
		}  
		exit; 
} 
?> 

<form action="" method="post" enctype="multipart/form-data">
    	<input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
</form>

</body>
</html>



I have the following problems:

==> I initially used $file = $_FILES['csv']['tmp_name']; but the basename came up randomly and the extension was a .tmp even thought the file I uploaded was a .csv
I then changed it to $file = $_FILES['csv']['name']; but I got the following error:

Warning: fopen(computing.csv): failed to open stream: No such file or directory in ...
Warning: fgetcsv() expects parameter 1 to be resource, boolean given in ...


==> Even when the code is working fine i.e. imports the information in the CSV file into the database, even then there is an error that says:
Notice: Undefined index: csv in ...


Please can anyone explain and help what the problem is with the code and I have been searching for a while and cant seem to find a solution.

Any help is appreciated, thanks.

This post has been edited by redrooster36: 12 March 2016 - 03:47 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Importing file to a mysql database using PATHINFO_EXTENSION problem

#2 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3837
  • View blog
  • Posts: 13,996
  • Joined: 08-August 08

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 12 March 2016 - 04:37 PM

Deprecated MySQL functions are simpler to use (not counting all the sanitizing you need to add) than PDO or MySQLi prepared statements, but far less secure. That's why they've been deprecated. If you're just learning PHP you should start with
http://www.dreaminco...duction-to-pdo/
or
http://php.net/manua...-statements.php
When dealing with old code that uses deprecated MySQL functions you should replace them if possible. If it's not possible, your experience with PDO or MySQLi will benefit you when dealing with MySQL functions.

Note that treating MySQLi like MySQL defeats the i in MySQLi.
Was This Post Helpful? 0
  • +
  • -

#3 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3151
  • View blog
  • Posts: 11,956
  • Joined: 03-December 12

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 12 March 2016 - 08:02 PM

You need to upload the file, move it to a new destination, attempt to insert the data to the database (using prepared statements, not using the 'addslashes' crap). Then, on successful insertion, remove the file from the location.

When a file initially get uploaded, it is given a temporary name and extension, when you move it you change the filename, or leave it, and the file extension is set. Before that it is in a holding area (the tmp directory).
Was This Post Helpful? 0
  • +
  • -

#4 redrooster36   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 21-December 14

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 13 March 2016 - 06:38 AM

View PostCTphpnwb, on 12 March 2016 - 04:37 PM, said:

Deprecated MySQL functions are simpler to use (not counting all the sanitizing you need to add) than PDO or MySQLi prepared statements, but far less secure. That's why they've been deprecated. If you're just learning PHP you should start with
http://www.dreaminco...duction-to-pdo/
or
http://php.net/manua...-statements.php
When dealing with old code that uses deprecated MySQL functions you should replace them if possible. If it's not possible, your experience with PDO or MySQLi will benefit you when dealing with MySQL functions.

Note that treating MySQLi like MySQL defeats the i in MySQLi.


Thanks for the links, but I'm not sure what parts of the code I'm using is deprecated, please could you explain.

View Postastonecipher, on 12 March 2016 - 08:02 PM, said:

You need to upload the file, move it to a new destination, attempt to insert the data to the database (using prepared statements, not using the 'addslashes' crap). Then, on successful insertion, remove the file from the location.

When a file initially get uploaded, it is given a temporary name and extension, when you move it you change the filename, or leave it, and the file extension is set. Before that it is in a holding area (the tmp directory).


Im pretty new to PHP and I haven't found anything that explains the details on how to do you suggestions anywhere else, would you mind giving some pointers or elaborating please.

Appreciate the help so far :)/>/>

This post has been edited by redrooster36: 13 March 2016 - 06:39 AM

Was This Post Helpful? 0
  • +
  • -

#5 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3837
  • View blog
  • Posts: 13,996
  • Joined: 08-August 08

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 13 March 2016 - 11:47 AM

You should always be using prepared statements. Never put data directly in a query.
Was This Post Helpful? 0
  • +
  • -

#6 redrooster36   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 21-December 14

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 13 March 2016 - 01:58 PM

Ahh ok, I understand now. Will look it up now.
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3151
  • View blog
  • Posts: 11,956
  • Joined: 03-December 12

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 13 March 2016 - 03:20 PM

I layed out the steps, which is causing issue?
Was This Post Helpful? 0
  • +
  • -

#8 redrooster36   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 21-December 14

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 14 March 2016 - 08:13 AM

I have uploaded the file into a new location and can import the data to a database, but I was thinking that the data would need to be validated, i.e. all the lines in the csv have the same number of columns, and that each column would need to be validated aswell (a column named first name shouldn't have any numbers in it)

I have found some code that will check apparently checks that each line has the selected number of columns but it doesnt seem to be working:
$allowedColNum=5;
$batchcount=0;
$file = fopen($file_name, "r"); 
while ($line = fgetcsv($file)){

  // count($line) is the number of columns
  $numcols = count($line);

  // Bail out of the loop if columns are incorrect
  if ($numcols != $allowedColNum) {
     break;
  }
  $col = $line[0]; 
  echo $batchcount++.". ".$col."\n";
}



When I run the code nothing shows up. Im not sure whats wrong

This post has been edited by redrooster36: 14 March 2016 - 08:14 AM

Was This Post Helpful? 0
  • +
  • -

#9 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3151
  • View blog
  • Posts: 11,956
  • Joined: 03-December 12

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 14 March 2016 - 08:48 AM

"Found some code" never leads to a good sentence.

You might want to disconnect the database for a few minutes and focus on what the current issue it.

Throw some print statements in to see what it is doing, fgetcsv returns an array of the data read. You are allowing an array size of 5 total columns. So, if the column count is not exactly 5, you stop what you are doing, is that what you want to do? Or should it let you know that the columns expected is not what is there?

Next, you take the first value from each line read and increment batchCount by 1.
Was This Post Helpful? 0
  • +
  • -

#10 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2764
  • View blog
  • Posts: 8,071
  • Joined: 15-January 14

Re: Importing file to a mysql database using PATHINFO_EXTENSION problem

Posted 14 March 2016 - 10:14 AM

The SPLFileObject is a better way of processing a CSV file:

http://php.net/manua...lfileobject.php

e.g.:

$row_nr = 0;
$file = new SplFileObject('file.csv');
$file->setFlags(SplFileObject::READ_CSV | SplFileObject::READ_AHEAD | SplFileObject::SKIP_EMPTY);
foreach ($file as $row)
{
  $row_nr++;
  echo 'Row ' . $row_nr . ' has ' . count($row) . ' fields<br>';
}


Each row will be a numerically indexed array with the fields on that row. You can loop through the file once to validate it, for example if you want to make sure that every row has the same number of fields, and then loop through a second time to process it, or you can just loop through it once depending on your requirements.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1