How to import a csv with copy function using php

Question

I need to import a csv file into my database. I first wrote the function in SQL but now I need to do it in php, I don't want to change my entire code so I just figured I could execute the SQL query with php but I get an error.

    $query="COPY bet_import FROM $1 DELIMITER ',' CSV HEADER";
    $result = pg_prepare($dbh, "", $query);
    $result = pg_execute($dbh, "",array('bet.csv'));

I get the error:

Error: Warning: pg_prepare(): Query failed: ERROR: syntax error at or near "$1" LINE 1: COPY bet_import FROM $1 DELIMITER ',' CSV HEADER

Ashok Gadri · Accepted Answer · 2019-07-23 06:37:56Z

0

You cannot bind identifiers (table name, field name) in the prepare statement. You need to add that into the query. If you are dynamically getting it you can escape it or sanitize it before you run the query. In your query the file path is an identifier.

$filePath = 'bet.csv';
$query="COPY bet_import FROM '$filePath' DELIMITER ',' CSV HEADER";
$result = pg_prepare($dbh, "", $query);
$result = pg_execute($dbh, "",array());

You have to pass the filename in the query itself. This query will run on the postgresql so you need to specify the absolute path of the file.. If this file is placed in same code as your php code then also you need to specify the absolute path.

edited Jul 23, 2019 at 6:37

answered Jul 22, 2019 at 18:03

Ashok Gadri

5184 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

How to import a csv with copy function using php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related