looping to read a csv file

Capt1701a · August 26, 2020

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

gw1500se · August 26, 2020

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.

kicken · August 26, 2020

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.

Sign In

looping to read a csv file

Recommended Posts

Capt1701a

Link to comment

Share on other sites

gw1500se

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information