Copy .txt file into Postgres using PHP/ PDO

Question

Im trying to enter a .txt file into a postgres database using the code below.

This sql query works when I enter it directly in the database using pgAdminIII but in the php code i get a syntax error:

PHP Parse error:  syntax error, unexpected T_STRING in D:\Apapub\public_html\databases\B13\upload_files.php on line 34

When I remove the ('') from around the text file like so:

$sql = 'COPY "public"."tblSedimentGrabEvent" FROM D:/Apapub/public_html/databases/B13/temp/grabevents.txt CSV';

I get a error from the sql side. any help will be appreciated, obviously I am new to the coding world.

Thanks

<?php
try { 
$db = new PDO("pgsql:dbname=NAME;host=HOST","USER","PASS");

}   
catch(PDOException $e) { echo $e->getMessage();
}

if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "<br>";
}
else
{
echo "Uploaded Successfully!<br>";
echo "File Name: " . $_FILES["file"]["name"] . "<br>";
echo "Type: " . $_FILES["file"]["type"] . "<br>";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
echo "Temp file: " . $_FILES["file"]["tmp_name"]. "<br>";

if (file_exists("upload/" . $_FILES["file"]["name"]))
  {
    echo $_FILES["file"]["name"] . " already exists. ";
  }
    else
  {
    move_uploaded_file($_FILES["file"]["tmp_name"],
    "D:/Apapub/public_html/databases/B13/temp/" . $_FILES["file"]["name"]);
    echo "Stored in: " . "D:\Apapub\public_html\databases\B13\temp" . $_FILES["file"]["name"]. "<br>";
  }

 }

$sql = 'COPY "public"."tblSedimentGrabEvent" FROM 'D:/Apapub/public_html/databases/B13/temp/grabevents.txt' CSV';

$resultC = $db->query($sql); 
if (!$resultC){
echo ('The query did not succeed<BR>');
echo ($sql);

}   

?>

ERROR: syntax error at or near "D" LINE 3: COPY "tblSedimentGrabEvent" FROM D:/Apapub/public_html/datab... ^ — ASan
– ASan, Commented May 10, 2013 at 20:10

peterm · Accepted Answer · 2013-05-10 20:20:54Z

2

The problem on php side is that you improperly use quotes. Try to change

$sql = 'COPY "public"."tblSedimentGrabEvent" FROM 'D:/Apapub/public_html/databases/B13/temp/grabevents.txt' CSV';
                                                  ^                                                       ^

to

$sql = 'COPY "public"."tblSedimentGrabEvent" FROM "D:/Apapub/public_html/databases/B13/temp/grabevents.txt" CSV';

or to

$sql = 'COPY "public"."tblSedimentGrabEvent" FROM \'D:/Apapub/public_html/databases/B13/temp/grabevents.txt\' CSV';

answered May 10, 2013 at 20:20

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ASan Over a year ago

ERROR: syntax error at or near ""D:/Apapub/public_html/databases/B13/temp/grabevents.txt"" LINE 3: COPY "tblSedimentGrabEvent" FROM "D:/Apapub/public_html/data... ^

ASan Over a year ago

Thank You. that last line worked, can you explain why the forward and back slash worked for future reference?

peterm Over a year ago

@AbelSantana You're welcome. Since your string is single quoted in order to have a literal single quote in your $sql variable you need to escape it with a backslash (). Thus two \'s around your file path. Read more here

Collectives™ on Stack Overflow

Copy .txt file into Postgres using PHP/ PDO

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related