1

SO, I'm trying to insert some registers to PostgreSQL table using PHP.

I have a career table and registers are into school_careers.sql. So I've tried this:

$host = "localhost";
$user = "user";
$pass = "passw0";
$db = "school";

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
       or die ("Could not connect to server $server\n");

$query = "\\i ./school_classes.sql";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");

echo $rs . " done\n";

pg_close($con);

But I just get:

Cannot execute query: \i ./school_classes.sql

Perception
  • 79,279
  • 19
  • 185
  • 195
slackmart
  • 4,754
  • 3
  • 25
  • 39

2 Answers2

1

You're getting an error because pg_query expects a literal query, not a file name.

This post discusses loading and running sql from a file in PHP: Loading .sql files from within PHP

The accepted answer by Jeremy Privett seems to recommend that you "build out a PHP file that contains your queries in a variable [from which you] can just run them".

Community
  • 1
  • 1
Paul Bellora
  • 54,340
  • 18
  • 130
  • 181
  • Urk. Wouldn't you want to use file_get_contents() [http://www.php.net/manual/en/function.file-get-contents.php] to read your SQL file and pass that to Pg, rather than hand-wrapping your SQL in PHP? – Craig Ringer May 29 '12 at 03:35
  • @CraigRinger I don't know because I don't write PHP - just quoting what that accepted answer says. Feel free to edit my answer here. – Paul Bellora May 29 '12 at 04:53
  • I don't do PHP either, but programmatic file I/O is preferable to copy and paste in pretty much any language. Just searched for the easiest way to read a whole file into a string var in PHP. It'll run out of memory and crash for really big files, but so will the copy-into-a-php-file approach; you'd need to be smarter for really big SQL files. – Craig Ringer May 29 '12 at 06:36
  • @CraigRinger, Your comment appear be interesting. Can you post a full answer, please?. I've found a similar solution, so I want to compare it. – slackmart May 29 '12 at 15:14
  • 2
    @sgmart Sorry, I don't really do PHP most of the time and can't spare the time to hack together a demo at the moment. The general idea is simply to read data from a .sql file on disk into a php string var and feed that to pg_query(). Only challenge is that if the whole .sql doesn't fit in your PHP's memory limit you'll have to scan for statement delimeters and send it in chunks, which is hard because a ; doesn't delimit a statement if "quoted", 'quoted' or $$quoted$$. – Craig Ringer May 30 '12 at 02:19
  • @sgmart - You might just ask a new question, similar to the post I linked but for PostgreSQL. Make sure to sum up the situation and describe the solution(s) you've researched so far, asking for other solutions with advantages/disadvantages. Let me know if you decide to do this - I'd be interested in what answers you get. Sorry I couldn't help beyond pointing out the error - not a pure PHP guy either. – Paul Bellora May 30 '12 at 02:38
1

This doesn't work because "\i" is a psql client program command, not a valid query. There's no way for this command as-is to work in PHP as a pg_query. If you want to do it this way, you can invoke a shell and run this command:

psql -c "\i ./school_classes.sql" -U <username> -h <hostname> <dbname>

This has a drawback in that I do not believe that you can simply pass the password to the psql client, it will prompt for one. You can set up a .pgpass file containing the username/password pair that it can then use to connect without prompting (this is detailed in the PostgreSQL documentation as "libpq-pgpass").

Also, I'm not a PHP person so I don't know how to run a shell command, but it's a common enough operation that it should be in the documentation. You'll also need to determine how to capture the output from the shell command in order to validate that your script can correctly.

Matthew Wood
  • 16,017
  • 5
  • 46
  • 35
  • For the password, just use `putenv('PGPASSWORD='.$password);` and you're done. http://www.postgresql.org/docs/9.1/static/libpq-envars.html – Artefact2 May 30 '12 at 16:47
  • "it should be in the documentation" http://php.net/shell_exec http://php.net/popen – Artefact2 May 30 '12 at 16:49
  • Yeah, that works. Of course, the best solution is to either (a) break up the script and write it in PHP or (b) ditch PHP completely in this context, configure the local PostgreSQL instance to allow for logged-in local authorized users to run psql (and scripts) with peer/trust authentication, and run the .sql script directly. If it needs to go with a PHP script, wrap the psql step and the PHP step together in a shell script (since the .sql script looks like an initialization it may be able to be run independently). – Matthew Wood May 30 '12 at 16:53