PHP PDO Import CSV file into MySQL Database

jmyrtle · January 28, 2020, 12:42pm

So the full code actually needs to be this:

<?php

include('dbconnect.php');
$file = fopen("demo.csv","r");


// Read and throw away the header row
fgetcsv($file);

$stmt = $pdo->prepare("INSERT INTO members (name, residential_address, mailing_address, precinct, age, ethnicity, gender, party, race, phone) VALUES (:name, :residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, :party, :race, :phone)");

while (! feof($file)) {
  $row = fgetcsv($file);
  $stmt->bindParam(':name', $row[0]);
  $stmt->bindParam(':residential_address', $row[1]);
  $stmt->bindParam(':mailing_address', $row[2]);
  $stmt->bindParam(':precinct', $row[3]);
  $stmt->bindParam(':age', $row[4]);
  $stmt->bindParam(':ethnicity', $row[5]);
  $stmt->bindParam(':gender', $row[6]);
  $stmt->bindParam(':party', $row[7]);
  $stmt->bindParam(':race', $row[8]);
  $stmt->bindParam(':phone', $row[9]);
  $stmt->execute();
  
}

fclose($file);

?>

right?

EDIT

I changed the code and tested it. The import works, but this happens in my database:

Why is a third record being made with no values in it? The csv only has 2 records…?

m_hutley · January 28, 2020, 2:50pm

Open your CSV file in a text editor.
Is there a return character on the end of line 2? Is there a line 3 with nothing on it?

jmyrtle · January 28, 2020, 2:51pm

Yes, there is.

m_hutley · January 28, 2020, 2:52pm

So, lesson of the day: Clean up your data

jmyrtle · January 28, 2020, 3:10pm

I’m currently working on that.

So now, since everything appears to be working, my next question is:

or do I need to do anything else first?

rpkamp · January 28, 2020, 4:42pm

You are correct. I though that was only MySQLi that worked that way, but PDO does the same thing. I personally always just pass an array to execute. Seems easiest.

Do you want to do this as a file upload or run it from the command line?

jmyrtle · January 28, 2020, 4:45pm

This is being used as a form, so it will have to be a file upload. Here is my code implemented with my form code:

<?php

include('dbconnect.php');

if(isset($_POST['btn_upload'])){
	$file = fopen("demo.csv","r");


	// Read and throw away the header row
	fgetcsv($file);

	$stmt = $pdo->prepare("INSERT INTO test_import (name, residential_address, mailing_address, precinct, age, ethnicity, gender, party, race, phone) VALUES (:name, :residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, :party, :race, :phone)");

	while (! feof($file)) {
  		$row = fgetcsv($file);
  		$stmt->bindParam(':name', $row[0]);
  		$stmt->bindParam(':residential_address', $row[1]);
  		$stmt->bindParam(':mailing_address', $row[2]);
  		$stmt->bindParam(':precinct', $row[3]);
  		$stmt->bindParam(':age', $row[4]);
  		$stmt->bindParam(':ethnicity', $row[5]);
  		$stmt->bindParam(':gender', $row[6]);
  		$stmt->bindParam(':party', $row[7]);
  		$stmt->bindParam(':race', $row[8]);
  		$stmt->bindParam(':phone', $row[9]);
  		$stmt->execute();
}

	fclose($file);
	header("Location: ../members_list.php");
}

if(isset($_POST['btn_back'])) {
	header("Location: ../members_list.php");
}

?>

rpkamp · January 28, 2020, 5:45pm

What does the form look like?

jmyrtle · January 28, 2020, 5:46pm

See my OP. If you need code:

<?php

include('nav/head.php');

?>

<!DOCTYPE html>
<html lang="en">

<head>

  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">

  <title>CCRP | Import Members List</title>
  
  <?php include('nav/header.php');  ?>

		<!-- ENTER CONTENT HERE -->
		
		<h1 class="h3 mb-2 text-gray-800">Import Members List</h1>
	<br>
	<form action="api/import.php" method="post">
		<div class="form-group">
			<input type="file" id="file" name="file" accept=".csv, .xlsx" autocomplete="off" />
		</div>				
			<input type="submit" name="btn_upload" class="btn btn-success" value="Upload">
			<input type="submit" name="btn_back" class="btn btn-danger" value="Return to Members List">							
	</form>


        </div>
        <!-- /.container-fluid -->

      </div>
      <!-- End of Main Content -->

	<?php include('nav/footer.php'); ?>

</html>

rpkamp · January 28, 2020, 6:27pm

