[PHP-DB] Re: large csv text file import into postgresql via PHP
I've written and automated system that does the following:: similar problem ** every evening a cron job executes on LAN linux and spools of prices into a csv text file ** The file is ftp/uploaded to remote server on web and a database entry created to show file ** cron job on remote server checks database every half hour for new entries ** if new entry exists then file is imported into database ** all uploaded files over a week olde are deleted hope it helps pete Dave wrote: we have a number of csv dumps that occasionally have to be used to update tables in a postgres database... normally this is done by uploading the file and whomever running a php based parser which opens the file into an array, does a split on the comma, then executes an insert or update to the database with the split array values. we have a new upload that involves a 143mb file, compared to previous upload sizes of 10k to 150k. Is there a more efficient way to handle this rather than having PHP load the entire file into an array (which would have to be in memory during the operation, correct?). thanks Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: large csv text file import into postgresql via PHP
oops forgot to mention reson for cron... Timeouts processing 143mb can take a long time. also you need to use fgets to read the file one line at a time.. pete Dave wrote: we have a number of csv dumps that occasionally have to be used to update tables in a postgres database... normally this is done by uploading the file and whomever running a php based parser which opens the file into an array, does a split on the comma, then executes an insert or update to the database with the split array values. we have a new upload that involves a 143mb file, compared to previous upload sizes of 10k to 150k. Is there a more efficient way to handle this rather than having PHP load the entire file into an array (which would have to be in memory during the operation, correct?). thanks Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Incorrect characters returned when querying database
Hi all, I posted this on the php-i18n list already, but I thought it might be relevant here too, so here goes. I am currently using PHP 4.3.1 with Microsoft Access 2000 in Windows 2000 with IIS web server to store content for a multilingual website. I am having two different but related problems. Problem 1: When Greek characters are pulled from the database and displayed on the web, they appear completely garbled. The same goes for accented characters in other languages such as French, Spanish, etc. It seems that any characters outside the standard ASCII range are not displayed properly. To see an example of what should be displayed and how it is actually being displayed, go to http://www.electonline.org/examples/example1.php Source of that PHP page is at http://www.electonline.org/examples/example1.php.txt Can someone tell me how to display the characters correctly? Problem 2: Greek characters entered into a form (with charset attribute set to 'utf-8') does not get added properly to the database. This screenshot demonstrates how the above Greek string appears after being added to the database via a form: http://www.electonline.org/examples/db1.gif However, when this garbled string is pulled back out from the database and displayed on the webpage, it appears perfectly! I would like to know how to store the string correctly in the database. My theories as to why these connected problems are occuring are: - Access 2000 is UTF-16 and the strings being added to the database are UTF-8. I have tried using iconv and mb_convert_encoding but to no avail. - PHP can't handle the strings coming from the database because they are in UTF-16 format. Other info: - The 'charset' attribute is set to 'UTF-8'. The characters appear perfect when viewed in Access 2000. - The connection to the database is being made by a system DSN. - mbstring support is enabled. - In php.ini, I have set mbstring.internal_encoding to UTF-8 and set mbstring.encoding_translation to On. Any suggestions you might have would be greatly appreciated. Thanks, Michael -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Authentication using NTLM
Hi there, How can I authenticate users against the database using NTLM instead of Basic Authentication? Instead of manually logging in, each user's NT account will automatically allow or deny access. Is there somewhere in the php.ini file where I have to enable the NTLM module? Here's my environment: Windows 2000 Server Service Pack 2 Apache/2.0.47 (Win32) mod_perl/1.99_10-dev Perl/v5.8.0 mod_python/3.0.3 Python/2.2.2 PHP/4.3.3 mysql 3.23.49 Thanks in advance, Mark -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to post code and avoid spam filter in Yahoo?
I keep getting Spam warnings on this list whenever I post code. Is there anyway around this? I'm not sure my questions can be answered without showing the code that I am having trouble with. If not, then I will have to get my boss to subscribe and post from his ISP account. Karen - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
RE: [PHP-DB] Re: Email Application
Try this function send_html_mail($myname, $myemail, $contactname, $contactemail, $subject, $message) { $headers = MIME-Version: 1.0\n; $headers .= Content-type: text/html; charset=iso-8859-1\n; $headers .= X-Priority: 3\n; $headers .= X-MSMail-Priority: Normal\n; $headers .= X-Mailer: PHP/.MIME-Version: 1.0\n; $headers .= From: $myname $myemail\n; return(mail($contactname $contactemail, $subject, $message,$headers)); } -Original Message- From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 6:16 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Re: Email Application From: [EMAIL PROTECTED] I am not so new in PHP but I have a problem. I am trying to create a webmail program for my penpal site. I cant retrieve and desplay e-mails from my mail server. Any one with ideas? I have an idea. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql, finding the last record
is there any quick way of selecting the 'last' row in a mysql table?? i could write some messy code but id prefer an elegant solution. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to post code and avoid spam filter in Yahoo?
On Saturday 27 September 2003 00:04, Karen Resplendo wrote: I keep getting Spam warnings on this list whenever I post code. Is there anyway around this? I'm not sure my questions can be answered without showing the code that I am having trouble with. You're not doing anything wrong. It's a fellow list subscriber ([EMAIL PROTECTED]) who has an incorrectly configured SpamAssassin setup and is churning out these misleading warnings. If not, then I will have to get my boss to subscribe and post from his ISP account. No need. Just ignore those 'warnings'. Your posts are coming through fine. Could the List Moderator chuck this guy, [EMAIL PROTECTED], off the list? Or at least ask him to configure his mail system properly. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Don't abandon hope: your Tom Mix decoder ring arrives tomorrow. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql, finding the last record
On Saturday 27 September 2003 01:04, John Ryan wrote: is there any quick way of selecting the 'last' row in a mysql table?? i could write some messy code but id prefer an elegant solution. mysql_num_rows() mysql_data_seek() -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Sometimes when you look into his eyes you get the feeling that someone else is driving. -- David Letterman */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] fgetcsv() only returns half of the rows
On Friday 26 September 2003 23:46, Karen Resplendo wrote: There are 113 rows in my comma delimited text file and the code below only displays 62 of them. It executes very quickly so I'm not thinking there is a time out problem. Any ideas? //here is URL with fgetcsv() explanation: http://us3.php.net/manual/en/function.fgetcsv.php $uploadthis = 'C:/AsciiTestJohn/OR100012-09252003.txt'; $row = 1; $handle = fopen ($uploadthis,r); rewind($handle); while ($fieldarray = fgetcsv ($handle, 300, ,)) { $fieldcount = count ($fieldarray); print p $fieldcount fields in line $row: br\n; $row++; for ($c=0; $c $fieldcount; $c++) { print $fieldarray[$c] . br\n; } Your logic here seems a bit odd. In your WHILE clause you're reading chunks of 300 chars at a time. Then in the code below, you're reading in another chunk of 300 chars. Thus each iteration of the WHILE-loop reads in 600 chars from the file. Is that your real intention? $buffer = fgets($handle,300); $buffer = str_replace(\,,$buffer); print $buffer.br; } //end of while loop fclose ($handle); -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* ...there can be no public or private virtue unless the foundation of action is the practice of truth. - George Jacob Holyoake */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] fgetcsv() only returns half of the rows
Does that mean that my length setting has to be exactly the length of the row? I don't know this because it is a comma delimited file, not a space delimited. The function description says: The length parameter must be greater than the longest line to be found in the CSV file (allowing for trailing line-end characters). I removed this block where I do an fgets() and then I get all rows displayed. Also, I was mistaken about total rows. There are 123 and I only display 62, which is half, so I'm thinking something is being incremented twice instead of once. What should I set the length at?: $buffer = fgets($handle,300); $buffer = str_replace(\,,$buffer); print $buffer.br; Jason Wong [EMAIL PROTECTED] wrote: On Friday 26 September 2003 23:46, Karen Resplendo wrote: There are 113 rows in my comma delimited text file and the code below only displays 62 of them. It executes very quickly so I'm not thinking there is a time out problem. Any ideas? //here is URL with fgetcsv() explanation: http://us3.php.net/manual/en/function.fgetcsv.php $uploadthis = 'C:/AsciiTestJohn/OR100012-09252003.txt'; $row = 1; $handle = fopen ($uploadthis,r); rewind($handle); while ($fieldarray = fgetcsv ($handle, 300, ,)) { $fieldcount = count ($fieldarray); print $fieldcount fields in line $row: \n; $row++; for ($c=0; $c $fieldcount; $c++) { print $fieldarray[$c] . \n; } Your logic here seems a bit odd. In your WHILE clause you're reading chunks of 300 chars at a time. Then in the code below, you're reading in another chunk of 300 chars. Thus each iteration of the WHILE-loop reads in 600 chars from the file. Is that your real intention? $buffer = fgets($handle,300); $buffer = str_replace(\,,$buffer); print $buffer. ; } //end of while loop fclose ($handle); -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* ...there can be no public or private virtue unless the foundation of action is the practice of truth. - George Jacob Holyoake */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [PHP-DB] fgetcsv() only returns half of the rows
On Saturday 27 September 2003 01:24, Karen Resplendo wrote: Does that mean that my length setting has to be exactly the length of the row? No. I don't know this because it is a comma delimited file, not a space delimited. The function description says: The length parameter must be greater than the longest line to be found in the CSV file (allowing for trailing line-end characters). So set it to [max line length]+10. I removed this block where I do an fgets() and then I get all rows displayed. Also, I was mistaken about total rows. There are 123 and I only display 62, which is half, so I'm thinking something is being incremented twice instead of once. Exactly, that block of code below is reading in another line. What is it's purpose anyway? What are you trying to achieve? What should I set the length at?: $buffer = fgets($handle,300); $buffer = str_replace(\,,$buffer); print $buffer.br; -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* The wise man seeks everything in himself; the ignorant man tries to get everything from somebody else. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql, finding the last record
if you are seeking for the last in autoincrement field, in your select use this: select max(field) from table; if is other, well, i dont remember :D Regards Nilson John Ryan dijo: is there any quick way of selecting the 'last' row in a mysql table?? i could write some messy code but id prefer an elegant solution. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php - Joe Nilson Zegarra Galvez Sistemas Kola Real Telefax : 0051-54-256658 Movil: 0051-54-9603244 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] stripping carrige returns with SQL
I have a query that is pulling user comments, supplied by via web internface and creating a text file out of them. In these comments are all sorts of carrige returns. I've tried stripping them out with the following queries, but once imported into excel, the carrige returns are still there. so obviously i'm doing something wrong I tried doing with the ASCII number .(no idea if this is valid) SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, ASCII(10),ASCII(32)) FROM `Comment` INNER JOIN Cat egory ON Comment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_Unit ON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Also with the escaped charatcer. SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, '\n',' ') FROM `Comment` INNER JOIN Category ON Co mment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_UnitON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Is REPLACE the right SQL Function to use? is there a better way to determine this character? Thanks Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Need some MySQL Optimization suggestions - uber slow queries
I have a page that when all said and done is super slow. The queries are running a bunch of JOINs and SORTs and WHEREs. I recently implemented the my-large.cnf file in hopes it would help out the situation, but the change appears negligible. Does anyone have any suggestions for speeding up the query process...a lot? Thanks, Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need some MySQL Optimization suggestions - uber slow queries
On Friday 26 September 2003 22:58, Matt Babineau wrote: I have a page that when all said and done is super slow. The queries are running a bunch of JOINs and SORTs and WHEREs. I recently implemented the my-large.cnf file in hopes it would help out the situation, but the change appears negligible. Does anyone have any suggestions for speeding up the query process...a lot? Try asking on the mysql list? -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* There is no grief which time does not lessen and soften. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Selection problem
Hello, I'm novice in php and mysql utilization so... Here is my problem: I have a form with some fields (for example, 2: field1 and field2) My mysql database contains for example 1 table (named for example SQLTABLE) with 3 fields: ID (auto_increment), AA, BB. On my form, field1 is represented in HTML by a SELECT SIZE=1... and returns values of AA in selected in SQLTABLE. I want when I choose a value with the selection menu in field1 of the form, that the BB corresponding value of the selected AA automatically appears in field2 of my form without submitting my page, without loosing informations already encoded in the page. (An application could be a customer subsciption form, where you have to enter your name, adress, ZIP, town, for a determinated country... and you can choose the zip code in a selection menu and automatically the corresponding town is shown next to it before subitting the form...) I suppose that a lot of people can help me Thanks... Stéphane, Belgium -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Selection problem
If I understand your problem correctly, it sounds like you need to get all of the values for the two select objects (which you can do with PHP, of course) then use JavaScript to handle the CLIENT SIDE update process between the two objects. If that sounds like what you're looking for, you can find numerous tutorials and code samples on the web. Just search for javascript on google and you should find a number of sites with script repositories. Pick a site and look to see what they have to satisfy your requirements. Hope that helps. Rich -Original Message- From: Stiphane RIVEZ [mailto:[EMAIL PROTECTED] Sent: Friday, September 26, 2003 2:12 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [PHP-DB] Selection problem Hello, I'm novice in php and mysql utilization so... Here is my problem: I have a form with some fields (for example, 2: field1 and field2) My mysql database contains for example 1 table (named for example SQLTABLE) with 3 fields: ID (auto_increment), AA, BB. On my form, field1 is represented in HTML by a SELECT SIZE=1... and returns values of AA in selected in SQLTABLE. I want when I choose a value with the selection menu in field1 of the form, that the BB corresponding value of the selected AA automatically appears in field2 of my form without submitting my page, without loosing informations already encoded in the page. (An application could be a customer subsciption form, where you have to enter your name, adress, ZIP, town, for a determinated country... and you can choose the zip code in a selection menu and automatically the corresponding town is shown next to it before subitting the form...) I suppose that a lot of people can help me Thanks... Stephane, Belgium -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] fgetcsv() only returns half of the rows
What I am trying to do: lab uploads txt file php validates file, row by row, field by field (against database) I display bad rows and reasons in red and reject file I've figured out a different way to display the whole row by just printing the array with , instead of br/n Just wondering why the row pointer was getting incremented and now I think I know why: a call to both fgetcsv() and fget() will each increment the row pointer by one. No? So, taking this out leaves only fgetcsv() to increment the rows in the text file it is looking at: /*$buffer = fgets($handle,300); $buffer = str_replace(\,,$buffer); print $buffer.br;*/ Warning: more on this later :) Jason Wong [EMAIL PROTECTED] wrote: On Saturday 27 September 2003 01:24, Karen Resplendo wrote: Does that mean that my length setting has to be exactly the length of the row? No. I don't know this because it is a comma delimited file, not a space delimited. The function description says: The length parameter must be greater than the longest line to be found in the CSV file (allowing for trailing line-end characters). So set it to [max line length]+10. I removed this block where I do an fgets() and then I get all rows displayed. Also, I was mistaken about total rows. There are 123 and I only display 62, which is half, so I'm thinking something is being incremented twice instead of once. Exactly, that block of code below is reading in another line. What is it's purpose anyway? What are you trying to achieve? What should I set the length at?: $buffer = fgets($handle,300); $buffer = str_replace(\,,$buffer); print $buffer. ; -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* The wise man seeks everything in himself; the ignorant man tries to get everything from somebody else. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
[PHP-DB] MSSQL connection seems abandoned
Using PHP 4.3.1 MSSQL 7.0 Executing a Stored Procedure through the function mssql_query that returns a message of: DELETE statement conflicted with COLUMN REFERENCE constraint... The statement has been terminated. causes the link to be unusable in executing function mssql_query again. Sql Server still shows the connection as sleeping awaiting command. Does anyone have ideas or a solution to this? Thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] stripping carrige returns with SQL
If you are trying to replace the carriage return it needs to be \r instead of \ n in your query. Also it may not work because in my experience the replace function under mysql does not replace them in the db but in the query results. If you are looking to replace them permanently you will need a script to do that. I have one if that is what you want to do. David On Fri, 2003-09-26 at 12:50, [EMAIL PROTECTED] wrote: I have a query that is pulling user comments, supplied by via web internface and creating a text file out of them. In these comments are all sorts of carrige returns. I've tried stripping them out with the following queries, but once imported into excel, the carrige returns are still there. so obviously i'm doing something wrong I tried doing with the ASCII number .(no idea if this is valid) SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, ASCII(10),ASCII(32)) FROM `Comment` INNER JOIN Cat egory ON Comment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_Unit ON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Also with the escaped charatcer. SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, '\n',' ') FROM `Comment` INNER JOIN Category ON Co mment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_UnitON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Is REPLACE the right SQL Function to use? is there a better way to determine this character? Thanks Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Server 2000 problem (column_source in mssql_fetch_field function)
Hello everybody, I need help in order to get the table name of a field in SQL Server 2000. In order to get the table name of a specific column in a query, I tried to use mssql_fetch_field function. But column_source is, in fact, the column name, not the table name how it was supposed to be. In a recent past, I used mysql_fetch_field, and the table property worked fine. Now had this problem when migrating from MySql to SQL Server. Any thoughts? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need some MySQL Optimization suggestions - uber slow queries
Matt, Have you tried an EXPLAIN query yet? Start with this and send the result of the query back to the list...that would give everyone a better sense of what is happening. Cory On Fri, 2003-09-26 at 09:58, Matt Babineau wrote: I have a page that when all said and done is super slow. The queries are running a bunch of JOINs and SORTs and WHEREs. I recently implemented the my-large.cnf file in hopes it would help out the situation, but the change appears negligible. Does anyone have any suggestions for speeding up the query process...a lot? Thanks, Matt -- Cory Hicks [EMAIL PROTECTED] TRI International -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] ANNOUNCE: Beta3 of plPHP released
Hello, Beta 3 of plPHP has been released with full trigger support for PostgreSQL. PL/PHP - PHP Procedural Language Copyright 2003 Command Prompt, Inc. http://www.commandprompt.com/ +1 503 222 2783 [EMAIL PROTECTED] 1. PL/PHP language installation notes 2. PL/PHP functions and arguments 3. Data values in PL/PHP 4. Database Access from PL/PHP 5. Trigger Procedures in PL/PHP 6. Trusted and Untrusted PL/PHP 7. PL/PHP Procedure Names 8. Missing features PL/PHP is a loadable procedural language that enables you to write PostgreSQL functions in the PHP programming language. 1. PL/PHP language installation notes To install PL/PHP language Postgres DBA should have compiled shared library plphp.so in pgsql/lib/ driectory. Then the following queries should be executed: CREATE FUNCTION plphp_call_handler() RETURNS LANGUAGE_HANDLER AS 'plphp' LANGUAGE C; CREATE TRUSTED LANGUAGE plphp HANDLER plphp_call_handler; This should be done only once per each database where plphp language required. 2. PL/PHP Functions and Arguments To create a function in the PL/PHP language, use the standard syntax: CREATE FUNCTION funcname (argument-types) RETURNS return-type AS ' # plphp function body ' LANGUAGE 'plphp'; The body of the function is ordinary PHP code. Arguments and results are handled as in any other PHP subroutine. Arguments are passed in $args array and a result value is returned with return operator. For example, a function returning the greater of two integer values could be defined as: CREATE FUNCTION plphp_max (integer, integer) RETURNS integer AS ' if ($args[0] $args[1]){ return $args[0]; } else return $args[1]; ' LANGUAGE 'plphp' WITH (isStrict); Note the clause WITH (isStrict), which saves us from having to think about NULL input values: if a NULL is passed, the function will not be called at all, but will just return a NULL result automatically. In a non-strict function, if the actual value of an argument is NULL, the corresponding $args[n-1] variable will be set to an empty string (unset). To detect whether a particular argument is NULL, use the function isset(). For example, suppose that we wanted plphp_max with one null and one non-null argument to return the non-null argument, rather than NULL: CREATE FUNCTION plphp_max (integer, integer) RETURNS integer AS ' if ( !isset($args[0]) ){ if ( !isset($args[1]) ){ return; } else return $args[1]; } if (!isset($args[1])){ return $args[0]; } if ($args[0] $args[1]){ return $args[0]; } else return $args[1]; ' LANGUAGE 'plphp'; As shown above, to return a NULL value from a PL/PHP function, just execute return. This can be done whether the function is strict or not. There is currently no support for accepting composite-type arguments and returning a composite-type result value. Tip: Because the function body is passed as an SQL string literal to CREATE FUNCTION, you have to escape single quotes and backslashes within your PHP source, typically by doubling them. 3. Data values in PL/PHP The argument values supplied to a PL/PHP function's script are simply the input arguments converted to text form (just as if they had been displayed by a SELECT statement). Conversely, the return command will accept any string that is acceptable input format for the function's declared return type. So, the plphp programmer can manipulate data values as if they were just text. 4. Database Access from PL/PHP There is currently no support for access the database from the body of a PL/PHP procedure using SPI interface. But you may use standard PHP interface for the database connections. 5. Trigger Procedures in PL/PHP When a function is used in a trigger, the global associative array $_TD contains trigger-related values. $_TD[new] An associative array containing the values of the new table row for INSERT/UPDATE actions, or empty for DELETE. The array is indexed by field name. Fields that are NULL will not appear in the array! $_TD[old] An associative array containing the values of the old table row for UPDATE/DELETE actions, or empty for INSERT. The array is indexed by field name. Fields that are NULL will not appear in the array! $_TD[name] contains the trigger name. $_TD[event] contains the event as a string (INSERT, UPDATE, DELETE, or UNKNOWN). $_TD[when] contains one of BEFORE, AFTER, and UNKNOWN. $_TD[level] contains one of ROW, STATEMENT, and UNKNOWN. $_TD[relid] contains the relation ID of the table on which the trigger occurred. $_TD[relname] contains the relation name. $_TD[argc] contains the arguments count. If the trigger was called with arguments they are available in $_TD[args][0] to $_TD[args][($_TD[argc]-1)]. Example: 5.1. Just to show $_TD structure: CREATE TABLE test ( i int, v varchar ) WITH OIDS; CREATE OR REPLACE FUNCTION