Jump to content

looping to read a csv file


Capt1701a

Recommended Posts

I need some assistance in trying to resolve some issue I have with some code. Currently when I click on an upload button, this should read my csv file and the put the data into phpmyadmin but after looking around various forums and websites I still can not seem to get this to work any assistance would be appreciated.

<?php
$db	=	new mysqli('localhost','root','','');
if ($db->connect_errno) {
  echo "Failed to connect to MySQL: " . $db->connect_error;
  exit();
}
?>
<t<html>
<head>
  <title>Local Care Direct </title>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
</head>
<body>
<form method="POST" enctype="multipart/form-data" action="<?php echo $_SERVER["PHP_SELF"]; ?>">
                         <div align="center">
                                  <p>Select CSV file: <input type="file" name="file"  /></p>
                                  <p><input type="submit" name="csv_upload_btn" value="Upload"  /></p>
                         </div>
                </form>
<table align="center"  width="800" border="1" style="border-collapse:collapse; border:1px solid #ddd;" cellpadding="5" cellspacing="0">
	<thead>
		<tr bgcolor="#FFCC00">
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
			<th>test</th>
		</tr>
	</thead>
	<tbody>
	<?php
	
	// Create database
$sql = "CREATE DATABASE IF NOT EXISTS csv";
if (mysqli_query($db, $sql)) 
{
  echo "Database created successfully";//confirmation message
} else
	{
		
  echo "Error creating database: " . mysqli_error($db);//error message and reason mysql  or php
  mysqli_close($db);
	}

//define the database to use
$sql = "Use `csv`";
if (mysqli_query($db, $sql)) 
{
  echo " Use csv successfully";//confirmation message
} else
	{
		
  echo " Error use csv: " . mysqli_error($db);//error message and reason mysql  or php
  mysqli_close($db);
	}