For file uploads you need to set the enctype of the form.

I can recommend to dive into the manual on file uploads: https://www.php.net/manual/en/features.file-upload.php

jmyrtle · January 28, 2020, 8:54pm

I found this when looking up the enctype attribute on W3Schools:

I’ve coded the second one in place (see code below), but what do I encode in the API?

<form action="api/import.php" method="post" enctype="multipart/form-data">
		<div class="form-group">
			<input type="file" id="file" name="file" accept=".csv, .xlsx" autocomplete="off" />
		</div>				
			<input type="submit" name="btn_upload" class="btn btn-success" value="Upload">
			<input type="submit" name="btn_back" class="btn btn-danger" value="Return to Members List">							
	</form>

rpkamp · January 29, 2020, 7:30am

Did you have a look at the link to manual I posted earlier? There are a lot of examples with explanations in there.

jmyrtle · January 29, 2020, 11:31am

I remember seeing something like this in various example codes I looked at online:

However, when I try to use one, it feeds back an Undefined Index error.

Notice: Undefined index: userfile in C:\xampp\htdocs\ccrp\api\import.php on line 6

<?php

include('dbconnect.php');

if(isset($_POST['btn_upload'])){
	$filename = $_FILES['userfile']['name'];
	$file = fopen($filename,"r");


	// Read and throw away the header row
	fgetcsv($file);

	$stmt = $pdo->prepare("INSERT INTO test_import (last_name, first_name, middle_name, suffix, residential_address, mailing_address, precinct, age, ethnicity, gender, party, race, phone) VALUES (:last_name, :first_name, :middle_name, :suffix, :residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, :party, :race, :phone)");

	while (! feof($file)) {
  		$row = fgetcsv($file);
  		$stmt->bindParam(':last_name', $row[0]);
		$stmt->bindParam(':first_name', $row[1]);
		$stmt->bindParam(':middle_name', $row[2]);
		$stmt->bindParam(':suffix', $row[3]);
  		$stmt->bindParam(':residential_address', $row[4]);
  		$stmt->bindParam(':mailing_address', $row[5]);
  		$stmt->bindParam(':precinct', $row[6]);
  		$stmt->bindParam(':age', $row[7]);
  		$stmt->bindParam(':ethnicity', $row[8]);
  		$stmt->bindParam(':gender', $row[9]);
  		$stmt->bindParam(':party', $row[10]);
  		$stmt->bindParam(':race', $row[11]);
  		$stmt->bindParam(':phone', $row[12]);
  		$stmt->execute();
}

	fclose($file);
	header("Location: ../members_list.php");
}

if(isset($_POST['btn_back'])) {
	header("Location: ../members_list.php");
}

?>

m_hutley · January 29, 2020, 11:59am

<input type=“file” id=“file” name=“file” accept=“.csv, .xlsx” autocomplete=“off” />
$filename = $_FILES[‘userfile’][‘name’];

Channels inner Sesame Street
One of these things is not like the other~~~

jmyrtle · January 29, 2020, 12:13pm

Duh… silly me didn’t bother to change the index. Now it works!

But, does the file have to be uploaded from the server files or can I upload the file anywhere from my computer?

m_hutley · January 29, 2020, 12:17pm

A file input in a web form prompts the user to attach a file from their computer for uploading.

jmyrtle · January 29, 2020, 12:19pm

Then what does this mean?

Warning: fopen(demo.csv): failed to open stream: No such file or directory in C:\xampp\htdocs\ccrp\api\import.php on line 7

Warning: fgetcsv() expects parameter 1 to be resource, bool given in C:\xampp\htdocs\ccrp\api\import.php on line 11

Warning: feof() expects parameter 1 to be resource, bool given in C:\xampp\htdocs\ccrp\api\import.php on line 15

Warning: fgetcsv() expects parameter 1 to be resource, bool given in C:\xampp\htdocs\ccrp\api\import.php on line 16

I just tried uploading the same file outside of my project directory and these errors showed up? The last two errors (lines 15 & 16) repeat throughout the page and the page doesn’t stop loading

m_hutley · January 29, 2020, 12:21pm

Go back and look at the manual page that rpkamp linked you to.

Or even the image that you posted in post 73.

What is the difference between name and tmp_name, and why is your code failing?

jmyrtle · January 29, 2020, 12:35pm

Ah, I see what I did wrong. Thanks for pointing that out. It’s actually tmp_name, not name.

Everything appears to be working as needed now. Thanks everyone for helping me.

system · April 29, 2020, 7:35pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.