How do I enable php to work with postgresql?

Question 1

<?php

try {
   $dbh = new PDO('pgsql:host=localhost;port=5432;dbname=###;user=###;password=##');
   echo "PDO connection object created";
}
catch(PDOException $e)
{
      echo $e->getMessage();
}

?>

I get the error message "Could Not Load Driver"

Question 2

Try this:

Uncomment the following in php.ini by removing the ";"

;extension=php_pgsql.dll

Use the following code to connect to a postgresql database server:

pg_connect("host=localhost dbname=dbname user=username password=password")
    or die("Can't connect to database".pg_last_error());

Question 3

Thanks to this book

Note that the function presented here effectively by-passes security restrictions, which are there for a reason. Your function should check the file path and table provided against strict white list conditions. This example is also open to SQL injection as it does not quote its input correctly.

Create function which execute `COPY` command

CREATE OR REPLACE FUNCTION copy_from_csv_ignoring_security(table_name text, table_fieds text, file_path text, oids boolean DEFAULT false, header boolean DEFAULT false, delimeter text DEFAULT ','::text, "null" text DEFAULT ''::text, quote text DEFAULT '"'::text, escape text DEFAULT '"'::text, force_not_null text DEFAULT ''::text)
  RETURNS void AS
$BODY$

declare statement text;
begin

statement := 'COPY ' || table_name || ' (' || table_fieds || ') ' || 'FROM ''' || file_path || ''' WITH ';
IF oids THEN
 statement := statement || 'OIDS ';
end if;
statement := statement || 'DELIMITER ''' || delimeter || ''' ';
statement := statement || 'NULL ''' || "null" || ''' CSV ';
IF header THEN
 statement := statement || 'HEADER ';
end if;
statement := statement || 'QUOTE ''' || "quote" || ''' ';
statement := statement || 'ESCAPE ''' || "escape" || ''' ';
IF force_not_null <> '' THEN
statement := statement || 'FORCE NOT NULL ''' || force_not_null || ''' ';
end if;
execute statement;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

Give rights on function

revoke all on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) from public;
grant execute on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) to db_user;

Execute from PHP

$dbh->exec('SELECT copy_from_csv_ignoring_security(...)');

===== If version >= 9.1.7 trick above doesn't works. =====

Solution:

create file .pgpass (avoid password prompt) in home directory of user which run this script.

#.pgpass contents (chmod 600 - requred)    
host:port:db_name:user_name:password

create php function, which executes meta-command

function executeMetaCommand($dbUser, $dbName, $dbPort, $command)
{    
    $command = sprintf(
        "psql -U %s -p %s -d %s -f - <<EOTn%snEOTn",
        $dbUser, $dbPort, $dbName, $command
    );
    $streams = array(
        array('pipe', 'r'),// stdin
        array('pipe', 'w'),// stdout
        array('pipe', 'w') // stderr
    );
    $process = proc_open($command, $streams, $pipes);
    if (!is_resource($process)) {
        throw new Exception("Cannot open process:n$command");
    } else {
        list(, $stdout, $stderr) = $pipes;
        $error = stream_get_contents($stderr);
        fclose($stderr);
        if (strlen($error) > 0) {
            throw new Exception("Process error:n$error");
        } else {
            $output = stream_get_contents($stdout);
            fclose($stdout);
            $returnCode = proc_close($process);
            if ($returnCode === -1) {
                throw new Exception("Process was completed incorrectly:n$output");
            } else {
                return array(
                    $returnCode,
                    $output
                );
            }
        }
    }
}

//usage:
$command = sprintf("\copy table(field1, field2) FROM '%s' WITH CSV", $filePath);
executeMetaCommand('postgres', 'test_db', '5432', $command);

Question 4

I had a similar situation today.

My solution is extremely simple, but is just smart enough to allow for comments and statements that span multiple lines.

// open script file

$scriptfile = fopen($script_path, "r");
if (!$scriptfile) { die("ERROR: Couldn't open {$scriptfile}.n"); }

// grab each line of file, skipping comments and blank lines

$script = '';
while (($line = fgets($scriptfile)) !== false) {
    $line = trim($line);
    if(preg_match("/^#|^--|^$/", $line)){ continue; } 
    $script .= $line;
}

// explode script by semicolon and run each statement

$statements = explode(';', $script);
foreach($statements as $sql){
    if($sql === '') { continue; }
    $query = $pdo->prepare($sql);
    $query->execute();
    if($query->errorCode() !== '00000'){ die("ERROR: SQL error code: ".$query->errorCode()."n"); }
}

Question 5

I found the solution: How do I install MySQL modules within PHP?

Question 6

These helped me out when I was having trouble.

http://www.danieltmurphy.com/setting-up-mercury-smtp/

http://www.youtube.com/watch?v=VU4PT7xMSO0

Mercury can be activated from the Xampp control panel.

How do I enable php to work with postgresql?

Answers

Create function which execute `COPY` command

Give rights on function

Execute from PHP

===== If version >= 9.1.7 trick above doesn't works. =====

php,windows,postgresql,pdo

How do I enable php to work with postgresql?

Answers

Create function which execute COPY command

Give rights on function

Execute from PHP

===== If version >= 9.1.7 trick above doesn't works. =====

php,windows,postgresql,pdo

Create function which execute `COPY` command