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

Improve this question

2 Answers 2

Reset to default
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".

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

6 Comments

Urk. Wouldn't you want to use file_get_contents() [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?
@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.
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.
@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.
@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$$.
|
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.

Improve this answer

3 Comments

For the password, just use putenv('PGPASSWORD='.$password); and you're done. postgresql.org/docs/9.1/static/libpq-envars.html
"it should be in the documentation" php.net/shell_exec php.net/popen
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.