import CSV file into postgres via php in web page

Question

Hi I'm trying to build a web interface that will allow people to import the contents of a csv file into a postgresql database. Below is the php component of what I'm trying to do, however when I run I just get "There was a problem uploading your data:" from my die command but with no other error.

For this example, I have hardcoded a value for the csv file, however the intent is for the user to point at a csv file on their machine or network location.

<?php

if ($_POST['submit']) {
// attempt a connection
$dbh = pg_connect("host=localhost dbname=blah user=user password=pass123");
if (!$dbh) {
die("Error in connection: " . pg_last_error());
}
// execute query
$sql = "COPY personaldetails(name, email, postaladdress, phone, username, password) FROM '../Test/Book1.csv' WITH DELIMITER ',' CSV";

$result = pg_query($dbh, $sql);
if (!$result) {
die("There was a problem uploading you data: " . pg_last_error());
}

echo "Data successfully inserted!";

// free memory
pg_free_result($result);

// close connection
pg_close($dbh);
}   

?>

I appreciate any help that can be offered.

You might be able to adapt my answer here: stackoverflow.com/a/66482947/5075276 My solution works best with PDO but there is a way to adapt it using pg_connect, which I also give. The key is using either pg_copy_from (with pg_connect) or pgsqlCopyFromArray (with PDO); both are well-documented and don't require the sort of privileges that COPY FROM file requires, nor do they require PHP actually subject the file to more than a few commands of minimal processing. — cazort
– cazort, Commented Mar 4, 2021 at 21:02

Daniel Vérité · Accepted Answer · 2013-08-29 12:55:33Z

COPY FROM file must be run by a superuser account and this restriction makes it generally unsuitable for web usage

Excerpt from the documentation:

COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

But PHP provides support for COPY FROM stdin which does not have this restriction. This example from php documentation shows how it's done:

  $conn = pg_pconnect("dbname=foo");
  pg_query($conn, "create table bar (a int4, b char(16), d float8)");
  pg_query($conn, "copy bar from stdin");
  pg_put_line($conn, "3\thello world\t4.5\n");
  pg_put_line($conn, "4\tgoodbye world\t7.11\n");
  pg_put_line($conn, "\\.\n");
  pg_end_copy($conn);

In the case of a file, you'll need to open the file with php functions and feed it line by line to the postgres connection with pg_put_line()

Collectives™ on Stack Overflow

import CSV file into postgres via php in web page

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related