//create the table in the database
$sql = "CREATE TABLE IF NOT EXISTS`csvfile` (
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(77) DEFAULT NULL,
  `Test` varchar(4013) DEFAULT NULL,
  `Test` varchar(16) DEFAULT NULL,
  `Test` varchar(16) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(16) DEFAULT NULL,
  `Test` varchar(22) DEFAULT NULL,
  `Test` varchar(16) DEFAULT NULL,
  `Test` varchar(22) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test`varchar(10) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(10) DEFAULT NULL,
  `Test` int(8) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
";
if (mysqli_query($db, $sql)) 
{
  echo " table created successfully";//confirmation message
} else
	{
		
  echo  " Error creating table: " . mysqli_error($db);//error message and reason mysql  or php
  mysqli_close($db);
	}
	
//in case of data already in the csvtable, delete the data
$sql = "DELETE FROM `csvfile`";	
if (mysqli_query($db, $sql)) 
{
  echo " deleted data from the table successfully "; //confirmation message
} else
	{
		
  echo  " Error deleting data from the table: " . mysqli_error($db); //error message and reason mysql  or php
  mysqli_close($db);
	}
	

//do not delete	
//Process form
 if(isset($_POST["csv_upload_btn"]))	 
	 $file = fopen("csv3.csv","r")or die("Failed to read file");
print_r(fgetcsv($file))== NULL;
for ($i = 0; $row = fgetcsv($file); ++$i) {

}

fclose($file);
 
	 
	
	 
		/* $db->query('INSERT INTO `csvfile`(`Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`, `Test`) VALUES ("'.$row[1].'","'.$row[2].'","'.$row[3].'","'.$row[4].'","'.$row[5].'","'.$row[6].'","'.$row[7].'","'.$row[8].'","'.$row[9].'","'.$row[10].'","'.$row[11].'","'.$row[12].'","'.$row[13].'","'.$row[14].'","'.$row[15].'","'.$row[16].'")');
		if($n>1){ */
				?>
					<tr>
					<td><?php echo $row[0];?></td>
					<td><?php echo $row[1];?></td>
					<td><?php echo $row[2];?></td>
					<td><?php echo $row[3];?></td>
					<td><?php echo $row[4];?></td>
					<td><?php echo $row[5];?></td>
					<td><?php echo $row[6];?></td>
					<td><?php echo $row[7];?></td>
					<td><?php echo $row[8];?></td>
					<td><?php echo $row[9];?></td>
					<td><?php echo $row[10];?></td>
					<td><?php echo $row[11];?></td>
					<td><?php echo $row[12];?></td>
					<td><?php echo $row[13];?></td>
					<td><?php echo $row[14];?></td>
					<td><?php echo $row[15];?></td>
					<td><?php echo $row[16];?></td>
					<td><?php echo $row[17];?></td>
					<td><?php echo $row[18];?></td>
					<td><?php echo $row[19];?></td>
					<td><?php echo $row[20];?></td>
					<td><?php echo $row[21];?></td>
					<td><?php echo $row[22];?></td>
					<td><?php echo $row[23];?></td>
					<td><?php echo $row[24];?></td>
					<td><?php echo $row[24];?></td>
					<td><?php echo $row[26];?></td>
					<td><?php echo $row[27];?></td>
					<td><?php echo $row[28];?></td>
					<td><?php echo $row[29];?></td>
					<td><?php echo $row[30];?></td>
					<td><?php echo $row[31];?></td>
					<td><?php echo $row[32];?></td>
					<td><?php echo $row[33];?></td>
					<td><?php echo $row[34];?></td>
					<td><?php echo $row[35];?></td>
				</tr>
				<?php
			//}
			//++;
		//}
		//fclose($handle); 
	?> 
	</tbody>
</table>
</body>
</html> 

Before I upload the file I get the following errors


Notice: Undefined variable: file in C:\xampp\htdocs\index.php on line 157

Warning: fgetcsv() expects parameter 1 to be resource, null given in C:\xampp\htdocs\index.php on line 157

Notice: Undefined variable: file in C:\xampp\htdocs\index.php on line 158

Warning: fgetcsv() expects parameter 1 to be resource, null given in C:\xampp\htdocs\index.php on line 158

Notice: Undefined variable: file in C:\xampp\htdocs\index.php on line 162

Warning: fclose() expects parameter 1 to be resource, null given in C:\xampp\htdocs\index.php on line 162

The uploaded file contains dates, time, blank fields, true and false and text.  I will need to prevent sql injection and html entities like ampersand sign.

After uploading the file, it only reads the first line of my csv file which contains the title of each column and nothing is put into phpmyadmin 

Link to comment
Share on other sites

You don't show which is line 157 but it is fairly obvious the problem is with $file and the fopen. Make sure you have error reporting  turned on at the beginning of your script:

ini_set('display_errors',1);
error_reporting(E_ALL);

 A better way to do what you want is:

if ($file = fopen("csv3.csv","r")) {
   echo "<pre>";
   print_r(fgetcsv($file));
   echo "</pre>";
}
else {
   print("Error opening file: ".error_get_last());
   

There are several other weird things in your script but this addresses your immediate problem. I'm guessing the error is "file not found" since the file will not be uploaded to the document root. You should be using $_FILES to get the uploaded file.

Link to comment
Share on other sites

4 hours ago, Capt1701a said:

Before I upload the file I get the following errors

Before you upload the file, $_POST["csv_upload_btn"] does not exist so the code

	 $file = fopen("csv3.csv","r")or die("Failed to read file");

is skipped because the if condition above it fails.

Since that code is skipped $file is never defined so this code

print_r(fgetcsv($file))== NULL;
for ($i = 0; $row = fgetcsv($file); ++$i) {

}

fclose($file);

is now referencing a variable that does not exist.

What you need to do is encase all your file processing code into a block that only runs when the file is uploaded and does not run when it has not been.

There are also many other issues in your code. 

print_r(fgetcsv($file))== NULL;

This code doesn't really make sense.   print_r is for debugging, which is fine if that's what you're doing but comparing it's result to NULL means nothing, especially since you're doing nothing with the result of that comparison.   So when you're debugging just call print_r with your variables, don't do a comparison too.

for ($i = 0; $row = fgetcsv($file); ++$i) {

}

This will loop through the rest of your rows of your file, but does nothing with them.  The loop body is empty.  Presumably your code to echo out all your table cells should be inside this loop, not after it.  You seem to have end of loop stuff commented out after the table cells so maybe you just made the loop empty for testing, but if you did that then you should be aware why it's only showing the first row (output of the print_r).

//create the table in the database
$sql = "CREATE TABLE IF NOT EXISTS`csvfile` (
  `Test` varchar(10) DEFAULT NULL,
  `Test` varchar(5) DEFAULT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
";

I don't know if this is how your code actually is or you changed it for this post to hide info, but you can't name all your columns 'Test'.  Column names need to be unique.

4 hours ago, Capt1701a said:

 I will need to prevent sql injection and html entities like ampersand sign.

Then you will need to use prepared queries along with parameter binding rather than concatenation as you currently have in your insert query.

 

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.

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