473,322 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Why won't this code work in Postgresql and php?

I am running this SQL statement in php 4.39 to Postgresql 7.3.4 and I
keep getting this error

Warning: pg_query(): Query failed: ERROR: syntax error at or near
"WITHOUT" at character 29 in /var/www/html/liberty/includes/test2.php
on line 12

I can't figure out why this is failing.

$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?

Aug 9 '06 #1
6 1466
On Wed, 09 Aug 2006 16:53:37 -0700, rich wrote:
$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?
What is $tempname set to?

Adam

--
Adam Harvey

To e-mail: don't make an example out of me!

Aug 10 '06 #2

Adam Harvey wrote:
On Wed, 09 Aug 2006 16:53:37 -0700, rich wrote:
$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?

What is $tempname set to?

Adam

--
Adam Harvey

To e-mail: don't make an example out of me!
Adam I wrote a small function to get temporary table names.
Here it is. I know this peice works

function temptablename() {
$gettempnumQ = "SELECT * FROM li_temptables";
$gettempnumR = pg_query($gettempnumQ)or die("The query fail".
pg_error());
$tempnumA = pg_fetch_array($gettempnumR);
$tempnum = $tempnumA['tempnum'];
$newnum = $tempnum + 1;
$incrementQ="UPDATE li_temptables SET tempnum = '$newnum' where tempnum
= '$tempnum'";
$incrementR = pg_query($incrementQ) or die("The increment query
failed". pg_error());
$temptable = "li_temptable".$tempnum;
return $temptable;
}

$tempname = temptablename(); This is where $tempname comes from.

Aug 10 '06 #3
rich wrote:
I am running this SQL statement in php 4.39 to Postgresql 7.3.4 and I
keep getting this error

Warning: pg_query(): Query failed: ERROR: syntax error at or near
"WITHOUT" at character 29 in /var/www/html/liberty/includes/test2.php
on line 12

I can't figure out why this is failing.

$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?

Hi,

You misplaced the WITHOUT OIDS.
Read more here:
http://www.postgresql.org/docs/7.3/i...eatetable.html

Regards,
Erwin Moller

PS: What do you have against OIDs? :P
Aug 10 '06 #4

Erwin Moller wrote:
rich wrote:
I am running this SQL statement in php 4.39 to Postgresql 7.3.4 and I
keep getting this error

Warning: pg_query(): Query failed: ERROR: syntax error at or near
"WITHOUT" at character 29 in /var/www/html/liberty/includes/test2.php
on line 12

I can't figure out why this is failing.

$buildtableQ = "CREATE TABLE $tempname WITHOUT OIDS AS
SELECT rt.resid, max(rt.transdate)
FROM li_roomtransfer as rt
JOIN li_resident AS r ON rt.resid = r.resid
WHERE r.nursehomeid = '1'AND r.distribid = '1'
GROUP BY rt.resid";
$buildtableR = pg_query($buildtableQ); This is Line 12. any
suggestions?


Hi,

You misplaced the WITHOUT OIDS.
Read more here:
http://www.postgresql.org/docs/7.3/i...eatetable.html

Regards,
Erwin Moller

PS: What do you have against OIDs? :P
I have done some reading and I can't remember where that states if you
don't need to use OID's don't. There is a finite number of them within
the database and you can hit that limit. I know its a huge number, but
why use them? I usually have my primary keys on each of the tables I
design. Isn't that redundant?

Aug 10 '06 #5
"rich" <rh****@gmail.comwrote:
>
I have done some reading and I can't remember where that states if you
don't need to use OID's don't. There is a finite number of them within
the database and you can hit that limit. I know its a huge number, but
why use them?
4 billion. It takes one hell of a lot of INSERTs to get to 4 billion.
>I usually have my primary keys on each of the tables I design. Isn't
that redundant?
Usually.

(That's a little joke. If you "usually have primary keys", then OIDs will
usually be redundant.)
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Aug 12 '06 #6
Tim Roberts wrote:
"rich" <rh****@gmail.comwrote:
>>
I have done some reading and I can't remember where that states if you
don't need to use OID's don't. There is a finite number of them within
the database and you can hit that limit. I know its a huge number, but
why use them?

4 billion. It takes one hell of a lot of INSERTs to get to 4 billion.
>>I usually have my primary keys on each of the tables I design. Isn't
that redundant?

Usually.

(That's a little joke. If you "usually have primary keys", then OIDs will
usually be redundant.)
LOL, indeed. :-)
To OP: I was also joking when I asked what your problem was with OIDs.
You don't need them if you design your databases good (=all tables have
PK/serials and all references are enforced via Foreign Keys).

Regards,
Erwin Moller
Aug 14 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: J Dubal | last post by:
Hello good people, Following works in FC1 (python-2.2.3-7, postgresql-7.3.4-11, kernel-2.4.22-1.2194.nptl, pyPgSQL-2.4) from pyPgSQL import PgSQL conn =...
0
by: Bill J. | last post by:
I have to update a PostgreSQL linked server through MSSQL2K. I first configured the connection with ODBC as follows and I can do queries with no problem: EXEC sp_droplinkedsrvlogin @rmtsrvname...
1
by: Errol Neal | last post by:
Hi all, Not sure if this is a question for a php list or this one, but I'll give it a shot and if I am wrong, please do not crucify me. :-) There is a php based sourceforge project called...
1
by: Lynn.Tilby | last post by:
Hi, Please see the history below... Does AUTOCOMMIT as used here functionally replace VACUUM ANALYZE? If I turn on AUTOCOMMIT is every transaction commited as it is executed? If this is...
4
by: David Wheeler | last post by:
4.8-STABLE FreeBSD 4.8-STABLE i386 sahlins# cd postgresql-7.4.2/contrib/pg_autovacuum sahlins# make "../../src/Makefile.global", line 38: Missing dependency operator...
9
by: Andy B | last post by:
If I bought one of these boxes/OS combos as a postgresql database server, would postgresql be able to make the best use of it with a huge (e.g. 40GB) database? Box: HP ProLiant DL585, with ...
0
by: Együd Csaba | last post by:
> -----Original Message----- > From: pgsql-admin-owner@postgresql.org > On Behalf Of Együd Csaba > Sent: 2004. július 16. 12:23 > To: pgsql-admin (E-mail) > Subject: URGENT - Postgres won't...
3
by: Hunter Hillegas | last post by:
I have two varchars on my table... I tried to run the following: VMGEngine=# update user_account set editor_status = 'true' and administrator_status = 'true' where rec_num = 20; ERROR: ...
0
by: Greg Sabino Mullane | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 This is a PGP-signed copy of the checksums for following PostgreSQL versions: 7.4.5 7.4.4 7.3.7
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.