[PHP-DB] Detailed Report
Hi All, I´ve got a connection, to a MySQL db, and get the following ResultSet(Category | Name | Code | City) Customers | John | A36 | New York Customers | Jason | B45 | Los Angeles Customers | Max | A36 | Paris Providers | John | A36 | London Providers | Mark | B67 | Madrid And I need the report in the following format: Customers John - A36 - New York Jason - B45 - Los Angeles Max - A36 - Paris Providers John - A36 - London Mark - B67 - Madrid Any one can help? I´m a bit stalled thx regards.
Re: [PHP-DB] Detailed Report
Not sure whether this would be such a good idea. Does anybody else have
a better one than this?
#main loop, gather distinct / unique category names
$sqlstmt = select distinct(category) as category from table;
$rs = mysql_query ($sqlstmt);
while ($row = mysql_fetch_object ($rs)) {
print $row-category . \n; # output Customers / Providers
$sqlstmt1 = select name, code, city from table;
$rs1 = mysql_query ($sqlstmt);
# inner loop, display those which category is from the main loop
while ($row1 = mysql_fetch_object ($rs1) {
print $row1-name . '-' . $row1-code . '-' . $row1-city .
\n';# output John - A36 - City
}
}
Danny wrote:
Hi All,
I´ve got a connection, to a MySQL db, and get the following
ResultSet(Category | Name | Code | City)
Customers | John | A36 | New York
Customers | Jason | B45 | Los Angeles
Customers | Max | A36 | Paris
Providers | John | A36 | London
Providers | Mark | B67 | Madrid
And I need the report in the following format:
Customers
John - A36 - New York
Jason - B45 - Los Angeles
Max - A36 - Paris
Providers
John - A36 - London
Mark - B67 - Madrid
Any one can help? I´m a bit stalled
thx
regards.
--
Best Regards,
Shahmat Dahlan
Research and Development
SAINS
Mobile: (60)16 882 6130
Office: (60)82 426 733 ext 5512
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
SV: [PHP-DB] Detailed Report
How about:
$sqlstmt1 = select category, name, code, city from table order by category;
$categry='';
while ($row1 = mysql_fetch_object ($rs1) {
if ($old_category != $row1-category) {
$ category = $row1-category;
Print($category.br);
}
Print($row1-name. '-' .$row1-code. '-' .$row1-city. \n');
}
Regards Henrik Hornemann
-Oprindelig meddelelse-
Fra: Shahmat Dahlan [mailto:[EMAIL PROTECTED]
Sendt: 27. oktober 2005 12:29
Til: Danny
Cc: php-db@lists.php.net
Emne: Re: [PHP-DB] Detailed Report
Not sure whether this would be such a good idea. Does anybody else have
a better one than this?
#main loop, gather distinct / unique category names
$sqlstmt = select distinct(category) as category from table;
$rs = mysql_query ($sqlstmt);
while ($row = mysql_fetch_object ($rs)) {
print $row-category . \n; # output Customers / Providers
$sqlstmt1 = select name, code, city from table;
$rs1 = mysql_query ($sqlstmt);
# inner loop, display those which category is from the main loop
while ($row1 = mysql_fetch_object ($rs1) {
print $row1-name . '-' . $row1-code . '-' . $row1-city .
\n';# output John - A36 - City
}
}
Danny wrote:
Hi All,
I´ve got a connection, to a MySQL db, and get the following
ResultSet(Category | Name | Code | City)
Customers | John | A36 | New York
Customers | Jason | B45 | Los Angeles
Customers | Max | A36 | Paris
Providers | John | A36 | London
Providers | Mark | B67 | Madrid
And I need the report in the following format:
Customers
John - A36 - New York
Jason - B45 - Los Angeles
Max - A36 - Paris
Providers
John - A36 - London
Mark - B67 - Madrid
Any one can help? I´m a bit stalled
thx
regards.
--
Best Regards,
Shahmat Dahlan
Research and Development
SAINS
Mobile: (60)16 882 6130
Office: (60)82 426 733 ext 5512
--
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
SV: [PHP-DB] Detailed Report
Hi,
Sorry about the stupid mistakes, should ofcourse have been:
$sqlstmt1 = select category, name, code, city from table order by category;
$rs1 = mysql_query ($sqlstmt1);
$category='';
while ($row1 = mysql_fetch_object ($rs1) {
if ($category != $row1-category) {
$category = $row1-category;
Print($category.br);
}
Print($row1-name. '-' .$row1-code. '-' .$row1-city. \n');
}
Regards Henrik Hornemann
-Oprindelig meddelelse-
Fra: Henrik Hornemann [mailto:[EMAIL PROTECTED]
Sendt: 27. oktober 2005 14:24
Til: php-db@lists.php.net
Emne: SV: [PHP-DB] Detailed Report
How about:
$sqlstmt1 = select category, name, code, city from table order by category;
$categry='';
while ($row1 = mysql_fetch_object ($rs1) {
if ($old_category != $row1-category) {
$ category = $row1-category;
Print($category.br);
}
Print($row1-name. '-' .$row1-code. '-' .$row1-city. \n');
}
Regards Henrik Hornemann
-Oprindelig meddelelse-
Fra: Shahmat Dahlan [mailto:[EMAIL PROTECTED]
Sendt: 27. oktober 2005 12:29
Til: Danny
Cc: php-db@lists.php.net
Emne: Re: [PHP-DB] Detailed Report
Not sure whether this would be such a good idea. Does anybody else have
a better one than this?
#main loop, gather distinct / unique category names
$sqlstmt = select distinct(category) as category from table;
$rs = mysql_query ($sqlstmt);
while ($row = mysql_fetch_object ($rs)) {
print $row-category . \n; # output Customers / Providers
$sqlstmt1 = select name, code, city from table;
$rs1 = mysql_query ($sqlstmt);
# inner loop, display those which category is from the main loop
while ($row1 = mysql_fetch_object ($rs1) {
print $row1-name . '-' . $row1-code . '-' . $row1-city .
\n';# output John - A36 - City
}
}
Danny wrote:
Hi All,
I´ve got a connection, to a MySQL db, and get the following
ResultSet(Category | Name | Code | City)
Customers | John | A36 | New York
Customers | Jason | B45 | Los Angeles
Customers | Max | A36 | Paris
Providers | John | A36 | London
Providers | Mark | B67 | Madrid
And I need the report in the following format:
Customers
John - A36 - New York
Jason - B45 - Los Angeles
Max - A36 - Paris
Providers
John - A36 - London
Mark - B67 - Madrid
Any one can help? I´m a bit stalled
thx
regards.
--
Best Regards,
Shahmat Dahlan
Research and Development
SAINS
Mobile: (60)16 882 6130
Office: (60)82 426 733 ext 5512
--
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Limited connections to the database. .
Norland, Martin wrote: http://www.google.com/search?q=oracle+limit+client+connections http://www.oracle.com/technology/pub/articles/php_experts/scaling_oracle _and_php.html Summary - either you're not using persistent connections, or your site is just very busy. If it's just that the site is busy, best you can do is just limit things on the website end. If the problem is no persistent connections - then you'll want to switch them on if you're doing multiple queries/page. You also want to make sure any information that doesn't need to be pulled from the database isn't (perhaps there are some global settings/etc. that change once a month, cache them globally somewhere and don't keep requesting them so often) - there may not be any such data, but then again, there may be. cheers, - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -Original Message- From: Marcos R. Cardoso [mailto:[EMAIL PROTECTED] Sent: Monday, October 17, 2005 5:37 AM To: php-db@lists.php.net Subject: [PHP-DB] Limited connections to the database. . Hello to everyone, we have been using an Apache server here at work (I work in a Library) where PHP/ASP scripts query the database very frequently (in the near future only PHP scripts will be used). The DBAs have recently complained that our webserver is performing the queries in a high speed and many times in a second. It worried them and they are asking if we could limit the connections to the database to a low number. As an example they told us that other applications in other webserver uses Tomcat to run Java, and there they managed to limit the number of active connections at the same time. I would like to know if it's possible to make something similar, and I'd like to know too if we have to configure the PHP or the Apache. Our environment is listed below: Windows 2003 Server Apache 2.0.54 PHP 4.4.0 Sun Chili!Soft ASP 3.6.2 MySQL 4.0.20a Oracle 9i (through a Oracle 8i NetClient) TIA, Marcos R. Cardoso Blumenau Brazil I was wondering if anyone thought managing multiple DBs in one script was feasible, let alone efficient? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Detailed Report
Let the code handle the separation of the products
?php
$sql = select * from table [where clause] order by cat;
//db connection here
$result = mysql_query($sql);
if (mysql_errno == 0)
{
if (mysql_num_rows($results)0)
{
$old_cat = ;
while ($rows = mysql_fetch_array($result))
{
//handle cat changes
if ($old_cat != $rows['cat'])
{
echo trtd.$rows['cat'].br /;
$old_cat = $rows['cat'];
}//end if
//echo out the data
echo $rows['name'].~.$rows['client_id'].~.$rows['city'].br /
}//wend
}else{
echo no results found;
}//end if
}//end if
?
bastien
From: Danny [EMAIL PROTECTED]
To: php-db@lists.php.net
Subject: [PHP-DB] Detailed Report
Date: Thu, 27 Oct 2005 11:32:04 +0200
Hi All,
I´ve got a connection, to a MySQL db, and get the following
ResultSet(Category | Name | Code | City)
Customers | John | A36 | New York
Customers | Jason | B45 | Los Angeles
Customers | Max | A36 | Paris
Providers | John | A36 | London
Providers | Mark | B67 | Madrid
And I need the report in the following format:
Customers
John - A36 - New York
Jason - B45 - Los Angeles
Max - A36 - Paris
Providers
John - A36 - London
Mark - B67 - Madrid
Any one can help? I´m a bit stalled
thx
regards.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Limited connections to the database. .
Sorry, I should've replied to your post first, but, have you tried
manually limiting the connections via PHP?
Code used from Omega Engine (Copyright Omega Vortex):
$ini_data = $engine-ini_data['file'];
setup_errors();
if ($ini_data['DB']['connections'] 5) {
$engine-error = There are too many connections to the database,
please try again later.;
$engine-toggle_errbits(ERR_CONNECT);
$engine-engine_die();
}
That code checks the connections value in the ini file to see if it's
greater than 5, if it is, it returns an error telling the visitor to try
again later.
Please note in order to make this work, at least 2-3 other functions
would have to be used. I'd be more than happy to write them for you if
you need it.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Long running db queries and the STOP button
Hello list, We are working on a project where we use PHP5 inside Apache2 and run very long running queries on mysql 5 and postgresql. A single query might run up to several minutes, and generally uses 100% CPU on the database server. So far everything is fine. Now what happens, is that sometimes the user hits the stop / back button before the query completes and reissues a new one. The result is that two queries are running on the database server, even though the results of the first query will never be used. Furthermore as both queries are concurring for the CPU, the second query takes much longer than normal. PHP will only realize that the connection was closed by the browser once the database query is completed, but not during the query itself. What we are looking for is a way to cancel a running query immediately when the user hits the back / stop button on his browser. We found no real solution for this problem in the usual places. Any ideas, thoughts or comments are very welcome! Cheers, Thomas - Thomas Seiler Ing. sys. com. dipl. EPFL SWISSCOM AG Innovations Security and Service Management Ostermundigenstrasse 93 CH - 3050 Bern SWITZERLAND Phone: +41 (0)31 342 42 69 Mobile: +41 (0)79 427 97 26 Fax: +41 (0)31 892 62 27 [EMAIL PROTECTED] http://www.swisscom.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Long running db queries and the STOP button
Few things I can think of: 1. warn the user that the query may take some time and then show a splash type screen that indicates that something is happening 2. Run the whole thing in a new window without the toolbar 3. rework the query so it doesn't take so much time. If there are a lot of joins in the query, you need to re-order the joins to make the combinations more efficient, perhaps break up the queries and place the individual results in array. Then manipulate the arrays to show the data how you want. (I did this to great effect to get a 4 minute query down to 10 seconds) Bastien From: [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Long running db queries and the STOP button Date: Thu, 27 Oct 2005 16:03:58 +0200 Hello list, We are working on a project where we use PHP5 inside Apache2 and run very long running queries on mysql 5 and postgresql. A single query might run up to several minutes, and generally uses 100% CPU on the database server. So far everything is fine. Now what happens, is that sometimes the user hits the stop / back button before the query completes and reissues a new one. The result is that two queries are running on the database server, even though the results of the first query will never be used. Furthermore as both queries are concurring for the CPU, the second query takes much longer than normal. PHP will only realize that the connection was closed by the browser once the database query is completed, but not during the query itself. What we are looking for is a way to cancel a running query immediately when the user hits the back / stop button on his browser. We found no real solution for this problem in the usual places. Any ideas, thoughts or comments are very welcome! Cheers, Thomas - Thomas Seiler Ing. sys. com. dipl. EPFL SWISSCOM AG Innovations Security and Service Management Ostermundigenstrasse 93 CH - 3050 Bern SWITZERLAND Phone: +41 (0)31 342 42 69 Mobile: +41 (0)79 427 97 26 Fax: +41 (0)31 892 62 27 [EMAIL PROTECTED] http://www.swisscom.com -- 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
[PHP-DB] problem with mysql_error()
I am not sure why this is not returning any values but it should be. The database structure CREATE TABLE `hosts` ( `id` int(11) NOT NULL auto_increment, `hostname` varchar(100) NOT NULL default '', `mac` varchar(100) NOT NULL default '', `ip` varchar(100) NOT NULL default '', `vlan` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `mac` (`mac`), UNIQUE KEY `ip` (`ip`), UNIQUE KEY `hostname` (`hostname`), UNIQUE KEY `id` (`id`) ) TYPE=MyISAM AUTO_INCREMENT=4705 ; And the code: $update = @mysql_query( UPDATE hosts SET hostname=\$host\, mac=\$mac\, ip=\$ip\, vlan=\$vlan\ WHERE id=\$id\, $db )or die( img src=\images/error.jpg\nbsp;nbsp;bError: /bProblem occured while updating host records for $host./bbrError Message: . @mysql_error( $update ) . br . Error Number: . @mysql_errno( $update ) . brEmail Administrator: a href=\mailto:$defined[mail]\;$defined[mail]/a ); Because I have set a couple of the fields to unique I should be recieving an error of 'duplicate entry' but i am getting an empty result for mysql_error(). Any help is appreciated. -- Jason Gerfen Student Computing Labs, University Of Utah [EMAIL PROTECTED] J. Willard Marriott Library 295 S 1500 E, Salt Lake City, UT 84112-0860 801-585-9810 My girlfriend threated to leave me if I went boarding... I will miss her. ~ DIATRIBE aka FBITKK -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] problem with mysql_error()
brError Message: . @mysql_error the '@' symbol suppresses the error message...remove it bastien From: Jason Gerfen [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] problem with mysql_error() Date: Thu, 27 Oct 2005 08:43:13 -0600 I am not sure why this is not returning any values but it should be. The database structure CREATE TABLE `hosts` ( `id` int(11) NOT NULL auto_increment, `hostname` varchar(100) NOT NULL default '', `mac` varchar(100) NOT NULL default '', `ip` varchar(100) NOT NULL default '', `vlan` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `mac` (`mac`), UNIQUE KEY `ip` (`ip`), UNIQUE KEY `hostname` (`hostname`), UNIQUE KEY `id` (`id`) ) TYPE=MyISAM AUTO_INCREMENT=4705 ; And the code: $update = @mysql_query( UPDATE hosts SET hostname=\$host\, mac=\$mac\, ip=\$ip\, vlan=\$vlan\ WHERE id=\$id\, $db )or die( img src=\images/error.jpg\nbsp;nbsp;bError: /bProblem occured while updating host records for $host./bbrError Message: . @mysql_error( $update ) . br . Error Number: . @mysql_errno( $update ) . brEmail Administrator: a href=\mailto:$defined[mail]\;$defined[mail]/a ); Because I have set a couple of the fields to unique I should be recieving an error of 'duplicate entry' but i am getting an empty result for mysql_error(). Any help is appreciated. -- Jason Gerfen Student Computing Labs, University Of Utah [EMAIL PROTECTED] J. Willard Marriott Library 295 S 1500 E, Salt Lake City, UT 84112-0860 801-585-9810 My girlfriend threated to leave me if I went boarding... I will miss her. ~ DIATRIBE aka FBITKK -- 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] Long running db queries and the STOP button
Bastien Koert wrote: Few things I can think of: 1. warn the user that the query may take some time and then show a splash type screen that indicates that something is happening 2. Run the whole thing in a new window without the toolbar 3. rework the query so it doesn't take so much time. If there are a lot of joins in the query, you need to re-order the joins to make the combinations more efficient, perhaps break up the queries and place the individual results in array. Then manipulate the arrays to show the data how you want. (I did this to great effect to get a 4 minute query down to 10 seconds) I just had the idea (never tried it myself) that you could possibly fork the request process and let the child process perform the query and the let the parent process (to which the browser 'is connected') wait around for the child to finish ... outputting any results, it could then be possible for user abort to be trapped in the parent and them have some code that kills the child process (or possibly that apache/php responds immeditately in the parent and just lets the child keep running - or kills it off internally) hope that makes sense. it does to me, but maybe I didn't explain it well. Bastien From: [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Long running db queries and the STOP button Date: Thu, 27 Oct 2005 16:03:58 +0200 Hello list, We are working on a project where we use PHP5 inside Apache2 and run very long running queries on mysql 5 and postgresql. A single query might run up to several minutes, and generally uses 100% CPU on the database server. So far everything is fine. Now what happens, is that sometimes the user hits the stop / back button before the query completes and reissues a new one. The result is that two queries are running on the database server, even though the results of the first query will never be used. Furthermore as both queries are concurring for the CPU, the second query takes much longer than normal. PHP will only realize that the connection was closed by the browser once the database query is completed, but not during the query itself. What we are looking for is a way to cancel a running query immediately when the user hits the back / stop button on his browser. We found no real solution for this problem in the usual places. Any ideas, thoughts or comments are very welcome! Cheers, Thomas - Thomas Seiler Ing. sys. com. dipl. EPFL SWISSCOM AG Innovations Security and Service Management Ostermundigenstrasse 93 CH - 3050 Bern SWITZERLAND Phone: +41 (0)31 342 42 69 Mobile: +41 (0)79 427 97 26 Fax:+41 (0)31 892 62 27 [EMAIL PROTECTED] http://www.swisscom.com -- 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
