RE: [PHP-DB] sql output to a multidimensional array
-Original Message-
From: Chris [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 07, 2006 9:52 PM
To: K.A.Bouton
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] sql output to a multidimensional array
K.A.Bouton wrote:
I need the output of my sql to be a multidimensional array
as follows.
chart [ 'chart_data' ] =3D array ( array ( , 2001,
2002, 2003,
2004 ),
array ( AAA, 0,
10, 30,
63 ),
array ( BBB, 100,
20, 65,
55 ),
array ( CCC,56,
21, 0,
90 )
);
I have tried and am unable so far to get this out of my database.
Any suggestions?
SELECT count( publications.title) AS title_number,
publications.year,
publications.affiliation=20 FROM publications GROUP BY
affiliation, year
ORDER BY year;
There is some for loop I am not getting, and am also not
getting the
= zero counts with the count(*).
You won't be able to get an sql query to return in that
format (I know
what you're trying to do, I've used the same chart software).
You won't get zero counts for data that doesn't exist, you'll need to
generate your series before hand:
?php
for ($i = 2000; $i 2006; $i++) {
$data[$i] = 0;
}
?
then later on override that value.
Thanks - but it's a dynamic dataset and I didn't want to hard code dates in.
I managed to do it by first doing a query on the years, then doing a count
query based on the year from the query above, and if no year was avaiable
count was 0.
Seems to work.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHP and MySQL on Windows causing severe slowdown
I just switched a customer from one Windows host to another. The new host is a Windows only shop, and not overly familiar with MySQL and PHP. My customer has two PHP applications, both important to the site. Everything else is ASP or ASP.NET. When the new host installed PHP and MySQL, the website slowed down to a crawl. Troubleshooting identified the PHP module that connects to MySQL to be the problem. Remove the DLL and there is no problem. Restore the DLL and the problem returns. Neither PHP app is as yet installed, but we are still getting these problems. Needless to say, we need to access the databases! The Windows server is OS: Windows Server 2003 Web Edition Memory: 320 MB RAM Disk: 15 GB SATA Raptor 10K RPM (Free Upgrade!) Allotted Bandwidth: 100 GB Bandwidth MySQL 4 was installed, but was replaced with MySQL 5, hoping this would solve the problem. It didn't. The PHP version is 5.1 Any and all help with this problem would be very much appreciated! Diane -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Displaying SQL Results
try
while($row = $db-sql_fetchrow($result))
{
//[INSERT CODE HERE, I want to display the 'user' and 'count']
$user = $row[0];
$count = $row[1];
}
bastien
From: JM [EMAIL PROTECTED]
To: php-db@lists.php.net
Subject: [PHP-DB] Displaying SQL Results
Date: Fri, 8 Sep 2006 11:58:46 -0700
Hi all,
Can anyone tell me what the syntax would be to display or gather the
results
of this SQL?
SELECT MAX(user), COUNT(*) FROM my_table GROUP BY user ASC;
$sql_records = SELECT MAX(user), email, COUNT(*) FROM my_table GROUP BY
user ;;
if($result = $db-sql_query($sql_records))
{
// fetch associative array
while($row = $db-sql_fetchrow($result))
{
[INSERT CODE HERE, I want to display the 'user' and 'count']
}
$db-sql_freeresult($result);
}
Thanks for any help!
J
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] postgresql, pg_pconnect issues, how to close connection properly
2006/9/8, Julian Tree [EMAIL PROTECTED]: I have a pretty complex data base that runs a lot of aggreated function (sum, count in addtion to joins) constantly. If I turn off pg_pconnect, the connection takes too long, if I turn on the pg_pconnect, the connection eventually max out. My current solution is to have cron job to restart apache gracefull every minute. apachectl graceful I don't like this solution. Is there a way to reset the connections every so often? Hi You're running out off memory? Read after about optimization of postgresql server settings But probably that won't be enough. You should realy look after those queries too. Why are they takeing so much of resources? I bet that you can optimize them. Expand your hardware (maybe some more memory will do the job?) You can optimize your code. As a workaround you can use 2 different connections. One for the complex sql queries and one for relative simple ones. Monitoring the load distributionshould also help. So you realy, realy have the job to do :) A. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Displaying SQL Results
Thanks for the quick reply. Actually I figured it out by using aliasing:
$sql_records = SELECT MAX(user) AS user_name, email, COUNT(user) AS
user_count FROM table_1 GROUP BY user DESC LIMIT 10;;
print $row[user_name] . - . $row[user_count];
This will display the top 10 users in descending order.
On 9/8/06, Bastien Koert [EMAIL PROTECTED] wrote:
try
while($row = $db-sql_fetchrow($result))
{
//[INSERT CODE HERE, I want to display the 'user' and 'count']
$user = $row[0];
$count = $row[1];
}
bastien
From: JM [EMAIL PROTECTED]
To: php-db@lists.php.net
Subject: [PHP-DB] Displaying SQL Results
Date: Fri, 8 Sep 2006 11:58:46 -0700
Hi all,
Can anyone tell me what the syntax would be to display or gather the
results
of this SQL?
SELECT MAX(user), COUNT(*) FROM my_table GROUP BY user ASC;
$sql_records = SELECT MAX(user), email, COUNT(*) FROM my_table GROUP BY
user ;;
if($result = $db-sql_query($sql_records))
{
// fetch associative array
while($row = $db-sql_fetchrow($result))
{
[INSERT CODE HERE, I want to display the 'user' and
'count']
}
$db-sql_freeresult($result);
}
Thanks for any help!
J
