PHP/PostgreSQL database writing issue

Question

I'm having problems writing data from a form to multiple datatables on my PostgreSQL database.

Here is my data model

CREATE TABLE institutions(i_id PK, name text, memberofstaff REFERENCES staff u_id);

CREATE TABLE staff(u_id PK, username text, password text, institution REFERENCES institutions i_id);

So its a 1:1 relationship. These tables have been set up fine. It's the PHP script I'm having difficult with. I'm using CTE-datamodifying or at least trying to but I keep receiving errors on submit.

The PHP:

 $conn = pg_connect('database information filled out in code');

       $result = pg_query("WITH x AS (

       INSERT INTO staff(username, password, institution)

       VALUES('$username', '$password', nextval('institutions_i_id_seq'))

       RETURNING u_id, i_id)

       INSERT INTO institutions (i_id, name, memberofstaff)

       SELECT x.i_id, x.u_id, '$institution'

       FROM x");

       pg_close($conn);

So that's the code and the error I get is:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "institutions_i_id_seq" does not exist LINE 3: VALUES('AberLibrary01', '', nextval('institutions_i_id_se... ^ in DIRECTORY LISTING(I replaced this) on line 22

Anyone got any ideas?

araqnid · Accepted Answer · 2012-09-04 11:01:40Z

1

Did you actually create the table with the column types of institutions.i_id and staff.u_id set to serial? Or create the sequence manually?

If the former, you don't need to explicitly use nextval anyway. If the latter, double-check the sequence name.

answered Sep 4, 2012 at 11:01

araqnid

135k25 gold badges163 silver badges138 bronze badges

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

Comments

edparry · Accepted Answer · 2012-09-04 10:58:52Z

0

This would ideally be a comment, but I think it might have something to do with:

$'password' which should be '$password' on the fourth line of that example.

answered Sep 4, 2012 at 10:58

edparry

7281 gold badge10 silver badges34 bronze badges

1 Comment

WebDevDanno Over a year ago

sorry yeah that's just a type in this submit form not in the actual code, I did have to check though!

Collectives™ on Stack Overflow

PHP/PostgreSQL database writing issue

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related