[PHP] PHP + PostgreSQL: pg_query not returning the expected result for this (edge) case:
Here's a tricky one:
Versions: I'm using PostgreSQL 9.0.7 and PHP 5.3.8.
First, I have a table bob with two columns: bobid (integer) and bobtext
(text). The bobid field defaults to a sequence called bob_seq which is just
a simple counter (1,2,3 etc.)
I can do an INSERT query into bob:
INSERT INTO bob (bobtext) VALUES ('Hello Bob') RETURNING bobid;
and get the value the sequence has given me for bobid: this works fine using
both the psql tool and pg_query in PHP.
I can also do an UPDATE query in the same vein:
UPDATE bob SET bobtext='Hello Bob revisited' WHERE bobid=x RETURNING bobid;
where x is a valid bobid value for an existing record in the table. This
returns the value of bobid for the updated record which should be the same as
x and again it works in the psql tool and in pg_query.
Next, I have a VIEW based on bob, called fred. It is simply defined as
CREATE VIEW fred AS (
SELECT * FROM bob
);
I have two rules on fred which allow me to alter the view.
The first is used for INSERT into fred, which translates them into INSERT into
bob:
CREATE OR REPLACE RULE fred_insert AS
ON INSERT TO fred
DO INSTEAD
INSERT INTO bob (bobtext) VALUES (NEW.bobtext) RETURNING *
;
The second is used for UPDATE on fred, which translates to creating a new
record in bob and deleting the old record:
CREATE OR REPLACE RULE fred_update AS
ON UPDATE TO fred
DO INSTEAD (
INSERT INTO bob (bobtext) VALUES (NEW.bobtext) RETURNING *;
DELETE FROM bob WHERE bobid=OLD.bobid;
)
;
Note that these rules both return all of the fields of the newly inserted bob
records back up the chain to be accessible to the original query.
Now, I can insert into fred knowing that the rule will handle it:
INSERT INTO fred (bobtext) VALUES ('Hello Fred') RETURNING bobid;
and that should return the new bobid value of the inserted record (from the
bob_seq sequence). It still works in the psql tool and in pg_query.
I should also be able to exercise the update rule on fred:
UPDATE fred SET bobtext='Hello Fred revisited' WHERE bobid=x RETURNING bobid
where x is a valid bobid value for an existing record in the table.
This should insert a new row with a new bobid from the sequence and the new
bobtext value, delete the row with bobid=x, and return the bobid for the
new row.
Here's the punchline (at last):
It works in the psql tool.
It doesn't work in pg_query.
?php
$result = pg_query(UPDATE fred SET bobtext='Hello Fred revisited' WHERE
bobid=42 RETURNING bobid); // Assume there is a record with bobid==42
if ($result pg_numrows($result))
{
echo pg_numrows($result).PHP_EOL;
echo pg_fetch_result($result, 0, 0).PHP_EOL;
)
?
Tells me there that although the $result resource is valid, there are no rows
and therefore no result to fetch (PHP Warning: pg_fetch_result(): Unable to
jump to row 0 on PostgreSQL result ...)
So: why is the return from the UPDATE rule different to the return from the
INSERT rule in PHP pgsql?
Thanks for taking the time...
Cheers
Pete
--
Peter Ford, Developer phone: 01580 89 fax: 01580 893399
Justcroft International Ltd. www.justcroft.com
Justcroft House, High Street, Staplehurst, Kent TN12 0AH United Kingdom
Registered in England and Wales: 2297906
Registered office: Stag Gates House, 63/64 The Avenue, Southampton SO17 1XS
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
The query was: $query = 'SELECT * FROM databasetablename'; So, database access seems to be the problem. Using the superuser account 'postgres', a user 'httpd' was created and all privileges were granted to the target database using the postgresql 'grant' command. However the user 'httpd' is not the owner of the database so perhaps that is the problem, although if this user could not access the database that would cause an error in the log (but no such error is seen)? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
On 30/04/2011, Daniel Brown danbr...@php.net wrote:
Readers? Sounds like you spend too much time writing newsletters
(to the wrong address, since php-general-digest-h...@lists.php.net is
a self-help command list for digest-form subscriptions). ;-P
On Sat, Apr 30, 2011 at 04:41, e-letter inp...@gmail.com wrote:
?php
$db = pg_connect('dbname=databasename user=username');
$query = 'SELECT * FROM databasename';
$value=pg_fetch_result($query,1,0);
echo 'export of database is ',$value,'';
?
p
why does this fail?
How is it failing? What error(s) are you seeing on screen or in
your log files? Noting that $value would contain an array, is that
the problem? And why are you using ending quotes in your echo? You
should just place the semicolon immediately after $value.
I looked at the error file located at '/var/log/httpd/error_log',
which identifies an error:
...Apache/2.2.6 (Mandriva Linux/PREFORK-8.2mdv2008.0) PHP/5.2.4 with
Suhosin-Patch mod_put/2.0.8 configured -- resuming normal
operations...
...PHP Parse error: syntax error, unexpected T_VARIABLE, expecting
',' or ';'...
The file was changed as follows which caused the parse error shown above:
?php
$db = pg_connect('dbname=webcuttings user=httpd');
$query = 'SELECT * FROM articles';
$value=pg_fetch_result($query);
echo 'all files' $value;
?
The file was copied from the manual page, without understanding that
an array was being used.
/p
/body
/html
The following php code produces the user agent:
?php
echo '$_SERVER['HTTP_USER_AGENT']';
?
First of all, no it doesn't. Placed inside single quotes, it'll
not only try to return it verbatim (i.e. - the variable would be
printed to screen), but it'll also cause a parse error, as you reuse
single quotes in the variable key container.
My mistake; with the command:
?php
echo $_SERVER['HTTP_USER_AGENT'];
?
the result is:
Opera/9.80 (X11; Linux i686; U; en-GB) Presto/2.6.30 Version/10.61
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
On Sun, 2011-05-01 at 09:24 +0100, e-letter wrote:
I looked at the error file located at '/var/log/httpd/error_log',
which identifies an error:
...Apache/2.2.6 (Mandriva Linux/PREFORK-8.2mdv2008.0) PHP/5.2.4 with
Suhosin-Patch mod_put/2.0.8 configured -- resuming normal
operations...
...PHP Parse error: syntax error, unexpected T_VARIABLE, expecting
',' or ';'...
The file was changed as follows which caused the parse error shown
above:
?php
$db = pg_connect('dbname=webcuttings user=httpd');
$query = 'SELECT * FROM articles';
$value=pg_fetch_result($query);
echo 'all files' $value;
?
The file was copied from the manual page, without understanding that
an array was being used.
The problem you've got there is a missing string concatenator in your
echo line. You should change that line to read:
echo 'all files' . $value;
note the . character? However, as you said, $value is actually an array,
so you would be better of using something like print_r() or var_dump()
on it.
--
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] postgresql database access failure
The file was changed: ... $value=pg_fetch_result($query,1,1); echo 'all files' . var_dump($value); ... The resultant web page produces: bool(false) all files The php file was changed again: ... $value=pg_fetch_result($query); echo 'all files' . var_dump($value); ... The resultant web page produces: NULL all files The error log shows: ...PHP Warning: pg_fetch_result(): supplied argument is not a valid PostgreSQL result resource... The objective is to learn how to extract data from a database and print to a web browser, but not much progress made so far..! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
e-letter wrote: The file was changed: ... $value=pg_fetch_result($query,1,1); echo 'all files' . var_dump($value); ... The resultant web page produces: bool(false) all files The php file was changed again: ... $value=pg_fetch_result($query); echo 'all files' . var_dump($value); ... The resultant web page produces: NULL all files The error log shows: ...PHP Warning: pg_fetch_result(): supplied argument is not a valid PostgreSQL result resource... The objective is to learn how to extract data from a database and print to a web browser, but not much progress made so far..! There is a good example of how to use pg_fetch_result in the docs at http://php.net/manual/en/function.pg-fetch-result.php. On the basis of the code shown here, it's a bit hard to determine exactly what your problem is; however the odds are that the error supplied argument is not a valid PostgreSQL result resource results from a SQL syntax error, or possibly that you have failed to open a connection to pgsql. However, there are some tools to help you; see http://php.net/manual/en/function.pg-result-error.php For future reference, it helps to post all the code that is relevant to your problem, so in this case it would help, for example, to see how you are making the connection to pgsql and how the $query variable is populated. Cheers -- David Robley A seminar on Time Travel will be held two weeks ago. Today is Boomtime, the 49th day of Discord in the YOLD 3177. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] postgresql database access failure
Readers,
A postgresql database (local disk installation) is successfully
accessed as a normal user:
psql -U username databasename
However, creating a php file to access the database has not been successful.
html
body
?php
$db = pg_connect('dbname=databasename user=username');
$query = 'SELECT * FROM databasename';
$value=pg_fetch_result($query,1,0);
echo 'export of database is ',$value,'';
?
p
why does this fail?
/p
/body
/html
The following php code produces the user agent:
?php
echo '$_SERVER['HTTP_USER_AGENT']';
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
Readers? Sounds like you spend too much time writing newsletters
(to the wrong address, since php-general-digest-h...@lists.php.net is
a self-help command list for digest-form subscriptions). ;-P
On Sat, Apr 30, 2011 at 04:41, e-letter inp...@gmail.com wrote:
?php
$db = pg_connect('dbname=databasename user=username');
$query = 'SELECT * FROM databasename';
$value=pg_fetch_result($query,1,0);
echo 'export of database is ',$value,'';
?
p
why does this fail?
How is it failing? What error(s) are you seeing on screen or in
your log files? Noting that $value would contain an array, is that
the problem? And why are you using ending quotes in your echo? You
should just place the semicolon immediately after $value.
/p
/body
/html
The following php code produces the user agent:
?php
echo '$_SERVER['HTTP_USER_AGENT']';
?
First of all, no it doesn't. Placed inside single quotes, it'll
not only try to return it verbatim (i.e. - the variable would be
printed to screen), but it'll also cause a parse error, as you reuse
single quotes in the variable key container.
--
/Daniel P. Brown
Network Infrastructure Manager
http://www.php.net/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
On Sat, Apr 30, 2011 at 12:23, Daniel Brown danbr...@php.net wrote: ?php echo '$_SERVER['HTTP_USER_AGENT']'; ? First of all, no it doesn't. Placed inside single quotes, it'll not only try to return it verbatim (i.e. - the variable would be printed to screen), but it'll also cause a parse error, as you reuse single quotes in the variable key container. Forgot the second of all before hitting send. Second of all, what does this have to do with your PostgreSQL problem? Did I miss something? -- /Daniel P. Brown Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL and select nextval
Alain Roger wrote:
Hi,
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2: VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
You don't use SELECT, just put nextval(...)
INSERT INTO tmp_importedxls (rec_id, publisher) VALUES
(nextval('tmp_importedxls_rec_id_seq'),'$pb')
Luck!
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL and select nextval
Richard Lynch wrote:
On Sun, August 12, 2007 2:35 am, Alain Roger wrote:
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2:
VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
I have the feeling that we can not use the select nextval(...) SQL
request
in an INSERT INTO one under PHP.
Is it true?
No.
PHP doesn't care diddly-squat what is in your query -- It just sends
it to PostgreSQL.
The query you have written just plain won't work in PostgreSQL, period.
Try it in the psql monitor.
OT:
Almost for sure, you just need to strip out the VALUES ( bit and the
closing paren for it.
Nop. If you don't put the VALUES ( you are passing a query with
applyable values for the table in the INSERT.
He only wants the next value in the sequence. In that case he just has
to not put the SELECT.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL and select nextval
On Sun, August 12, 2007 2:35 am, Alain Roger wrote:
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2:
VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
I have the feeling that we can not use the select nextval(...) SQL
request
in an INSERT INTO one under PHP.
Is it true?
No.
PHP doesn't care diddly-squat what is in your query -- It just sends
it to PostgreSQL.
The query you have written just plain won't work in PostgreSQL, period.
Try it in the psql monitor.
OT:
Almost for sure, you just need to strip out the VALUES ( bit and the
closing paren for it.
--
Some people have a gift link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PostgreSQL and select nextval
Hi,
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2: VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
I have the feeling that we can not use the select nextval(...) SQL request
in an INSERT INTO one under PHP.
Is it true?
thanks
--
Alain
Windows XP SP2
PostgreSQL 8.2.3
Apache 2.2.4
PHP 5.2.3
Re: [PHP] PostgreSQL and select nextval
Alain Roger wrote:
Hi,
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2: VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
I have the feeling that we can not use the select nextval(...) SQL request
in an INSERT INTO one under PHP.
Is it true?
If rec_id is a SERIAL it will increment itself:
INSERT INTO tmp_importedxls (publisher) VALUES ('$pb');
brian
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] postgreSQL and bytea / image
Hi,
In my PHP page i upload pictures into database. For that i wrote a simple
function which control which user is logged and will upload his picture.
here is the function :
CREATE OR REPLACE FUNCTION sp_a_006(login character varying, photo bytea)
RETURNS boolean AS
$BODY$
DECLARE
my_idINTEGER :=0;
BEGIN
select into my_id
account_id from accounts where account_login = $1;
IF (my_id != 0) THEN
UPDATE users
SET user_photo = $2
WHERE user_account_id = my_id;
RETURN (TRUE);
ELSE
RETURN (FALSE);
end if;
END;
when i call this function in PHP, i do the following :
$my_query = select * from immense.sp_a_006
('.$_SESSION[username].','{$escaped}');
$res_pic = pg_query(my_query);
where $escaped = pg_escape_bytea($data);
this inserts the picture only if i add E in front of '{$escaped}' and
becomes E'{$escaped}').
why ?
on another website i do not use function but a simple SQL query as following
and it works :
pg_query(INSERT INTO photo (photo_id,document_orientation_id, photo_date,
photo_image)
VALUES
(nextval('photo_photo_id_seq'),.$orientation_id[0].,NOW(),
'{$escaped}'));
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
Re: [PHP] postgreSQl and images
On Mon, November 6, 2006 5:17 pm, Børge Holen wrote: aaight... I get yer point there, BUT you see, what do you do when an artists changes it name... forget it, that was a bad choice... Oh no, it's a GREAT choice. First of all, if they really really changed their name, then I create a new record for them, and they have 2 Artist Profiles, so I can maintain historical accuracy. E.g., if Prince had played our venue every year, I'd want the shows in 1993-- to be 'Prince' and then from 1993 to 2000 to be 'tafkap' and then I'd be using the same pre-1993 record again from 2000 onwards. (Assuming his name does not change again.) I have done this many many many times for artist who marry, rename their bands, have side projects or perform solo as well as with their band, or suffer some other identity crisis. If you've worked with musicians, you would know that all of the above are not uncommon experiences. :-) If it's just a typo in an artist name, I just fix it. But the artist name is the ONE field we simply do not let the artist have access to. They'd end up destroying the historical accuracy of our web calendar listing their gigs back to 2003 (and earlier, if I ever get the owner to drag in his dead-tree bookings calendars so I can input them...) And since the ID3 tag comes from the DB in real-time, pre-pended to the mp3 stream with the static audio portion of the mp3 following, the actual output MP3 stream is corrected for the artist name as soon as I fix the typo. PHP ID3 rocks. :-) Actually, if you download the same MP3 file 2 successive times from my server, and if the artist has multiple images, you could trivially prove that I change the stream on the fly, as I choose the image at random. I think Rachel Sage has the most images in there, so that would be a good choice, if she has turned on downloads in her account. That's why only a tiny fraction of the 65000 mp3s are available. Artists have to login, choose the tracks and their usage parameters (streaming, download, radio, hifi, lofi, cafe speaker system) and they have to sign a release form. [aside] The release form is the worst possible non-contract I could manage to draft, on purpose, to avoid locking the artist into anything, while keeping the cafe lawyer happy -- Dang thing doesn't even have a DATE on it, much less read as an enforcable contract. So your version of the MP3 and mine will not match on our browser, wrt the ID3 meta-data being different images. The static audio portion will remain the same, of course. anyway... you see, in one of my fields of interests, you got dogs... see, dogs can change name, not just the calling name, but I mean completely change it all. second, they change apperance with growth. So to keep track on stuff you need to make a system work for you, not the other way around... Hence, all in the db... but no way someone would be * enought to put ... you know what.. In that case, I'd suggest a series of related records for each dog tied back to some unique ID. If there is an industry-standard ID for a dog that never changes throughout the course of its life, tie that into the central ID, or just make one up and convince users to tie their records together correctly through the business logic. I don't even attempt to map out which artists are really the same as which other artists, as the can of worms and social issues this would open up are legion. Prince/tafka looks a mere piker in this regard compared to some of the data I'd have to deal with... I'll pick a funny story of an artist, just as an example... Ken Vandermark was interviewed a couple years ago, after winning the McArthur Foundation genius grant (sic) and they got to talking about all the bands he was in. He's a free/improv jazz artist, plays mostly saxophone. Anyway, the interviewer asked him just how many bands he was in. Ken's answer was something not unlike this: I don't know. You'd have to track down all the bands I ever played in, and ask them [shrug]. If we played together every 2 or 3 years for awhile, but it's been 5 years now since we've been together, are we still a band? I dunno. I guess it just depends on whether we make plans to play together again or not. Call it about 500 bands, give or take. Now Ken's an extreme example, but these and similar issues turn out to be pretty non-trivial, once you start digging into it. NOone would EVER put a single mp3 file in a db ;) lol. There we agree. I don't know that nobody would ever put a single MP3 in a db. They might have a zillion teeny tiny MP3 files that they can more efficiently serve up from the DB, and have the research and stats to prove that it's better on their system. But if you have to ask the question about this matter, I'd say putting the data in your file-system is the Right Answer. :-) -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I
Re: [PHP] postgreSQl and images
On Sat, November 4, 2006 11:26 am, Alain Roger wrote: I create a table with some large object (ref: OID) to store some images. When my PHP will display some data, it will also display the images stored as OID. However, i've read that before i must restore the image by exporting them to local (on server) file. isn't it easier in this case, to simply store the path and file name of file to DB and just read the data to display image on PHP pages ? what is the purpose in this case to store image a bytea / large object ? moreover, how my php code will load image from DB and stored there as OID ? isn't it too much complex in comparison with just storing path and name of image ? Yes, it will probably be easier all around to de-clutter your DB and just store the images on the file system, which is itself a highly-tuned mass-storage specialized database system. :-) You would *NOT* need to store the file temporarily on the hard drive: ?php $image_data = some_function_to_get_image_date_from_OID($OID); header(Content-type: image/jpeg); echo $image_data; ? No temp file needed. This DB versus file system for images has been debated on this list before. Check the archives. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgreSQl and images
On Sat, November 4, 2006 5:38 pm, Børge Holen wrote: either you end up with a had as method of grouping them together, moreover you can have thousands of small files inside one dir with an id name to it, and yes the last one, thousands of directories with one file inside... Speaking as a guy who has 65,000+ mp3s on-line (though only a fraction of them available to the general public) I'd go crazy if they were all in the DB, not to mention that my webhost would kill me... But I'm not dumb enough to put them all in one directory either. foobar.mp3 goes in /f/o/foobar.mp3 Actually, which *drive* it is on is determined by the date the audio was recorded, as I have about a Terabyte available spread across 4 cheap IDE drives, and just change a simple include file when one of the drives is nearly full to start using up the next one. So it really turnes into one of these: drive1/f/o/foobar.mp3 drive2/f/o/foobar.mp3 drive3/f/o/foobar.mp3 . . . based on what date the live performance occurred, which is in my meta-data, which I need to get for the on-the-fly ID3 tags anyway. [*] It's all very crude and shoestring budget, but it works. Well, except until they turned off the A/C in the office last summer, and then spilled coffee grounds all over the box... :-( But I found an old computer in the closet that I had found in a dumpster and threw the hard drives in that, and it works. 495 MHz seems enough for my audio server. :-) I'm finishing up a process of copying the files to a real host so I'll have a crude 2-tier fail-over. Anyway, you have to plan this out with some idea of what scale and scope you are dealing with to avoid insanity, but cramming binary data into the DB seems like the least attractive choice to me personally. [*] It's a shame the MP3 players all seem to ignore my nifty JPEG ID3 data I'm pre-pending to the audio streams. You have to download the files just to see the artist photo. Sigh. PS Feel free to give a listen if you like accoustic music: http://uncommonground.com/radio_hifi.m3u http://uncommonground.com/radio_lofi.m3u (hifi, lofi, respectively, obviously) iTunes Podcast version is in beta if you're interested in being a beta-tester... Same audio, just in RSS/XML format. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgreSQl and images
aaight... I get yer point there, BUT you see, what do you do when an artists changes it name... forget it, that was a bad choice... anyway... you see, in one of my fields of interests, you got dogs... see, dogs can change name, not just the calling name, but I mean completely change it all. second, they change apperance with growth. So to keep track on stuff you need to make a system work for you, not the other way around... Hence, all in the db... but no way someone would be * enought to put ... you know what.. NOone would EVER put a single mp3 file in a db ;) lol. There we agree. My problem originated with the planning... or rather lack of. Now? I think it works flawlessly so why fix it On Monday 06 November 2006 23:00, Richard Lynch wrote: On Sat, November 4, 2006 5:38 pm, Børge Holen wrote: either you end up with a had as method of grouping them together, moreover you can have thousands of small files inside one dir with an id name to it, and yes the last one, thousands of directories with one file inside... Speaking as a guy who has 65,000+ mp3s on-line (though only a fraction of them available to the general public) I'd go crazy if they were all in the DB, not to mention that my webhost would kill me... But I'm not dumb enough to put them all in one directory either. foobar.mp3 goes in /f/o/foobar.mp3 Actually, which *drive* it is on is determined by the date the audio was recorded, as I have about a Terabyte available spread across 4 cheap IDE drives, and just change a simple include file when one of the drives is nearly full to start using up the next one. So it really turnes into one of these: drive1/f/o/foobar.mp3 drive2/f/o/foobar.mp3 drive3/f/o/foobar.mp3 . . . based on what date the live performance occurred, which is in my meta-data, which I need to get for the on-the-fly ID3 tags anyway. [*] It's all very crude and shoestring budget, but it works. Well, except until they turned off the A/C in the office last summer, and then spilled coffee grounds all over the box... :-( But I found an old computer in the closet that I had found in a dumpster and threw the hard drives in that, and it works. 495 MHz seems enough for my audio server. :-) I'm finishing up a process of copying the files to a real host so I'll have a crude 2-tier fail-over. Anyway, you have to plan this out with some idea of what scale and scope you are dealing with to avoid insanity, but cramming binary data into the DB seems like the least attractive choice to me personally. [*] It's a shame the MP3 players all seem to ignore my nifty JPEG ID3 data I'm pre-pending to the audio streams. You have to download the files just to see the artist photo. Sigh. PS Feel free to give a listen if you like accoustic music: http://uncommonground.com/radio_hifi.m3u http://uncommonground.com/radio_lofi.m3u (hifi, lofi, respectively, obviously) iTunes Podcast version is in beta if you're interested in being a beta-tester... Same audio, just in RSS/XML format. -- --- Børge Kennel Arivene http://www.arivene.net --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] postgreSQl and images
Hi, I create a table with some large object (ref: OID) to store some images. When my PHP will display some data, it will also display the images stored as OID. However, i've read that before i must restore the image by exporting them to local (on server) file. isn't it easier in this case, to simply store the path and file name of file to DB and just read the data to display image on PHP pages ? what is the purpose in this case to store image a bytea / large object ? moreover, how my php code will load image from DB and stored there as OID ? isn't it too much complex in comparison with just storing path and name of image ? thanks a lot, Al.
Re: [PHP] postgreSQl and images
On Saturday 04 November 2006 18:26, Alain Roger wrote: Hi, I create a table with some large object (ref: OID) to store some images. When my PHP will display some data, it will also display the images stored as OID. However, i've read that before i must restore the image by exporting them to local (on server) file. No you don't. isn't it easier in this case, to simply store the path and file name of file to DB and just read the data to display image on PHP pages ? depends. what is the purpose in this case to store image a bytea / large object ? Keeping track af thousands of small images... in my case anyway. makes it very easy to keep track of what you got'n not. moreover, how my php code will load image from DB and stored there as OID ? the usual way? isn't it too much complex in comparison with just storing path and name of image ? depends again. You wouldn't be in my shoes storing all those small images on the fs and then keep track of them. either you end up with a had as method of grouping them together, moreover you can have thousands of small files inside one dir with an id name to it, and yes the last one, thousands of directories with one file inside... no, no and nono thanks a lot Al. -- --- Børge Kennel Arivene http://www.arivene.net --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Postgresql PHP
Any recommendations on books for postgresql PHP usage. Thanks, Dan. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Postgresql PHP
Hello Danny, Tuesday, March 22, 2005, 6:07:55 PM, you wrote: DB Any recommendations on books for postgresql PHP usage. Just get a good book on Postgres, the PHP side of it can be easily picked-up from the PHP manual itself. Best regards, Richard Davey -- http://www.launchcode.co.uk - PHP Development Services I do not fear computers. I fear the lack of them. - Isaac Asimov -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/Postgresql problem
I am trying to get PHP to work with an instance of postgresql on a server. There is already one, 'proprietary' postgresql running with its own user, own group and own (not default) port. The postgresql I am installing will be used for several database projects. following are my config/makes and the results: Postgres (7.3.5) configures, compiles and installs cleanly with: ./configure --enable-odbc --enable-tcl --enable-locale --with-python --with- perl --with-readline make; make install At which point I can create users and databases and connect to them with the command-line tools. PHP configures and compiles cleanly with: ./configure --with-apxs=/usr/local/apache/bin/apxs --with-mysql --with- pgsql=/usr/local/pgsql --with-gettext --with-ldap --without-imap --enable- sigchild --enable-force-cgi-redirect make However make test (or make install and attempt to use it) yield the follow flow of messages: PHP Warning: Function registration failed - duplicate name - pg_connect in Unknown on line 0 PHP Warning: Function registration failed - duplicate name - pg_pconnect in Unknown on line 0 PHP Warning: Function registration failed - duplicate name - pg_close in Unknown on line 0 PHP Warning: Function registration failed - duplicate name - pg_connection_status in Unknown on line 0 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/Postgresql problem
I am trying to get PHP to work with an instance of postgresql on a server. There is already one, 'proprietary' postgresql running with its own user, own group and own (not default) port. The postgresql I am installing will be used for several database projects. following are my config/makes and the results: Postgres (7.3.5) configures, compiles and installs cleanly with: ./configure --enable-odbc --enable-tcl --enable-locale --with-python --with- perl --with-readline make; make install At which point I can create users and databases and connect to them with the command-line tools. PHP configures and compiles cleanly with: ./configure --with-apxs=/usr/local/apache/bin/apxs --with-mysql --with- pgsql=/usr/local/pgsql --with-gettext --with-ldap --without-imap --enable- sigchild --enable-force-cgi-redirect make However make test (or make install and attempt to use it) yield the follow flow of messages: PHP Warning: Function registration failed - duplicate name - pg_connect in Unknown on line 0 PHP Warning: Function registration failed - duplicate name - pg_pconnect in Unknown on line 0 PHP Warning: Function registration failed - duplicate name - pg_close in Unknown on line 0 PHP Warning: Function registration failed - duplicate name - pg_connection_status in Unknown on line 0 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Contract Position at New York Post for PHP-PostgreSQL Developer
The New York Post is seeking a part-time consultant to participate in the planning and development of a web-based registration project. The position will last for approximately 3 months or until the candidate's role in the project is completed. The candidate will work with other programmers on staff to integrate database-backed online registration with an existing demographics database implemented in PostgreSQL and will report directly to the Senior Database Programmer. Work from home is an option. Salary commensurate with experience. There is a possibility for additional contracts with the Post and/or full-time employment in the future. The successful candidate will have the following: * Basic familiarity with Unix (Solaris and/or FreeBSD preferred) * Moderate to advanced knowledge of PostgreSQL (MySQL ok, but PostgreSQL preferred) * Experience with database planning (e.g., table design, indexing, logging, performance, knowledge of plpgsql a plus) * Experience with web-based registration projects a strong plus * Moderate to advanced knowledge of PHP (e.g., experience with functions that interface with PostgreSQL or MySQL a must, knowledge of application security a plus) * Basic html (forms, tables) Please email or mail resume and cover letter describing your qualifications to: Heather Johnson Senior Database Programmer New York Post 1211 6th Avenue, 9th Floor New York, NY 10036 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP POSTGRESQL AND WML
Hi. I am trying to send two variables from login.wml to auth.php. But auth.php gets empty variables. here is the code for login.wml: ?xml version=1.0? !DOCTYPE wml PUBLIC -//WAPFORUM//DTD WML 1.1//EN http://www.wapforum.org/DTD/wml_1.1.xml; wml card newcontext=true title=Fuhrpark Login id=login p fieldset title=name amp; PIN Name: input name=name type=text/br/ Password: input name=pin type=password/ /fieldset anchor Login go method=post href=auth.php postfield name=name value=$nameeingeben/ postfield name=pin value=$pineingeben/ /go /anchor do type=accept label=Login go method=post href=auth.php postfield name=name value=$nameeingeben/ postfield name=pin value=$pineingeben/ /go /do /p /card /wml and here is the code for auth.php: ?php echo (Name $name); echo (Pin $pin); ? What do i wrong? Please can you help me? __ ComputerBild (15-03) empfiehlt den besten Spam-Schutz: WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021125 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PostgreSQL Cursor ROWCOUNT
Given: PHP 4.x PostgreSQL 7.3.x In 'psql' monitor: declare foo cursor for select * from humongous_table; move forward all in foo; The second query will print out a number which informs one of the actual number of rows that are in the cursor's query's result set, if one were to actually fetch them all. I would like to get to that number in PHP. Alas, as far as I can tell, there is to way to get that number through the PHP API... For sure: pg_num_rows -- only counts rows actually fetched pg_last_notice -- nope pg_result_error -- nope pg_result_status -- nope No other documented functions seem appropriate, though I may have missed one... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Hi. I thought I had abstracted out the SQL querying part of my code out,
just to find out today that it doesn't work when it comes to
transactions. I had come up with this code:
function sql_query($sql) {
$conn = pg_connect(dbname=JC user=postgres);
$res = pg_exec($conn, $sql);
if (!$res) {
echo CONNECTION: could not execute query ($sql)br;
die;
}
else return $res;
}
I had transactions in my code implemented like this:
$sql = BEGIN;
sql_query($sql);
[some sql that should be in a transaction ...]
$sql = COMMIT;
sql_query($sql);
This doesn't work. Now that I look at my code I clearly see why. All sql
queries are executed using a new Postgres connection, hence the use of
BEGIN/COMMIT as I was using them have no effect.
Can someone recommend a way to abstract out my DB layer while still
being able to use transactions?
I was thinking of using the same function but if the incoming query
contained the word BEGIN, saving that and all future queries in a
session var and when the COMMIT comes in executing all the saved queries
as one (i.e. BEGIN;[];COMMIT). One drawback is that all queries
will be written out to disk (as session vars) and that will slow things
down. Another drawback is that I have to abort if not COMMIT comes in.
And a few more drawbacks ...
I was also thinking about maybe the $sql a GLOBAL or first building up
my query as as long string (BEGIN;[];COMMIT) and *then* sending it
to my sql_query() function.
The last two seem easier to implement, safer, and more efficient but
they don't seem elegant because I haven't abstracted out the fact that
I want a transaction. Whenever I write an SQL query I have to think
does this need to be in a transaction and then use a different coding
technique depending on the answer. And if the future something that
didn't need to be in a transaction now needs to be in a transaction I
have to revisit my code and change the code.
I'm sure someone out there must have thought about this and come up with
an elegant solution and way of abstracting out the DB layer from PHP.
Can anyone share their solution with me or give me some pointers to
reference material?
Thanks,
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Not exactly sure why your transactions aren't working but if your script
already has an open connection to the database and you issue another
call to pg_connect with the same connect string PHP will return the
existing connection and should not create another connection to the
database, that is provided you do not pg_close the connection between
calls.
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
The manual page for pg_connect is:
http://www.php.net/manual/en/function.pg-connect.php
Storing your db connection as a global will work.
On Thu, 2003-01-09 at 21:56, Jean-Christian Imbeault wrote:
Hi. I thought I had abstracted out the SQL querying part of my code out,
just to find out today that it doesn't work when it comes to
transactions. I had come up with this code:
function sql_query($sql) {
$conn = pg_connect(dbname=JC user=postgres);
$res = pg_exec($conn, $sql);
if (!$res) {
echo CONNECTION: could not execute query ($sql)br;
die;
}
else return $res;
}
I had transactions in my code implemented like this:
$sql = BEGIN;
sql_query($sql);
[some sql that should be in a transaction ...]
$sql = COMMIT;
sql_query($sql);
This doesn't work. Now that I look at my code I clearly see why. All sql
queries are executed using a new Postgres connection, hence the use of
BEGIN/COMMIT as I was using them have no effect.
Can someone recommend a way to abstract out my DB layer while still
being able to use transactions?
I was thinking of using the same function but if the incoming query
contained the word BEGIN, saving that and all future queries in a
session var and when the COMMIT comes in executing all the saved queries
as one (i.e. BEGIN;[];COMMIT). One drawback is that all queries
will be written out to disk (as session vars) and that will slow things
down. Another drawback is that I have to abort if not COMMIT comes in.
And a few more drawbacks ...
I was also thinking about maybe the $sql a GLOBAL or first building up
my query as as long string (BEGIN;[];COMMIT) and *then* sending it
to my sql_query() function.
The last two seem easier to implement, safer, and more efficient but
they don't seem elegant because I haven't abstracted out the fact that
I want a transaction. Whenever I write an SQL query I have to think
does this need to be in a transaction and then use a different coding
technique depending on the answer. And if the future something that
didn't need to be in a transaction now needs to be in a transaction I
have to revisit my code and change the code.
I'm sure someone out there must have thought about this and come up with
an elegant solution and way of abstracting out the DB layer from PHP.
Can anyone share their solution with me or give me some pointers to
reference material?
Thanks,
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Jason Sheets wrote:
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
You're right! I did some more testing and the problem is with my testing
code. I don't know why but the following code times out *but*, PHP
throws an error saying the code has timed out *but* calling
connection_status() says the code did *not* time out!
Any idea why connection_status() returns 0 when it should return 2??
My code:
set_time_limit(2);
echo set execution limit to 2 seconds BR;
register_shutdown_function(timed_out);
require_once(db_functions/sql_query.inc);
$sql = BEGIN;;
$res = sql_query($sql);
$sql = insert into test(test) values('testing 4');;
$res = sql_query($sql);
//This will cause the script to time out
$i = 0;
while(true) {$i++;}
$sql = COMMIT;;
$res = sql_query($sql);
function timed_out() {
$status = connection_status();
if ($status == 2) {
echo the script timed out BR;
}
else echo no time out. Connection status is $status BR;
}
The OUPUT:
set execution limit to 2 seconds
Fatal error: Maximum execution time of 2 seconds exceeded in
/www/htdocs/jc/shut.php on line 16
no time out. Connection status is 0
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Jean-Christian
If you are only doing an insert then you do not need the transactions
BEGIN and COMMIT because that is already done for you on a single
insert. PGSQL is transaction based so if it does not go then it will not
work.
-Ray
On Thu, 2003-01-09 at 22:23, Jean-Christian Imbeault wrote:
Jason Sheets wrote:
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
You're right! I did some more testing and the problem is with my testing
code. I don't know why but the following code times out *but*, PHP
throws an error saying the code has timed out *but* calling
connection_status() says the code did *not* time out!
Any idea why connection_status() returns 0 when it should return 2??
My code:
set_time_limit(2);
echo set execution limit to 2 seconds BR;
register_shutdown_function(timed_out);
require_once(db_functions/sql_query.inc);
$sql = BEGIN;;
$res = sql_query($sql);
$sql = insert into test(test) values('testing 4');;
$res = sql_query($sql);
//This will cause the script to time out
$i = 0;
while(true) {$i++;}
$sql = COMMIT;;
$res = sql_query($sql);
function timed_out() {
$status = connection_status();
if ($status == 2) {
echo the script timed out BR;
}
else echo no time out. Connection status is $status BR;
}
The OUPUT:
set execution limit to 2 seconds
Fatal error: Maximum execution time of 2 seconds exceeded in
/www/htdocs/jc/shut.php on line 16
no time out. Connection status is 0
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
You could try leaving off the ;...
Try $sql = BEGIN
Try $sql = COMMIT
That should work...
On Thu, 2003-01-09 at 22:23, Jean-Christian Imbeault wrote:
Jason Sheets wrote:
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
You're right! I did some more testing and the problem is with my testing
code. I don't know why but the following code times out *but*, PHP
throws an error saying the code has timed out *but* calling
connection_status() says the code did *not* time out!
Any idea why connection_status() returns 0 when it should return 2??
My code:
set_time_limit(2);
echo set execution limit to 2 seconds BR;
register_shutdown_function(timed_out);
require_once(db_functions/sql_query.inc);
$sql = BEGIN;;
$res = sql_query($sql);
$sql = insert into test(test) values('testing 4');;
$res = sql_query($sql);
//This will cause the script to time out
$i = 0;
while(true) {$i++;}
$sql = COMMIT;;
$res = sql_query($sql);
function timed_out() {
$status = connection_status();
if ($status == 2) {
echo the script timed out BR;
}
else echo no time out. Connection status is $status BR;
}
The OUPUT:
set execution limit to 2 seconds
Fatal error: Maximum execution time of 2 seconds exceeded in
/www/htdocs/jc/shut.php on line 16
no time out. Connection status is 0
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
Michael Sims wrote: o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. True, changing schema is a major PITA with Postgres. My only real complaint about it, in fact... Have you installed the recent PostgreSQL version 7.3 already? This new version allows the use of ALTER TABLE much like you'd want it. Vincent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP PostgreSQL
I'm switching from a MySQL environment to PGSQL and I'm going through and trying to learn the differences between the two. I've come across some issues that I can't seem to find the answers to in the docs or on the web. I'm hoping someone can help me out. * MySQL has a function to reset the result set so that it can be iterated through again - mysql_data_seek(). I don't see that there is a similar function for PGSQL. Is that true? If so, in order to reset the result set you have to perform the query again? Isn't that a waste of resources? * For PGSQL, you can get the database name, the field name even the *host name* but you can't get the table name from a particular query? Any info would be greatly appreciated! Chris
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 18:13, Boget, Chris wrote: I'm switching from a MySQL environment to PGSQL and I'm going through and trying to learn the differences between the two. I've come across some issues that I can't seem to find the answers to in the docs or on the web. I'm hoping someone can help me out. * MySQL has a function to reset the result set so that it can be iterated through again - mysql_data_seek(). I don't see that there is a similar function for PGSQL. Is that true? If so, in order to reset the result set you have to perform the query again? Isn't that a waste of resources? pg_result_seek() should perform a similar function. In most cases you wouldn't need to use that because if you're going to be using the results more than once you could store them in an array. * For PGSQL, you can get the database name, the field name even the *host name* but you can't get the table name from a particular query? Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* In Hollywood, all marriages are happy. It's trying to live together afterwards that causes the problems. -- Shelley Winters */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP PostgreSQL
pg_result_seek() should perform a similar function. In most cases you wouldn't need to use that because if you're going to be using the results more than once you could store them in an array. Sometimes yes, sometimes no. But a valid point nonetheless. * For PGSQL, you can get the database name, the field name even the *host name* but you can't get the table name from a particular query? Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information (user, pw, host and perhaps a dbname that may or may not be the dbname that gets queried later on). So sure, you can access those member variables (except maybe dbname; I don't think that gets stored) which is also information you can get programatically using built in functions. But let's say you pass a query to the query() method that doesn't use the dbname that you used when instantiating the object. That's certainly valid. But now, there's no way (ok, there's a way but it isn't necessarily accurate) to get the dbname for the query that was just run... apart from requiring the programmer to pass the dbname as another paramter to the query() method You can do this in mysql. I just don't know why you can't do this in pgsql. Chris
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 18:41, Boget, Chris wrote: * For PGSQL, you can get the database name, the field name even the *host name* but you can't get the table name from a particular query? Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information (user, pw, host and perhaps a dbname that may or may not be the dbname that gets queried later on). So sure, you can access those member variables (except maybe dbname; I don't think that gets stored) which is also information you can get programatically using built in functions. But let's say you pass a query to the query() method that doesn't use the dbname that you used when instantiating the object. That's certainly valid. But now, there's no way (ok, there's a way but it isn't necessarily accurate) to get the dbname for the query that was just run... apart from requiring the programmer to pass the dbname as another paramter to the query() method You can do this in mysql. I just don't know why you can't do this in pgsql. Sorry, you've lost me. In your OP you say you wanted the table name, but now you're talking about the dbname? For dbname, you can use (oddly enough) pg_dbname(). -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* [Babe] Ruth made a big mistake when he gave up pitching. -- Tris Speaker, 1921 */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
At 06:20 PM 12/30/02 +0800, Jason Wong wrote: On Monday 30 December 2002 18:13, Boget, Chris wrote: I'm switching from a MySQL environment to PGSQL and I'm going through and trying to learn the differences between the two. The things that bothered me the most: o Pg doesn't have DATE_FORMAT(), or the types SET and ENUM. o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. o The command line program's exit command starts with a \. It took me a long time to find that... The command line interface for pg is much better. o Large result sets are returned in less, and the command editor feels better. o There are lots of cool things you can look at with the \ commands in the command line program.try \help o You can see the source code of the queries that make up the \ commands. I don't remember exactly how, but it is either in \help or the man page. * MySQL has a function to reset the result set so that it can be iterated through again - mysql_data_seek(). pg_fetch_array() has a third parameter that specifies which record to return. In most cases you wouldn't need to use that because if you're going to be using the results more than once you could store them in an array. I disagree... You have easy access to the entire result set, in any order, with either database. You are wasting time and memory making another copy of the data. Rick -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP PostgreSQL
At 04:41 AM 12/30/02 -0600, Boget, Chris wrote: You can do this in mysql. I just don't know why you can't do this in pgsql. The basic answer to why the interfaces to the databases differ is because the PHP interfaces reflects the C interface provided by the database authors. Most PHP extensions are just wrappers that allow you to call existing libraries. Rick -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 19:11, Rick Widmer wrote: At 06:20 PM 12/30/02 +0800, Jason Wong wrote: On Monday 30 December 2002 18:13, Boget, Chris wrote: I'm switching from a MySQL environment to PGSQL and I'm going through and trying to learn the differences between the two. The things that bothered me the most: o Pg doesn't have DATE_FORMAT(), pg's date functions are pretty abysmal compared to mysql's. or the types SET and ENUM. These aren't standard and handling these in PHP does require a bit more code. So you're probably better off using an extra table instead. o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. pg's ALTER TABLE doesn't really let you alter much at all which makes prototyping a pain. o The command line program's exit command starts with a \. It took me a long time to find that... Use CTRL-d, it's a very commonly used exit command which gets you out of almost anything -- mysql, psql, ftp, telnet, ssh etc. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Ambiguity: Telling the truth when you don't mean to. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP PostgreSQL
Sorry, you've lost me. In your OP you say you wanted the table name, but now you're talking about the dbname? For dbname, you can use (oddly enough) pg_dbname(). Eeep, I'm ever so sorry. That's what I get for responding at 4:30a when trying to battle insomnia. Yes, I am looking for the table name, not dbname. My apologies. Chris
Re: [PHP] PHP PostgreSQL
Due to writing this email *way* too early in the morning, I got everything mixed up and didn't say what I meant. The below is what I really meant. Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information. When you pass a query to the query() there's no way (ok, there's a way but it isn't necessarily accurate) to get the table name for the query that was just run... apart from requiring the programmer to pass the table name as another paramter to the query() method You can do this in mysql using the mysql_tablename() function. I just don't know why you can't do this in pgsql. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Mon, 30 Dec 2002 04:11:02 -0700, you wrote:
The things that bothered me the most:
o Pg doesn't have DATE_FORMAT()
to_char() accomplishes the same thing, for example:
select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf
from users;
, or the types SET and ENUM.
I'm not sure what SET is, never used it, but ENUM is totally
unnecessary in Postgres because it supports foreign keys. If you want
a particular field to only allow ENUM('red', 'blue', 'green') then
create a lookup table, insert these values into it, and then set up a
foreign key. This is more standard anyway, and if you need to update
the set there is no need to touch the database schema.
o Changing database structure is harder. With PG, I usually found it
easier to dump, edit, then reload the database to make changes I did
in MySQL with ALTER TABLE.
True, changing schema is a major PITA with Postgres. My only real
complaint about it, in fact...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
o Pg doesn't have DATE_FORMAT() to_char() accomplishes the same thing, for example: select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf from users; Unless I missed something, that function doesn't work with unix timestamps. Is there anythin internal to PG that allows you to convert to and from unix timestamps? , or the types SET and ENUM. o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. True, changing schema is a major PITA with Postgres. My only real complaint about it, in fact... That's how I did it and it was, indeed, a pain in the butt... But there was some good that came out of it. When I created the MySQL tables, I didn't have foreign keys (wasn't using InnoDB), constraints or anything like that so in recreating the tables, I was able to implement those things to make life easier down the road when reworking the code and the logic. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Mon, 30 Dec 2002 07:57:04 -0600, you wrote:
o Pg doesn't have DATE_FORMAT()
to_char() accomplishes the same thing, for example:
select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf
from users;
Unless I missed something, that function doesn't work with unix timestamps.
Is there anythin internal to PG that allows you to convert to and from unix
timestamps?
To convert TO a unix timestamp:
select date_part('epoch', lastlogin) as lastloginf;
or
select extract(epoch from lastlogin) as lastloginf;
To convert FROM a unix timestamp:
select to_char(timestamp 'epoch' + unix_timestamp, 'FMMM-DD-
FMHH:MI AM');
That's how I did it and it was, indeed, a pain in the butt... But there was
some good that came out of it. When I created the MySQL tables, I didn't
have foreign keys (wasn't using InnoDB), constraints or anything like that
so in recreating the tables, I was able to implement those things to make
life easier down the road when reworking the code and the logic.
And there is the major benefit of using Postgres...not to mention
sub-selects, views, unions, etc. Once you get used to these things
it's very hard to give them up.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 21:33, Chris Boget wrote: Due to writing this email *way* too early in the morning, I got everything mixed up and didn't say what I meant. The below is what I really meant. Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information. When you pass a query to the query() there's no way (ok, there's a way but it isn't necessarily accurate) to get the table name for the query that was just run... apart from requiring the programmer to pass the table name as another paramter to the query() method You can do this in mysql using the mysql_tablename() function. I just don't know why you can't do this in pgsql. Either you're confused or I'm confused :) mysql_tablename() is used in conjunction with mysql_list_tables() to get the names of the tables defined in the specified database. It cannot get the table name for the query that was just run And another thing, you _know_ the query that was just run, and presumably the tables used is part of the query itself, so why would you need another function to tell you should already know? Can you give an example of what you're trying to do? -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Q: How did you get into artificial intelligence? A: Seemed logical -- I didn't have any real intelligence. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
To convert TO a unix timestamp: To convert FROM a unix timestamp: Excellent. Thank you very much for the info! And there is the major benefit of using Postgres... Why do you think I decided to move to Postgres? :P not to mention sub-selects, views, unions, etc. Indeed. And I'm going to take every advantage in using them in my code. I figure when all is said and done, the scripts will run so much faster because it won't have to be doing those things programatically. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 21:57, Chris Boget wrote: o Pg doesn't have DATE_FORMAT() to_char() accomplishes the same thing, for example: select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf from users; Unless I missed something, that function doesn't work with unix timestamps. No, it only works on pg's date/time formats. Is there anythin internal to PG that allows you to convert to and from unix timestamps? Best to store date/time in pg's format rather than unix timestamp. If you've got legacy data which are stored as unix timestamps then you can do one of the following: 1) Do a one time conversion from unix timestamp to pg date/time 2) Manipulate the dates in PHP instead 3) Write your own function in pg to convert them on the fly -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Griffin's Thought: When you starve with a tiger, the tiger starves last. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
Either you're confused or I'm confused :) It's me. This just hasn't been a good week for me. :p One time I'll get it right! mysql_tablename() is used in conjunction with mysql_list_tables() to get the names of the tables defined in the specified database. It cannot get the table name for the query that was just run You are right. The function I was thinking of was mysql_field_table() I knew I should have consulted the documentation and not relied on my memory. :p And another thing, you _know_ the query that was just run, and presumably the tables used is part of the query itself, That's not true in all cases. so why would you need another function to tell you should already know? Can you give an example of what you're trying to do? Sure. And this is what prompted my message initially. PEAR's sql2xml only works with mysql because there's no way to get the table name programatically. When you are using an abstraction such as this, unless you pass another argument to the primary function, you have to go through hoops within the abstraction just to pull it out of the query. The code for sql2xml can be found here: http://pear.php.net/package-info.php?pacid=18 Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
At 07:59 AM 12/30/02 -0600, Michael Sims wrote: On Mon, 30 Dec 2002 04:11:02 -0700, you wrote: , or the types SET and ENUM. I'm not sure what SET is, never used it, Color set( 'red', 'green', 'blue' ) can contain only the following values: NULL;'blue'; 'green'; 'green,blue'; 'red'; 'red,blue'; 'red,green'; 'red,green,blue'; You can enter the color names in any order, but they always come out like this. Only words contained in the set are allowed. You can also look at a set as a number, and do bit manipulation on it. The list of values above are 0 to 7 when used in a numeric context. but ENUM is totally unnecessary in Postgres because it supports foreign keys. The big difference using set and enum is in data storage. If you look on the disk you will find both types are stored in the smallest integer they fit in. Even 'red,green,blue' fits in a single byte each time it is stored. This is more standard anyway, and if you need to update the set there is no need to touch the database schema. I like being able to define some things in the schema. I have widgets in my toolkit that build arrays of checkboxes, radio buttons or drop down lists based on the values allowed in an enum or set. They get used quite a bit, in fact losing these widgets may be the biggest thing keeping me using MySQL. (Harder prototyping is a close second.) Rick. P.S. Thanks for the info on handling dates! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] postgresql - image - php
anyone know how to store an image to postgresql and load it back using php? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PostgreSQL
How can i enable PostgreSQL module? I have installed PHP 4 on IIS5, Windows 2000 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PostgreSQL
PHP is compiled with PostgreSQL support but I can not connect. I get this error message What does it mean and what do I need to do to fix it Warning: pg_connect() unable to connect to PostgreSQL server: connectDB() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'localhost' and accepting connections on TCP/IP port '5583'? in /home/sites/home/web/test/test.php on line 2 -- Best regards, rdkurth mailto:[EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL
[EMAIL PROTECTED] wrote: PHP is compiled with PostgreSQL support but I can not connect. I get this error message What does it mean and what do I need to do to fix it Warning: pg_connect() unable to connect to PostgreSQL server: connectDB() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'localhost' and accepting connections on TCP/IP port '5583'? in /home/sites/home/web/test/test.php on line 2 either your postmaster is not running... or your postmaster does not accept TCP/IP connections... read the documentation for PostgreSQL Installation it has tons of info there for your convenience... try editing your pg_hba.conf file for postgreSQL... so it will accept tcp IP connections: host all IP 255.255.255.0 trust where IP is the ip address of the host that will access the postgreSQL database at the server. i hope i got it right -- Lejanson C. Go, SDE I -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PostgreSQL - WHILE loop working too well / WARNING: Unable to jump to row 2 on PostgreSQL...
I do not understand why PHP is trying to pull row 2!! I want to create an
array of data and construct a table from a query. The echo of $rows shows 2
rows, so if I'm setting my variable to 0 and incrementing with each loop, it
doesn't make sense to me why it tries to pull row 2. It should stop at row
1 because the next loop would make the variable 2 and the if says if $ii is
less than 2.
WHAT AM I MISSING!?
I have a while loop written to pull data from a postgreSQL database. I have
2 and only 2 rows of data in this table. Probably labeled within PostgreSQL
as '0' and '1'. The browser displays the error message along with the
proper table listed below it!!
Warning: Unable to jump to row 2 on PostgreSQL result index 2 in
/var/www/html/steve/frontdoor.php on line 92
sgaas Steve Gaas
mjohnson Matt Johnson
The code is:
?
$results = pg_exec($link, select * from users);
if (!$link) {
print Could not connect;
}
$ii = 0;
$rows = pg_numrows($results);
if ($rows) {
echo There are $rows rows of data in $localdb;
}
echo BR\nHR;
?
table
?
if ( $ii $rows) {
while ($tabledata = pg_fetch_array($results, $ii)) {--(LINE 92)
$username = $tabledata[username];
$firstname = $tabledata[firstname];
$lastname = $tabledata[lastname];
echo TR\n;
echo td$username/td\n;
echo td$firstname/td\n;
echo td$lastname/td\n;
echo /tr\n;
$ii++;
}
}
?
/table
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL - WHILE loop working too well / WARNING: Unable to jump to row 2 on PostgreSQL...
On Thursday 30 May 2002 11:30, Steve G wrote:
Please do not reply to an existing post. Start a new one!
I do not understand why PHP is trying to pull row 2!! I want to create an
array of data and construct a table from a query. The echo of $rows shows
2 rows, so if I'm setting my variable to 0 and incrementing with each loop,
it doesn't make sense to me why it tries to pull row 2. It should stop at
row 1 because the next loop would make the variable 2 and the if says if
$ii is less than 2.
The if statement only runs once. At the time it is run the statement is true
(ie $ii 2) ...
WHAT AM I MISSING!?
I have a while loop written to pull data from a postgreSQL database. I
have 2 and only 2 rows of data in this table. Probably labeled within
PostgreSQL as '0' and '1'. The browser displays the error message along
with the proper table listed below it!!
... but inside your while loop you have $ii++, which results in ...
Warning: Unable to jump to row 2 on PostgreSQL result index 2 in
/var/www/html/steve/frontdoor.php on line 92
sgaas Steve Gaas
mjohnson Matt Johnson
Basically you're going about this the wrong way. Replace this:
?
if ( $ii $rows) {
while ($tabledata = pg_fetch_array($results, $ii)) {--(LINE 92)
$username = $tabledata[username];
$firstname = $tabledata[firstname];
$lastname = $tabledata[lastname];
echo TR\n;
echo td$username/td\n;
echo td$firstname/td\n;
echo td$lastname/td\n;
echo /tr\n;
$ii++;
}
}
?
with:
/** Untested use with extreme caution **/
while ($tabledata = pg_fetch_array($results)) {
$username = $tabledata[username];
$firstname = $tabledata[firstname];
$lastname = $tabledata[lastname];
echo TR\n;
echo td$username/td\n;
echo td$firstname/td\n;
echo td$lastname/td\n;
echo /tr\n;
}
--
Jason Wong - Gremlins Associates - www.gremlins.com.hk
Open Source Software Systems Integrators
* Web Design Hosting * Internet Intranet Applications Development *
/*
Occam's eraser:
The philosophical principle that even the simplest
solution is bound to have something wrong with it.
*/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Win32 PHP + PostgreSQL support
Any ideas how to get PHP for win32 with PostgreSql support? Cheers Todor -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Win32 PHP + PostgreSQL support
Doesn't php for windows come with pgsql already? extension=php_pgsql.dll Gary Todor Stoyanov wrote: Any ideas how to get PHP for win32 with PostgreSql support? Cheers Todor -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: [PHP-DB] PHP + Postgresql + Linux = Frustration
Is there an easy way to do this sort of stuff on Linux or is it better to just buy off the shelf products that work? Hi I hate to do the my distro is better than yours thing, but typing apt-get install postgresql postgresql-client apache php4 php4-pgsql at the command line on a Debian system will download and install it all automatically for you. Just configure the postgres settings to how you want them, edit the php.ini and httpd.conf files and that's it. I run a debian-based system at home with postgresql, apache and PHP4, and I've also set up web servers with this configuration on Debian boxes. If you're looking for ease-of-maintainence of a Linux server, then Debian is really the one you want to look at. --jaa -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [PHP-DB] PHP + Postgresql + Linux = Frustration
Mike, This can be a bit frustrating, but all in all it's not too bad. I usually make up my own configuration file just to avoid re-typing everything, because I generally don't get it right the first time. I then chmod +x them so that they will execute. They're quite simple, just scripts that call configure with all the various parameters. I arrived at the choices below after reading the LAMP documents, the Soothingly Seamless Installation of Linux, Apache, MySQL and PHP and the various configuration options for each of MySQL, PHP, PostgreSQL and Apache. Before building from source I tried working with RPMs on my older Red Hat systems, and with dselect on two Debian boxes. These are all well-debugged software packages, and aside from taking some time to compile on the oldest box, a P 133, everything went smoothly. Here's the one for PostgreSQL, locating PostgreSQL in a very non-standard location. Can't remember why I wanted w-odbc. ---start of script -- #!/bin/sh ./configure \ --with-prefix=/drv2/bin/pgsql \ --with-odbc -- end of script For Apache I wanted a whole bunch of stuff, thus enable-shared=max. If I remember correctly that let me load all the modules dynamically. ---start of script -- #!/bin/sh ./configure \ --with-layout=Apache\ --prefix=/usr/local/apache \ --enable-shared=max -end of script --- And finally PHP. with apxs allows Apache to load PHP dynamically, and the parameter has to be specific. with-mysql points to my non-standard location of mysql, similarly I told it explicity where PostgreSQL was installed. --- start of script #! /bin/sh ./configure \ --with-apxs=/usr/local/apache/bin/apxs \ --with-mysql=/drv2/bin/mysql \ --with-pgsql=/usr/local/pgsql \ --enable-track-vars - end of script Pay attention to the line in the PHP INSTALL doc refers to relocating php-ini-dist (think that's the name) and to the changes you have to make to Apache's httpd.conf file. When all configuring, making and installing is done, you may get an error from Apache that it can't find the .so modules for PostgreSQL (and in my case, MySQL). If so, locate the modules and edit /etc/ld.so.conf, adding the paths to these modules. On my system I added these two lines, /usr/local/pgsql /drv2/bin/mysql/lib/mysql Save the file and run ldconfig. Apache should then run OK. As to your question Why doesn't PHP have support for PostgreSQL built in .., have a look at all the different databases PHP supports and consider how immense the program would be if everything was compiled in by default. Hope this is helpful. If you have more problems, there is a php-install list which deals with specific installation problems. Regards - Miles Thompson PS These 4 programs were my first experience building on Linux systems, and initially it was a bit daunting. I hope you find this helpful. /mt At 12:15 PM 1/26/2002 -0500, you wrote: Hello, I am rather new to Linux but an old timer at software development. I am investigating alternate platforms for future development for my company. Linux is at the top of the list. I am trying to get a server setup with a db (PostgreSQL), web server (Apache), and some sort of web scripting language (PHP). I can not make everything work together. PHP4 won't work out-of-the-box with PostgreSQL because PHP doesn't have support for PosgreSQL built in at compile time (why not just build everything into when it was initially built?). So I get the sources to PHP and try to build. However, it wants the sources (header files) for Apache. At this point I don't want to go any further. Eventually, I'll probably need the sources for everything. Is there an easy way to do this sort of stuff on Linux or is it better to just buy off the shelf products that work? Thanks, Mike -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL query taking a long time
use EXPLAIN to get more information about how postgres executes this query.
I'm not sure about this, but there are some issues with fields of type int8.
It may help to cast explicitly the fields involved in join like:
WHERE bible.book::int4 = books.id::int4
Ask it the postgres mailing list, too!
btw, what version of pg do you have?
- Original Message -
From: K Old [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, December 21, 2001 5:51 PM
Subject: [PHP] PostgreSQL query taking a long time
| Hello all,
|
| I have a PostgreSQL database that is storing The Bible. It has 31,103
| records in it and I have a PHP page executing this query:
| SELECT
| books.book_name, bible.chapter, bible.verse, bible.versetext
| FROM asv_bible bible, book_bible books WHERE bible.book = books.id ORDER BY
| random() LIMIT 1
|
| The database schema is:
|
| /*
| Sequences
| */
| CREATE SEQUENCE book_bible_seq start 1 increment 1 maxvalue 2147483647
| minvalue 1 cache 1;
|
| /*
| Table structure for table asv_bible
| */
| CREATE TABLE asv_bible (
|id int8 NOT NULL,
|book int8,
|chapter int8,
|verse int8,
|versetext text,
|CONSTRAINT asv_bible_pkey PRIMARY KEY (id)
| );
|
|
|
| /*
| Table structure for table book_bible
| */
| CREATE TABLE book_bible (
|id int4 DEFAULT nextval('book_bible_seq'::text) NOT NULL,
|book_name varchar(20),
|CONSTRAINT book_bible_pkey PRIMARY KEY (id)
| );
|
| Right now it takes 9 seconds to return the results. I was wondering if
| anyone could offer any help with lowering the time it takes to run?
|
| Or if this is the normal runtime for a database of this size, I'd just like
| confirmation.
|
| Thanks,
| Kevin
|
|
| _
| Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
|
|
| --
| PHP General Mailing List (http://www.php.net/)
| To unsubscribe, e-mail: [EMAIL PROTECTED]
| For additional commands, e-mail: [EMAIL PROTECTED]
| To contact the list administrators, e-mail: [EMAIL PROTECTED]
|
[PHP] PostgreSQL query taking a long time
Hello all,
I have a PostgreSQL database that is storing The Bible. It has 31,103
records in it and I have a PHP page executing this query:
SELECT
books.book_name, bible.chapter, bible.verse, bible.versetext
FROM asv_bible bible, book_bible books WHERE bible.book = books.id ORDER BY
random() LIMIT 1
The database schema is:
/*
Sequences
*/
CREATE SEQUENCE book_bible_seq start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1;
/*
Table structure for table asv_bible
*/
CREATE TABLE asv_bible (
id int8 NOT NULL,
book int8,
chapter int8,
verse int8,
versetext text,
CONSTRAINT asv_bible_pkey PRIMARY KEY (id)
);
/*
Table structure for table book_bible
*/
CREATE TABLE book_bible (
id int4 DEFAULT nextval('book_bible_seq'::text) NOT NULL,
book_name varchar(20),
CONSTRAINT book_bible_pkey PRIMARY KEY (id)
);
Right now it takes 9 seconds to return the results. I was wondering if
anyone could offer any help with lowering the time it takes to run?
Or if this is the normal runtime for a database of this size, I'd just like
confirmation.
Thanks,
Kevin
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL query taking a long time
Hi,
You dont have the book field in the asv_bible table indexed. Use this to
index it
CREATE INDEX myindexname ON asv_bible(book);
(the primary key fields are ok because PostgreSQL creates a unique index to
implement the PRIMARY KEY constraint).
Hope that helps :)
--
Shane
I have a PostgreSQL database that is storing The Bible. It has 31,103
records in it and I have a PHP page executing this query:
SELECT
books.book_name, bible.chapter, bible.verse, bible.versetext
FROM asv_bible bible, book_bible books WHERE bible.book = books.id ORDER BY
random() LIMIT 1
The database schema is:
/*
Sequences
*/
CREATE SEQUENCE book_bible_seq start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1;
/*
Table structure for table asv_bible
*/
CREATE TABLE asv_bible (
id int8 NOT NULL,
book int8,
chapter int8,
verse int8,
versetext text,
CONSTRAINT asv_bible_pkey PRIMARY KEY (id)
);
/*
Table structure for table book_bible
*/
CREATE TABLE book_bible (
id int4 DEFAULT nextval('book_bible_seq'::text) NOT NULL,
book_name varchar(20),
CONSTRAINT book_bible_pkey PRIMARY KEY (id)
);
Right now it takes 9 seconds to return the results. I was wondering if
anyone could offer any help with lowering the time it takes to run?
Or if this is the normal runtime for a database of this size, I'd just like
confirmation.
Thanks,
Kevin
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Hmmm? PHP+PostgreSQL
I've installed PostgreSQL into /usr/local/pgsql, then I tried to include into my PHP support for PostgreSQL and made next: (I had pre-compiled support for mySQL before that) make clean ./configure --with-apxs=/usr/local/apache/bin/apxs --enable-versioning --enable-track-vars --with-pgsql --with-mysql make make install (I have mySQL, too) Compilation process was great, no errors. Nevertheless, next little PHP script: html head titletesting needed functions of current-gtlg with PHP+PostgreSQL/title /head body bgcolor=white ?php $conn = pg_connect (host=localhost port=5432 dbname=test user=postgres); ? /body /html still says: Fatal error: Call to undefined function: pg_connect() in /usr/local/apache/htdocs/pg-php/index.php on line 7 What I did wrong, guys? Will be looking forward your answer, please, CC: to [EMAIL PROTECTED] Thanks. -- green [http://www.extrasy.net] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] PostgreSQL connection problems
Warning: Unable to connect to PostgreSQL server: No pg_hba.conf entry for host myhost, user myuser, database mydb in /path/to/principal.php on line 27 that's $bdConexion = pg_connect (host=.$bdHost. port=.$bdPuerto. dbname=.$bdBD. user=.$bdLogin. password=.$bdPassword); PHP is already compiled with PostgreSQL support. PostgreSQL is running on a remote server. Thnx in advance -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL connection problems
You need to configure you database in the pg_hba.conf. The pg_hba.conf is in your postgres directory. Put one line like this in your pg_hba.conf host yourdatabase yourhost 255.255.255.255 trust - Original Message - From: Alberto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 27, 2001 7:51 AM Subject: [PHP] PostgreSQL connection problems Warning: Unable to connect to PostgreSQL server: No pg_hba.conf entry for host myhost, user myuser, database mydb in /path/to/principal.php on line 27 that's $bdConexion = pg_connect (host=.$bdHost. port=.$bdPuerto. dbname=.$bdBD. user=.$bdLogin. password=.$bdPassword); PHP is already compiled with PostgreSQL support. PostgreSQL is running on a remote server. Thnx in advance -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Postgresql large objects.
Does using pg_loread or any other php functions for postgresql large objects modify the size of the file? I'm seeing a size increase in a few bytes when I receive the file back to my hard drive. The file still seems to run ok, but I'm wondering what is doing this? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Postgresql + Sessions + Large objects.
I am having trouble with the following function working _with_
sessions. If I turn off my session checking function it enables you to
download the file flawlessly, if sessions are turned on then Internet
Explorer displays an error:
Internet Explorer cannot download ...URL
Internet Explorer was not able to open this Internet site. The requested
site is either unavailable or cannot be found. Please try again later.
Is there a way I can download large objects from postgresql _with_
sessions? Thankyou.
function retrieve_file($id) {
global $dbconn1;
$result1 = pg_exec ($dbconn1, select data, file_name from files where
id='$id';);
$image_oid = pg_result($result1 ,0, 'data');
$file_name = pg_result($result1 ,0, 'file_name');
header (Content-disposition: filename=\$file_name\);
header(Content-type: application/download);
pg_exec ($dbconn1, begin transaction;);
$image_handle = pg_loopen ($dbconn1, $image_oid, 'r');
pg_loreadall ($image_handle); pg_loclose ($image_handle);
pg_exec ($dbconn1, commit transaction;); }
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs. Interbase
Hi Please discuss as to your point of view the advantage of PostgreSQL over Interbase and/or vise versa. I'm considering three (3) important points I'm just evaluating Interbase and there is an important point that did not come out in the thread. It seems that Borland went back on key commitments to the open source development team and lost their trust and goodwill. As a result, the source has forked, with most of the momentum behind the fully open source Firebird project. Borlands commitment to their own 'official' version looks pretty thin. You can find info on Firebird at: http://firebird.sourceforge.net http://www.ibphoenix.com http://www.interbase2000.com Things are at an early stage, but Firebird have already identified and fixed a major security hole during their security audit of the Borland code, and offer binary builds for a number of platforms. Looks like the Firebird fork is the one with the future. Geoff Caplan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs. Interbase
Altunergil, Oktaywrote: The link that goes to interbase's web site in freshmeat.com redirects to http://www.borland.com/interbase/ which does not list the product as free or open source? Is there another version? oktay My understanding is that the current open source version is available from: http://www.borland.com/devsupport/interbase/opensource/ Ongoing certified versions with Borland support will be available from somewhere on borland.com for a price and any future open source versions will be available from SourceForge. Cheers, Luke Welling -- PHP and MySQL Web Development by Luke Welling and Laura Thomson http://www.amazon.com/exec/obidos/ASIN/0672317842/tangledwebdesign -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] PostgreSQL vs. Interbase
Hi there. I'm currently in serious research on what database to use for a B2B site which is expected to hold millions of records. I have in so far considered two open source databases - Interbase and PostgreSQL. With this in mind, I'm sending this email to both the PostgreSQL and Interbase mailing lists and also on PHP's. I would appreciate what ever information you can provide. Thanks :) My inquiry: Please discuss as to your point of view the advantage of PostgreSQL over Interbase and/or vise versa. I'm considering three (3) important points 1. Speed 2. Data Reliability 3. Portability Your messages will be greatly appreciated. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs. Interbase
Arnold Gamboa wrote: I'm currently in serious research on what database to use for a B2B site which is expected to hold millions of records. I have in so far considered two open source databases - Interbase and PostgreSQL. With this in mind, I'm sending this email to both the PostgreSQL and Interbase mailing lists and also on PHP's. I would appreciate what ever information you can provide. Thanks :) My inquiry: Please discuss as to your point of view the advantage of PostgreSQL over Interbase and/or vise versa. I'm considering three (3) important points 1. Speed 2. Data Reliability 3. Portability I first saw your message on the PostgreSQL list, but figured what I was going to answer belonged here more. One other thing you might like to consider is documentation. Interbase probably has better documentation than PostgreSQL from their respective primary sources, but you will find a *lot* more documentation on using PHP with PostgreSQL than with Interbase. Where PHP examples and documentation is database specific, it is mostly MySQL, some PostgreSQL, some Oracle and very little of any other. Cheers, Luke Welling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] PostgreSQL vs. Interbase
The link that goes to interbase's web site in freshmeat.com redirects to http://www.borland.com/interbase/ which does not list the product as free or open source? Is there another version? oktay -Original Message- From: Luke Welling [mailto:[EMAIL PROTECTED]] Sent: Monday, May 07, 2001 3:17 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] PostgreSQL vs. Interbase Arnold Gamboa wrote: I'm currently in serious research on what database to use for a B2B site which is expected to hold millions of records. I have in so far considered two open source databases - Interbase and PostgreSQL. With this in mind, I'm sending this email to both the PostgreSQL and Interbase mailing lists and also on PHP's. I would appreciate what ever information you can provide. Thanks :) My inquiry: Please discuss as to your point of view the advantage of PostgreSQL over Interbase and/or vise versa. I'm considering three (3) important points 1. Speed 2. Data Reliability 3. Portability I first saw your message on the PostgreSQL list, but figured what I was going to answer belonged here more. One other thing you might like to consider is documentation. Interbase probably has better documentation than PostgreSQL from their respective primary sources, but you will find a *lot* more documentation on using PHP with PostgreSQL than with Interbase. Where PHP examples and documentation is database specific, it is mostly MySQL, some PostgreSQL, some Oracle and very little of any other. Cheers, Luke Welling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] PostgreSQL and PHP - some Great Bridge news
All, I'm pleased to announce two initiatives at Great Bridge aimed at improving the integration of PostgreSQL and PHP, and some exciting stuff that we're doing as a company to get behind PHP more fully. First, Great Bridge is partnering with Zend Technologies to include a fully-integrated installation of PHP in an upcoming Web developer's edition of Great Bridge PostgreSQL. In addition to professionally-supported PHP (and other open source Web technologies), we'll also give our customers the chance to purchase Zend's value-added PHP tools (IDE, cache, encoder, and more...) Zend is a major contributor to the open source PHP effort, and we're very happy to be working with them. Secondly, we're pleased to announce that three PHP core developers have joined the Great Bridge advisory committee- Rasmus Lerdorf, the creator of PHP; Thies Arntzen, an expert in PHP-database connectivity; and Sascha Schumann, PHP author and provider of high-performance webchat solutions. Rasmus, Thies, and Sascha will work with us to help craft our Web developer products and services, including (of course) a fully optimized free download package. We'll be putting some development energy into the PHP-Postgres interface, and look for other ways the two open source projects can help each other. We're also very interested in hacker feedback as to what you'd like to see in a software and service package that revolves around the Web development "stack." What kind of tools, building blocks, and applications would you like to see commercially supported? Don't be shy - please email me at [EMAIL PROTECTED] with any suggestions. I'll also be at ApacheCon in a few weeks if you'd like to get together in person. Thanks, Ned -- Ned Lilly e: [EMAIL PROTECTED] Vice Presidentw: www.greatbridge.com Evangelism / Hacker Relationsv: 757.233.5523 Great Bridge, LLCf: 757.233. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] PHP PostgreSQL
Hi, All. I have to build a web site and I was studying postgreSQL, but, whether I am quite wrong or this database does not support Foreign Key. Does anyone use postgreSQL and know how handle Foreign Keys ??? Thanks, Marcelo Pereira Computer Programmer __ O BOL é Top10 no iBest! Vote já para torná-lo Top3! http://www.bol.com.br/ibest.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PHP PostgreSQL
On Sun, 4 Mar 2001, Marcelo Pereira wrote: Hi, All. I have to build a web site and I was studying postgreSQL, but, whether I am quite wrong or this database does not support Foreign Key. Does anyone use postgreSQL and know how handle Foreign Keys ??? Yes, it supports Foreign Keys ... how to use them is another story, as its something I keep meaning to dive into and just never seem to find the time for :( Check out: http://postgresql.readysetnet.com/users-lounge/docs/7.0/user/sql-createtable.htm which talks about using CONSTAINTS on tables, where 'REFERENCES' is discussed ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PHP PostgreSQL
The following is an example demonstrating how to set up a foreign key
constraint in PostgreSQL 7:
CREATE SEQUENCE school_id_seq;
CREATE TABLE school (
school_id INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'),
school_name VARCHAR(80) NOT NULL
);
CREATE SEQUENCE student_id_seq;
CREATE TABLE student (
student_id INT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'),
student_name VARCHAR(80) NOT NULL,
school_idINT NOT NULL,
CONSTRAINT school_exists
FOREIGN KEY(school_id) REFERENCES school
ON DELETE RESTRICT
);
INSERT INTO school (school_name) VALUES ('Alice''s School of Truck
Driving');
INSERT INTO school (school_name) VALUES ('Bob''s School of Underwater
Knitting');
INSERT INTO student (student_name, school_id) VALUES ('Charlie', 1);
INSERT INTO student (student_name, school_id) VALUES ('Doug', 1);
INSERT INTO student (student_name, school_id) VALUES ('Ernie', 2);
Note the 'ON DELETE RESTRICT' which will prevent you from deleting a school
if there is a student going to that school. First look at what's in the
tables:
SELECT * FROM school;
SELECT * FROM student;
Now attempt to delete the school:
DELETE FROM school WHERE school_id = 1;
If you try the above, you should see 'ERROR: school_exists referential
integrity violation - key in school still referenced from student' and
notice that nothing was deleted.
If you want to try the above more than once, the following may be handy:
DROP SEQUENCE school_id_seq;
DROP TABLE school;
DROP SEQUENCE student_id_seq;
DROP TABLE student;
Cascade Update
Instead of 'ON DELETE RESTRICT' you could specify 'ON DELETE CASCADE'. This
would allow the delete (instead of preventing it like in the example above),
but it would 'cascade' the delete to the student table so that any students
going to the school you deleted would also be deleted.
As well as the 'ON DELETE ...' clause you can also specify what is to happen
on an update of the foreign key with either:
a.. ON UPDATE RESTRICT or
b.. ON UPDATE CASCADE
ON UPDATE RESTRICT would prevent UPDATE school SET school_id = 20 WHERE
school_id = 1 from proceeding if there were any students with a school_id of
1.
You might be able to guess that ON UPDATE CASCADE would allow the UPDATE
school SET school_id = 20 WHERE school_id = 1 to proceed, but it would also
update the school_id field in the student table appropriately.
Foreign Keys where the field names are different
Consider the following table setup:
CREATE SEQUENCE school_id_seq;
CREATE TABLE school (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'),
name VARCHAR(80) NOT NULL
);
CREATE SEQUENCE student_id_seq;
CREATE TABLE student (
idINT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'),
name VARCHAR(80) NOT NULL,
school_id INT NOT NULL,
CONSTRAINT school_exists
FOREIGN KEY(school_id) REFERENCES school(id)
ON DELETE RESTRICT
);
INSERT INTO school (name) VALUES ('Alice''s School of Truck Driving');
INSERT INTO school (name) VALUES ('Bob''s School of Underwater Knitting');
INSERT INTO student (name, school_id) VALUES ('Charlie', 1);
INSERT INTO student (name, school_id) VALUES ('Doug', 1);
INSERT INTO student (name, school_id) VALUES ('Ernie', 2);
Note how the field names don't match (school.id as opposed to
student.school_id); in this case we can put the field name in brackets after
the table name.
As before, the following will fail:
DELETE FROM school WHERE id = 1;
- AndrewH
- Original Message -
From: "Marcelo Pereira" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, March 05, 2001 1:00 AM
Subject: [PHP] PHP PostgreSQL
Hi, All.
I have to build a web site and I was
studying postgreSQL, but, whether I am
quite wrong or this database does not
support Foreign Key.
Does anyone use postgreSQL and know
how handle Foreign Keys ???
Thanks,
Marcelo Pereira
Computer Programmer
__
O BOL Top10 no iBest! Vote j para torn-lo Top3!
http://www.bol.com.br/ibest.html
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
On Friday 02 March 2001 00:23, Shaun Thomas wrote: On Thu, 1 Mar 2001, Meir kriheli wrote: I use both of the databases (Interbase 6.01 and PostgreSQL 7.1beta4). PostgreSQL has more features comapared to Interbase (the procedureal language is very robust and there are many datatyps to choose from. Also you can have some kind of object support in it to inherit tables for example), but it's windows implemenation is very hard (at least for me, I like to compile it). Postgres also has a nasty show-stopping bug they don't seem to want to fix. Try making a stored procedure with many parameters, and send a single null to it. I dare you. I so love having all of my other parameters, and the return value of the function turned into null because postgres can't tell where a null occoured. I don't like stored procedures, or functions in any programming language that accepts many parameters, and I avoid the as much as I can. Those things are hard to debug and write (hmm, what paraemeters should go here ? :-( ) and they hinder the readability of the code. As for null values, I don't use them as well, I work with different databases and each one has their quirks about null. Basiclly null is undefined and should stay that way. Usually I decide on an invalid value (such as -1) and pass it to the function. I also love the fact that you can't drop foreign keys, modify columns, drop columns without rebuilding the entire table, etc. We use it here, but it makes me want to pull my hair out. If someone would just combine postgres and mysql, we'd have the best database in the universe. Fast and stable, with all of the RDBMS anyone could want. Well I can do all of this in mysql because there's no referential integrity. You can't drop or modify a column which is reference by a foreign key, beacuse that would break the integrity. You don't need to rebuld the table, just drop the foriegn key and off you go. But as it stands, postgres is still a bit player with an incomplete feature set. But I don't want to start a holy war here, so I'll drop it. OK. As for speed both are very fast (even when compared to commerical DB, in my tests the deafult install of Interbase outperfomed the default install of Oracle 8i about 10X, tested on P166 with 96MB and PII400 with 192MB). This only occours if you don't know how to optimize Oracle. Oracle is *very* picky about *everything*. You need index tablespaces on separate disks from the data tablespaces, and yet another one for system tablespaces. You should also have one for archive logs, redo logs, and of course your temporary tables. Setting it to threading mode is also nice for connection pooling and to stop killing your machine under heavy load. That, and the machines you've quoted are in no way powerful enough for production Oracle databases, period. You should also run oracle on some kind of Solaris/Sun combo. Raw mount points direct to the actual disks is ideal, but loopback filesystems work just as well. Remember to cluster your raid into 4 - 6 arrays of 3+ disks, too. There is no such thing as a default Oracle install, because installing oracle on a single user machine with one disk and only a little ram (yes, anything under 512 is very little for Oracle) will make Oracle look like a piece of crap. The point about Oracle is that it *lets* you do all of those optimizations, and if you're good at it, it will outperform almost any other database you throw at it. Trust me on this one. Well let me see, 1. I have several databases to choose from, Which give me the performance I need. 2. I don't have to be a DBA to manage them, I don't have to use raids to get performance and I don't have to split indexes between hard disks to get adaquete preformence. 3. I don't a supercomputer and I can use them in moderate hardware for my needs, and I don't have to dedicate plenty of RAM to them. 4. This databases are open-source and don't have linking problems. Anyone tried to install Oracle 8.0.5 on Linux (patch-O-rama for Glibc). 8i is a resource hog (JVM in the database, are they nuts ?) and i won;t touch it with a stick, 5. I can expect this databases to work for me out of the box (out of the install in this case) 6. All this databases are free and I can choose the one I want according to the task I'm facing, without paying outrages licensing fee to Oracle, and spending lots of money on unnedded hardware. Isn't the choice obvious ? Why would anyone touch Oracle - I guess it is only because of their hype and spin - much like some other company I won't mention. BTW, I was in Oracle's OPP program for quite a while, but left it when 8i came out, it was an overkill. (But I sure miss those Oracle parties, They were outrages, or as one of their representives said to me, they had lots of money the needed to spend). OK, I'll stop now, I'm running out of air, I think I'm turning blue, Help !! Help !! someone
Re: [PHP] PostgreSQL vs InterBase
On Wednesday 28 February 2001 06:19, [EMAIL PROTECTED] wrote: Arnold Gamboa wrote: I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- 1. I think Postgresql 2 and 3 I can't say which is better. Both seem to work ok - you might want to search for postgresql and persistent connections in the archives - seems they aren't perfect yet. I use both of the databases (Interbase 6.01 and PostgreSQL 7.1beta4). PostgreSQL has more features comapared to Interbase (the procedureal language is very robust and there are many datatyps to choose from. Also you can have some kind of object support in it to inherit tables for example), but it's windows implemenation is very hard (at least for me, I like to compile it). As for speed both are very fast (even when compared to commerical DB, in my tests the deafult install of Interbase outperfomed the default install of Oracle 8i about 10X, tested on P166 with 96MB and PII400 with 192MB). -- Meir Kriheli There's someone in my head, but it's not me - Pink Floyd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
On Thu, 1 Mar 2001, Meir kriheli wrote: I use both of the databases (Interbase 6.01 and PostgreSQL 7.1beta4). PostgreSQL has more features comapared to Interbase (the procedureal language is very robust and there are many datatyps to choose from. Also you can have some kind of object support in it to inherit tables for example), but it's windows implemenation is very hard (at least for me, I like to compile it). Postgres also has a nasty show-stopping bug they don't seem to want to fix. Try making a stored procedure with many parameters, and send a single null to it. I dare you. I so love having all of my other parameters, and the return value of the function turned into null because postgres can't tell where a null occoured. I also love the fact that you can't drop foreign keys, modify columns, drop columns without rebuilding the entire table, etc. We use it here, but it makes me want to pull my hair out. If someone would just combine postgres and mysql, we'd have the best database in the universe. Fast and stable, with all of the RDBMS anyone could want. But as it stands, postgres is still a bit player with an incomplete feature set. But I don't want to start a holy war here, so I'll drop it. As for speed both are very fast (even when compared to commerical DB, in my tests the deafult install of Interbase outperfomed the default install of Oracle 8i about 10X, tested on P166 with 96MB and PII400 with 192MB). This only occours if you don't know how to optimize Oracle. Oracle is *very* picky about *everything*. You need index tablespaces on separate disks from the data tablespaces, and yet another one for system tablespaces. You should also have one for archive logs, redo logs, and of course your temporary tables. Setting it to threading mode is also nice for connection pooling and to stop killing your machine under heavy load. That, and the machines you've quoted are in no way powerful enough for production Oracle databases, period. You should also run oracle on some kind of Solaris/Sun combo. Raw mount points direct to the actual disks is ideal, but loopback filesystems work just as well. Remember to cluster your raid into 4 - 6 arrays of 3+ disks, too. There is no such thing as a default Oracle install, because installing oracle on a single user machine with one disk and only a little ram (yes, anything under 512 is very little for Oracle) will make Oracle look like a piece of crap. The point about Oracle is that it *lets* you do all of those optimizations, and if you're good at it, it will outperform almost any other database you throw at it. Trust me on this one. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | "ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
From: "Shaun Thomas" [EMAIL PROTECTED] The point about Oracle is that it *lets* you do all of those optimizations, and if you're good at it, it will outperform almost any other database you throw at it. Trust me on this one. I'm sorry, I can't trust you very much on this. Having worked as a high-end Oracle tuner on a 1 terabyte database for 3 years and having recently been involved in a new DB2 app, I find DB2 to be as good if not better than Oracle in very many important ways. Not even including a cost that's about 1/4 of a highly discounted Oracle cost model for an identical HA envioronment. Mark C. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
Hi We have been using Interbase for all our php development and have not experienced problems. The oprational database is 4gigs and if we execute large queries on it we recieve sub-second responses so speed is definetly not a problem Interbase is a good way to go if you have need for a relational database and of course a bonus is that it is free :) - Original Message - From: Arnold Gamboa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 27, 2001 11:00 AM Subject: [PHP] PostgreSQL vs InterBase I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
I know this isn't one of the databases that you;re inquiring about, but we've been using MySQL (www.mysql.com) and it has been working beautifully. We have an email server with a MySQL db that's about 4 Gigs now a it's still running strong. MySQL is free and might be worth looking into :-) Arnold Gamboa wrote: I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- 209 Media http://www.209media.com> Ron Wills [EMAIL PROTECTED]> Programmer -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
Arnold Gamboa wrote: I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- 1. I think Postgresql 2 and 3 I can't say which is better. Both seem to work ok - you might want to search for postgresql and persistent connections in the archives - seems they aren't perfect yet. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
I [EMAIL PROTECTED] wrote: Can someone give me a simple example script that uses postgres session handling, that works with register_globals "off"? As a followup again, it seems what I've been running into is a bug in PHP: http://bugs.php.net/bugs.php?id=8772 http://bugs.php.net/bugs.php?id=9002 So, hopefully, someday, it will be fixed. -- Steve [EMAIL PROTECTED] Now playing: Moon and Sun Part II: North's Son (Amorphis - "Black Winter Day") -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
I [EMAIL PROTECTED] wrote: It seems the pgsql_session_write() function is not even being invoked. Here it is: As a followup, it seems that turning register_globals "on" allows the pgsql_session_write() function to be called, and my test script works if I replace $HTTP_SESSION_VARS["count"] with $count. However, my environment is such that I have register_globals "off", which seems to conflict with sessions. In fact, I even get a Undefined variable: HTTP_SESSION_VARS error for the line $HTTP_SESSION_VARS["count"]++; Can someone give me a simple example script that uses postgres session handling, that works with register_globals "off"? Thanks, -- Steve [EMAIL PROTECTED] "And when you walk in golden halls, you get to keep the gold that falls" -- Black Sabbath, "Heaven and Hell" -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Postgresql session handling
PHP 4.04pl1, PostgresQL 7.0.3 on a RedHat 6.2 system.
I'm trying to get session data to be stored in a
postgres table. I'm using the pgsql session handler from
http://www.csh.rit.edu/~jon/projects/php/pgsql_session_handler/, and I
think I've got it set up correctly, but I thought I'd ask here first.
With the following test script
+-
| ? include("pgsql_session_handler.inc"); ?
| ?
| session_start();
| session_register("count");
| $HTTP_SESSION_VARS[count]++;
| ?
| html
| head
| titleTest page/title
| /head
| body
| Hello! You've been here ?= $HTTP_SESSION_VARS[count]; ? times!br
|
| ? print "To continue, A HREF=\"test-session.php\"click here/a"; ?
|
| /body
| /html
+-
I can see php querying the database for session data (in the postgres
logs), but never writing session data to the database. Thus, the
counter is always '1'.
Any ideas where I'm going wrong? Why wouldn't session data get
written to the database?
Thanks,
--
Steve [EMAIL PROTECTED]
Now playing: Five Magics
(Megadeth - "Rust In Peace")
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
The writing of the session data occurs *after* the server-browser HTTP
connection is cut.
If you have any error-reporting happening in your session_write function,
you won't see it.
Alter that function to log errors to a file or something.
--
Visit the Zend Store at http://www.zend.com/store/
Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm
- Original Message -
From: Bolt Thrower [EMAIL PROTECTED]
Newsgroups: php.general
Sent: Tuesday, February 20, 2001 12:45 PM
Subject: [PHP] Postgresql session handling
PHP 4.04pl1, PostgresQL 7.0.3 on a RedHat 6.2 system.
I'm trying to get session data to be stored in a
postgres table. I'm using the pgsql session handler from
http://www.csh.rit.edu/~jon/projects/php/pgsql_session_handler/, and I
think I've got it set up correctly, but I thought I'd ask here first.
With the following test script
+-
| ? include("pgsql_session_handler.inc"); ?
| ?
| session_start();
| session_register("count");
| $HTTP_SESSION_VARS[count]++;
| ?
| html
| head
| titleTest page/title
| /head
| body
| Hello! You've been here ?= $HTTP_SESSION_VARS[count]; ? times!br
|
| ? print "To continue, A HREF=\"test-session.php\"click here/a"; ?
|
| /body
| /html
+-
I can see php querying the database for session data (in the postgres
logs), but never writing session data to the database. Thus, the
counter is always '1'.
Any ideas where I'm going wrong? Why wouldn't session data get
written to the database?
Thanks,
--
Steve [EMAIL PROTECTED]
Now playing: Five Magics
(Megadeth - "Rust In Peace")
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
"Richard Lynch" [EMAIL PROTECTED] wrote:
The writing of the session data occurs *after* the server-browser HTTP
connection is cut.
If you have any error-reporting happening in your session_write function,
you won't see it.
Alter that function to log errors to a file or something.
It seems the pgsql_session_write() function is not even being invoked.
Here it is:
+-
| function pgsql_session_write($key, $val)
| {
| /* debugging */
| $fp = fopen("/tmp/phpdebugwrite","w") or die ("can't open file");
| fwrite($fp, "HERE");
| fclose($fp);
| /* end debug */
|
| global $pgsql_session_handle, $pgsql_session_table;
|
| $key = addslashes($key);
| $val = addslashes($val);
| $now = time();
|
|
|
| /*
| * Delete any existing data for this session and then insert a new row
| * containing the current session data, all in a single transaction.
| * This should prevent collisions between multiple session instances.
| *
| * Thanks to "Will Fitzgerald" [EMAIL PROTECTED].
| */
| $query = 'begin; ';
| $query .= "delete from $pgsql_session_table where session_id = '$key'; ";
| $query .= "insert into $pgsql_session_table values('$key', $now, '$val'); ";
| $query .= 'commit;';
| $result = @pg_exec($pgsql_session_handle, $query);
|
| $ret = (pg_cmdtuples($result) == 0);
| pg_freeresult($result);
|
| return ($ret);
| }
+-
I've added the debugging statements at the top. Can you see anything
wrong with this function? At the end of the include file in which
this appears, is the session_set_save_handler() call:
session_set_save_handler(
'pgsql_session_open',
'pgsql_session_close',
'pgsql_session_read',
'pgsql_session_write',
'pgsql_session_destroy',
'pgsql_session_gc'
);
Thanks,
--
Steve [EMAIL PROTECTED]
"And when you walk in golden halls, you get to keep the gold that falls"
-- Black Sabbath, "Heaven and Hell"
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
"Richard Lynch" [EMAIL PROTECTED] wrote:
The writing of the session data occurs *after* the server-browser HTTP
connection is cut.
If you have any error-reporting happening in your session_write
function,
you won't see it.
Alter that function to log errors to a file or something.
It seems the pgsql_session_write() function is not even being invoked.
Here it is:
+-
| function pgsql_session_write($key, $val)
| {
| /* debugging */
| $fp = fopen("/tmp/phpdebugwrite","w") or die ("can't open file");
| fwrite($fp, "HERE");
| fclose($fp);
| /* end debug */
Why don't you just use error_log() function and write errors to a file?
|
| global $pgsql_session_handle, $pgsql_session_table;
|
| $key = addslashes($key);
| $val = addslashes($val);
| $now = time();
|
|
|
| /*
| * Delete any existing data for this session and then insert a new
row
| * containing the current session data, all in a single transaction.
| * This should prevent collisions between multiple session instances.
| *
| * Thanks to "Will Fitzgerald" [EMAIL PROTECTED].
| */
| $query = 'begin; ';
| $query .= "delete from $pgsql_session_table where session_id =
'$key'; ";
| $query .= "insert into $pgsql_session_table values('$key', $now,
'$val'); ";
| $query .= 'commit;';
| $result = @pg_exec($pgsql_session_handle, $query);
I've seen this kind of implmentatin
delete
insert
IMHO, 2 sql operation that requires locks for session handling is just a
waste
of resources. (delete requires higher lock level than select, transaction
needs more resources, for each delete, insert requires fsync() and requires
more disk head movements, etc, etc)
Unless you would like to implement some kind of session key validation to
session handlers, simple
select
insert or update ...
may give better performance with many web servers and users. (may not make
any significant improvement, though. Disabling PostgreSQL's fsync() for
session db would much better result. Just FYI)
|
| $ret = (pg_cmdtuples($result) == 0);
| pg_freeresult($result);
|
| return ($ret);
| }
+-
I've added the debugging statements at the top. Can you see anything
wrong with this function? At the end of the include file in which
this appears, is the session_set_save_handler() call:
You will not get output from die('.') ( or any print/echo) in session
handlers. Just use error_log() or implement user defined error handler
so that you can trigger error to display session handler errors.
my example pg_session_write() looks like:
function pg_session_write ($session_id, $session_data) {
global $db_session, $HTTP_SERVER_VARS, $HTTP_COOKIE_VARS;
if (!$db_session) {
error_log("session_write(): Cannot connect to database.",0);
}
if (strlen($session_data) intval(SESS_DATA_MAX)) {
error_log('Session data too large. Unable to update session data for
session '. $session_id, 0);
}
if (strlen($session_id) != 32) {
error_log("session_write(): Invalid Session ID",0);
return 0;
}
$session_id = addslashes($session_id);
$session_data = addslashes($session_data);
$uid = isset($HTTP_COOKIE_VARS['t_uid']) ?
addslashes($HTTP_COOKIE_VARS['t_uid']) : '';
$query = 'SELECT session_id, i_session_counter FROM '. SESS_TABLE ." WHERE
session_id = '$session_id'";
$result_id = pg_exec($db_session,$query);
$session_exist = pg_numrows($result_id);
if ($session_exist == 0) {
// $query = 'INSERT INTO '. SESS_TABLE ." (session_id, i_time_created,
i_last_active, t_remote_addr, t_session_data) VALUES ('$session_id', ".
time() .", ". time() .", '".
isset($HTTP_ENV_VARS['HTTP_X_FORWARDED_FOR'])?$HTTP_ENV_VARS['HTTP_X_FORWARDED_FOR']:$HTTP_SERVER_VARS['REMOTE_ADDR']
."', '$session_data')";
$query = 'INSERT INTO '. SESS_TABLE ." (session_id, i_time_created,
i_last_active, t_uid, t_remote_addr, t_session_data) VALUES ('$session_id',
". time() .", ". time() .", '$uid', '". $HTTP_SERVER_VARS['REMOTE_ADDR']
."', '$session_data')";
}
else {
$rec = pg_fetch_array($result_id,0,PGSQL_ASSOC);
$query = 'UPDATE '. SESS_TABLE ." SET t_session_data =
'$session_data',
i_last_active = ". time() .", t_uid = '$uid' , i_session_counter = ".
intval(++$rec['i_session_counter']) ." WHERE session_id = '$session_id'";
}
$rows_affected = pg_cmdtuples(pg_exec($db_session,$query));
//error_log($query, 0);
if (!$rows_affected) {
error_log('session_write(): Failed to INSERT or UPDATE session.',0);
}
return $rows_affected;
}
session_set_save_handler(
'pgsql_session_open',
Re: [PHP] PostgreSQL + PHP + SQL warning messages
El Jue 25 Ene 2001 16:36, Evelio Martinez escribi: Hi! Is there any way to get the equivalent sqlca.sqlcode value of informix in PostgreSQL from php ? I would like to use the sqlcode to print the messages in Spanish. The problem is that informix puts in an array (sort of) all the info about the last query. What sort of information would you like? Last insert? You have an pg_getlastoid() function. Hope it helps. Saludos...:-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martn Marqus email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
