Home>
[postgreSQL] I want to output DB data to CSV with PHP

I want to convert all the table data in the database to a CSV file using postgreSQL in the PHP file.
If i am familiar with postgreSQL, please let me know.

Applicable source code
<? php
    // connect to PostgreSQL
    $conn = pg_connect ('host = localhost dbname = test
    user = XXXXX password = XXXXX ');
    // execute SQL statement
    $query = "COPY test_json TO '/tmp/sampletbl1.csv' CSV";
    pg_query ($conn, $query);
    // Disconnect PostgreSQL
    $close = pg_close ($conn);
    ?>
Supplemental information

・ /tmp/sampletbl1.csv is not created.
・ MAMP is used.
・ DB connection is successful.
-No PHP error has occurred.
・ PHP version: 5.6.30
・ PostgreSQL version: 10.5
・ MAMP version: 5.0.1

・ About the database
Host name: localhost
DB name: test
Table name I want to get: test_json
CSV file name after acquisition: sampletbl1.csv

  • Answer # 1

    Can I run sql with psql?
    Me,

    copy table name to '/ path/file name.csv' with csv;


    It couldn't be executed without it ...
    The path must have write permission for the postgres user.

  • Answer # 2

    '/tmp/sampletbl1.csv'has access rights to the file
    Whether it is in the web server program itself
    Please confirm.

    And
    When the COPY statement is executed with the privileges of the db connection user,
    If client authentication that associates db connected users with users on the OS is used,
    It is better to check the authority of who the OS user is.

    Is Apache httpd for MAMP?

    About the code.

    // execute SQL statement
        $query = "COPY test_json TO '/tmp/sampletbl1.csv' CSV";
        $result = pg_query ($conn, $query);
        if ($result === FALSE) {
          echo "An error occurred. \ n";
          echo pg_last_error ($conn). "\ n";
          exit;
        }


    If it does, it may be displayed if it is an error in PostgreSQL access instead of on php.

  • Answer # 3

    It may not lead to a direct answer, but it uses the if statement to determine $query, not the return value of pg_query.
    It is not possible to judge whether the query result is correct.