Re: [GENERAL] PHP and Postgres arrays
Hannes Dorbath wrote: Yannick Warnier wrote: I thought about it, but it's not very portable if you want to ship a PHP application. But I admit I will fall back to that solution if I cannot find any other (considering PostgreSQL might be the only open-source database to offer in-fields arrays anyway). You can use the build in function array_to_string: http://www.postgresql.org/docs/8.3/static/functions-array.html Then use implode(YOUR_DELIMITER, YOUR_STRING) to get an array. Another solution would be to use the MDB2 (or similar) PEAR wrapper. b ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PHP and Postgres arrays
On Fri, 18 Jan 2008 23:01:09 +0100 Yannick Warnier [EMAIL PROTECTED] wrote: Hello, I've been searching (not very thoroughly, but still) for a way to get Postgres arrays right into PHP arrays, but it seems that the Postgres driver simply doesn't allow that. What about using a stored procedure to return the array as a joined row? -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PHP and Postgres arrays
Le vendredi 18 janvier 2008 à 23:10 +0100, Ivan Sergio Borgonovo a écrit : On Fri, 18 Jan 2008 23:01:09 +0100 Yannick Warnier [EMAIL PROTECTED] wrote: Hello, I've been searching (not very thoroughly, but still) for a way to get Postgres arrays right into PHP arrays, but it seems that the Postgres driver simply doesn't allow that. What about using a stored procedure to return the array as a joined row? I thought about it, but it's not very portable if you want to ship a PHP application. But I admit I will fall back to that solution if I cannot find any other (considering PostgreSQL might be the only open-source database to offer in-fields arrays anyway). Yannick ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PHP and Postgres arrays
Hello, I've been searching (not very thoroughly, but still) for a way to get Postgres arrays right into PHP arrays, but it seems that the Postgres driver simply doesn't allow that. The only solution seems to use some user-defined functions[1] to split the result of a query (a string) into a PHP array. Some Perl-related readings [2] seem to say that Postgres actually provides a string, loosing the possibility to get it as an array, but these comments date back to 2005. Any chance someone around here might tell me more about this and possibly give me some better way to get those arrays than copy-pasting the user-proposed PHP functions? Thanks, Yannick Warnier [1] http://www.php.net/manual/en/ref.pgsql.php#58660 [2] http://www.perlmonks.org/?node_id=474518 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PHP and Postgres arrays
Yannick Warnier wrote: I thought about it, but it's not very portable if you want to ship a PHP application. But I admit I will fall back to that solution if I cannot find any other (considering PostgreSQL might be the only open-source database to offer in-fields arrays anyway). You can use the build in function array_to_string: http://www.postgresql.org/docs/8.3/static/functions-array.html -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] php and postgres - too many queries too fast?
Tom Hart wrote: It turns out that for some reason it didn't like to UPDATE when I was using a text type field (specifically an empty text field) in the WHERE clause. To remedy this, I instructed PHP to not use a field in the WHERE clause if the destination type was 'text', and now we're working beautifully (2.405 seconds to run the script through 1566 rows, running updates on 1563 of them). Now I just need to figure out what's going on with those 3 rogue rows. Its almost certainly not an empty text field, but a NULL one, see Jeff's reply to your original message. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] php and postgres - too many queries too fast?
On Mon, 2007-11-05 at 17:18 -0500, Tom Hart wrote: UPDATE table SET is_ok = 'TRUE' WHERE var1 = value1 AND var2 = value2 AND var3 = value3. As others have said, you need to narrow the problem down a bit more before we can provide useful help. However, a wild guess might be that some of your fields contain NULLs. In SQL, NULL=NULL is _not_ true (more specifically, it is NULL). To see what I mean, do SELECT 1 WHERE NULL=NULL, it will return 0 rows. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] php and postgres - too many queries too fast?
andy wrote: Tom Hart wrote: [snip] OK, enough of the background, here's my issue. For some lovely reason, even though my script reports running an UPDATE query 1563 times (out of 1566 rows), only 316 rows have is_ok set to TRUE. I've tried a few times, changing this and that, and it always updates those 316 rows (no reason for this, the data is actually really good when it comes in, I'm just trying to build an extra layer of assuredness). Of particular note, I'm trying to keep the script fairly uniform and work for all our tables, so the UPDATE statement looks something like UPDATE table SET is_ok = 'TRUE' WHERE var1 = value1 AND var2 = value2 AND var3 = value3. Thomas R. Hart II [EMAIL PROTECTED] Have you run one of these queries via psql or something other than php? I doubt its a too many too fast thing. I'd guess a logic error someplace. Why 1563 queries? Can you get the row's modified per query? If you're tables looks like: var1 | var2 | var3 a | b | a a | b | c Would you fire of two query's like: UPDATE table SET is_ok = 'TRUE' WHERE var1 = 'a' AND var2 = 'b' and var3 = 'a; UPDATE table SET is_ok = 'TRUE' WHERE var1 = 'a' AND var2 = 'b' and var3 = 'c; if so, do you generate the update's on the fly? -Andy ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match I thought I had run the queries through psql (it's been a long one :-) and when I tried to verify, I was able to find my problem (yes, I am an idiot). It turns out that for some reason it didn't like to UPDATE when I was using a text type field (specifically an empty text field) in the WHERE clause. To remedy this, I instructed PHP to not use a field in the WHERE clause if the destination type was 'text', and now we're working beautifully (2.405 seconds to run the script through 1566 rows, running updates on 1563 of them). Now I just need to figure out what's going on with those 3 rogue rows. Sorry I hadn't checked all the bases thoroughly, but now they definitely are belong to us. Thanks for the help and have a good night. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] php and postgres - too many queries too fast?
Hey everybody. I'm running postgresql 8.2 on a windows 2k3 server machine. I have a table (two tables actually, in table and table_import format). The _import table has all text type fields, while the main table has datatypes defined. I wrote a PHP script that checks the various type fields (e.g. integer is a number, date is a date, etc.) and sets a bool flag is_ok to true for the row if the data all checks out. The script gets a dump of the data from a SELECT * statement, then takes each row, verifies the various data fields (successfully, I've tested) and sets a variable baddata. At the end of the checking, if baddata still equals 0, then it crafts an UPDATE statement to change the value of is_ok. There are a relatively small amount of rows (~1500, small time to you guys I'm sure), and the script runs fairly fast. OK, enough of the background, here's my issue. For some lovely reason, even though my script reports running an UPDATE query 1563 times (out of 1566 rows), only 316 rows have is_ok set to TRUE. I've tried a few times, changing this and that, and it always updates those 316 rows (no reason for this, the data is actually really good when it comes in, I'm just trying to build an extra layer of assuredness). Of particular note, I'm trying to keep the script fairly uniform and work for all our tables, so the UPDATE statement looks something like UPDATE table SET is_ok = 'TRUE' WHERE var1 = value1 AND var2 = value2 AND var3 = value3. for every field in the record (I would have it base it on the primary key, but the field names and locations are different for each table). Is it possible that I'm trying to run too many queries at once (or rather rapid succession)? I've tried encapsulating the queries in a BEGIN .. COMMIT transaction which improved my speed quite a bit, but it's still updating only those rows. I know that it's entirely possible that the problem lies in the PHP, or the network, or the web server configuration, or the moon phase, but is there anything here that jumps out at anybody as a possible cause? TIA Thomas R. Hart II [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] php and postgres - too many queries too fast?
On 11/5/07, Tom Hart [EMAIL PROTECTED] wrote: Hey everybody. I'm running postgresql 8.2 on a windows 2k3 server machine. I have a table (two tables actually, in table and table_import format). The _import table has all text type fields, while the main table has datatypes defined. I wrote a PHP script that checks the various type fields (e.g. integer is a number, date is a date, etc.) and sets a bool flag is_ok to true for the row if the data all checks out. The script gets a dump of the data from a SELECT * statement, then takes each row, verifies the various data fields (successfully, I've tested) and sets a variable baddata. At the end of the checking, if baddata still equals 0, then it crafts an UPDATE statement to change the value of is_ok. There are a relatively small amount of rows (~1500, small time to you guys I'm sure), and the script runs fairly fast. OK, enough of the background, here's my issue. For some lovely reason, even though my script reports running an UPDATE query 1563 times (out of 1566 rows), only 316 rows have is_ok set to TRUE. I've tried a few times, changing this and that, and it always updates those 316 rows (no reason for this, the data is actually really good when it comes in, I'm just trying to build an extra layer of assuredness). Of particular note, I'm trying to keep the script fairly uniform and work for all our tables, so the UPDATE statement looks something like UPDATE table SET is_ok = 'TRUE' WHERE var1 = value1 AND var2 = value2 AND var3 = value3. Can you create a repeatable test version of this? I.e. have one row that does and one row that doesn't update? And give it to the list as a .sql file to be loaded along with the queries you're using? I understand the general idea of what you're saying, but I'm afraid there's not enough detail in your post to really help. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] php and postgres - too many queries too fast?
Tom Hart wrote: [snip] OK, enough of the background, here's my issue. For some lovely reason, even though my script reports running an UPDATE query 1563 times (out of 1566 rows), only 316 rows have is_ok set to TRUE. I've tried a few times, changing this and that, and it always updates those 316 rows (no reason for this, the data is actually really good when it comes in, I'm just trying to build an extra layer of assuredness). Of particular note, I'm trying to keep the script fairly uniform and work for all our tables, so the UPDATE statement looks something like UPDATE table SET is_ok = 'TRUE' WHERE var1 = value1 AND var2 = value2 AND var3 = value3. Thomas R. Hart II [EMAIL PROTECTED] Have you run one of these queries via psql or something other than php? I doubt its a too many too fast thing. I'd guess a logic error someplace. Why 1563 queries? Can you get the row's modified per query? If you're tables looks like: var1 | var2 | var3 a | b | a a | b | c Would you fire of two query's like: UPDATE table SET is_ok = 'TRUE' WHERE var1 = 'a' AND var2 = 'b' and var3 = 'a; UPDATE table SET is_ok = 'TRUE' WHERE var1 = 'a' AND var2 = 'b' and var3 = 'c; if so, do you generate the update's on the fly? -Andy ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PHP with Postgres
El dia 04-Aug-99 en Kevin Heflin va escriure : Go to www.php.net in the online manual, it shows all the pg_ functions of php. If you are looking for an acutal book, I've recently bought a PHP programming books from amazon.com, I didn't find that it went into any more detail than the online documentation. But does have some explanation of basic programming for the beginner. The specific link for the postgres functions can be found at: http://www.php.net/manual/ref.pgsql.php3 Kevin Oh ,yes !! Thanks Kevin Simeó Reig [EMAIL PROTECTED] Barcelona (SPAIN) -
