PHP PDO Import CSV file into MySQL Database

Is there anything I need to do after the execution or anything else I need to add to the while loop? My current while loop looks like this:

while (! feof($file)) {
  print_r(fgetcsv($file));
  $stmt->execute();
  
}

Also, the $file variable shows this:

$file = fopen("demo.csv","r");

How do I allow the user to upload a .csv file with another name? I’d like to have a variable that determines what the filename is before the file is opened.

$filename = (code here)
$file = fopen($filename, "r");

Can you post the full code please?

<?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)");
$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]);

while (! feof($file)) {
  fgetcsv($file);
  $stmt->execute();
  
}

fclose($file);

?>

Three questions:

  1. Do I need to add anything else?
  2. Do I need to change anything?
  3. How do I make a variable for the file name rather than just having it stay as demo.csv?

The binding of variables should be done within the while loop. Please read back a few posts to the approach I posted there.

Stop worrying about the filename for now. We can tackle it once the rest works. Solve two problems at a time you’ll get poor solutions for both.

I didn’t think that was necessarily the case. I was under the impression that you can prepare() and bindParam() (but not bindValue()) before the loop, and calling execute() inside the loop will use the current value of the bound variables. I do that in some places, but I do make sure that the variables exist, even with null values, at the point that I call bindParam - I’ve never tested to see whether that’s required or not.

As I see it, the problem is that this line:

fgetcsv($file);

should read

$row = fgetcsv($file);

to populate the array prior to calling execute().

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…?

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?

1 Like

Yes, there is.

So, lesson of the day: Clean up your data :wink:

I’m currently working on that. :wink:

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

or do I need to do anything else first?

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?

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");
}

?>

What does the form look like?

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>

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

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>

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

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");
}

?>

<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~~~

1 Like

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?