Re: [PHP] Compare 2 resultsets of db-query
Analysis Solutions wrote: Hey Danny: On Mon, Jun 10, 2002 at 10:09:12PM +0200, Danny Kruitbosch wrote: Query1: SELECT FIELD1, COUNT(FIELD2) AS TOTAL from TABLE GROUP BY FIELD1 Query2: SELECT FIELD1, COUNT(FIELD2) AS SUB from TABLE WHERE FIELD2=1 GROUP BY FIELD1 Now I want to print a table that prints the values of FIELD1, TOTAL and SUB. Query 1 returns more rows as query 2. Field1 is the same in both queries so I should be able to 'link' the results of both queries together. I'm wondering exactly what you're trying to do. It may be possible to do in one well crafted query. Why limit the second query only to items where Field2 = 1? What's in FIELD1 and FIELD2? Also, I assume you want each row of your HTML table to have the FIELD1, TOTAL and SUB fields in them, right? --Dan FIELD 1 contains IP address pool names (not unique), field2 the status (0 free, 1 leased). So what I try to do is get an overview of the total number of IP adresses in a pool and the number of leased addresses. There's a fourth field that contains the actual address. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Compare 2 resultsets of db-query
On Wed, Jun 12, 2002 at 04:54:30PM +0200, Danny Kruitbosch wrote: Analysis Solutions wrote: Hey Danny: On Mon, Jun 10, 2002 at 10:09:12PM +0200, Danny Kruitbosch wrote: Query1: SELECT FIELD1, COUNT(FIELD2) AS TOTAL from TABLE GROUP BY FIELD1 Query2: SELECT FIELD1, COUNT(FIELD2) AS SUB from TABLE WHERE FIELD2=1 GROUP BY FIELD1 FIELD 1 contains IP address pool names (not unique), field2 the status (0 free, 1 leased). So what I try to do is get an overview of the total number of IP adresses in a pool and the number of leased addresses. There's a fourth field that contains the actual address. 0 and 1 in FIELD2? That makes this a piece of cake! All you need is one query. In Query1, do a SUM(FIELD2) rather than COUNT(FIELD2). Forget about Query2. --Dan -- PHP classes that make web design easier SQL Solution | Layout Solution | Form Solution sqlsolution.info | layoutsolution.info | formsolution.info T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y 4015 7 Av #4AJ, Brooklyn NY v: 718-854-0335 f: 718-854-0409 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Compare 2 resultsets of db-query
Analysis Solutions wrote: On Wed, Jun 12, 2002 at 04:54:30PM +0200, Danny Kruitbosch wrote: Analysis Solutions wrote: Hey Danny: On Mon, Jun 10, 2002 at 10:09:12PM +0200, Danny Kruitbosch wrote: Query1: SELECT FIELD1, COUNT(FIELD2) AS TOTAL from TABLE GROUP BY FIELD1 Query2: SELECT FIELD1, COUNT(FIELD2) AS SUB from TABLE WHERE FIELD2=1 GROUP BY FIELD1 FIELD 1 contains IP address pool names (not unique), field2 the status (0 free, 1 leased). So what I try to do is get an overview of the total number of IP adresses in a pool and the number of leased addresses. There's a fourth field that contains the actual address. 0 and 1 in FIELD2? That makes this a piece of cake! All you need is one query. In Query1, do a SUM(FIELD2) rather than COUNT(FIELD2). Forget about Query2. --Dan Thanks! Sometimes the obvious is the last thing you think about Danny -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Compare 2 resultsets of db-query
What you're asking is considered a UNION. I'm not sure which database
you're using, so I can't tell if it supports UNIONs or not. If you're using
MySQL, UNIONs are supported in version 4.0-alpha.
Another approach is storing the results of both queries in single array and
then pulling it from there when you need to work with it.
Then again... you don't seem to be sorting the results, so you could just as
easily start the table, run the db query, dump the results, run another
query and dump the results, and then close the table.
I don't know if what I just said makes sense, so here's a psuedo example:
table
?php
$sql = query#1;
$Results = mysql_query($sql, $DBLink);
while ($Row = mysql_fetch_array($Results) )
{
print tr;
print td.$Row[0]./td;
print td.$Row[1]./td;
print td.$Row[2]./td;
print /tr;
}
$sql = query#1;
$Results = mysql_query($sql, $DBLink);
while ($Row = mysql_fetch_array($Results) )
{
print tr;
print td.$Row[0]./td;
print td.$Row[1]./td;
print td.$Row[2]./td;
print /tr;
}
?
/table
-Original Message-
From: Danny Kruitbosch [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 10, 2002 1:09 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Compare 2 resultsets of db-query
Hi,
I've two queries on the same table. They have the following structure:
Query1: SELECT FIELD1, COUNT(FIELD2) AS TOTAL from TABLE GROUP BY FIELD1
Query2: SELECT FIELD1, COUNT(FIELD2) AS SUB from TABLE WHERE FIELD2=1
GROUP BY FIELD1
Now I want to print a table that prints the values of FIELD1, TOTAL and
SUB.
Query 1 returns more rows as query 2. Field1 is the same in both queries
so I should be able to 'link' the results of both queries together.
How do I do this??
Thanks!
Danny
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
This message is intended for the sole use of the individual and entity to
whom it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are
not the intended addressee, nor authorized to receive for the intended
addressee, you are hereby notified that you may not use, copy, disclose or
distribute to anyone the message or any information contained in the
message. If you have received this message in error, please immediately
advise the sender by reply email and delete the message. Thank you very
much.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Compare 2 resultsets of db-query
Hey Danny: On Mon, Jun 10, 2002 at 10:09:12PM +0200, Danny Kruitbosch wrote: Query1: SELECT FIELD1, COUNT(FIELD2) AS TOTAL from TABLE GROUP BY FIELD1 Query2: SELECT FIELD1, COUNT(FIELD2) AS SUB from TABLE WHERE FIELD2=1 GROUP BY FIELD1 Now I want to print a table that prints the values of FIELD1, TOTAL and SUB. Query 1 returns more rows as query 2. Field1 is the same in both queries so I should be able to 'link' the results of both queries together. I'm wondering exactly what you're trying to do. It may be possible to do in one well crafted query. Why limit the second query only to items where Field2 = 1? What's in FIELD1 and FIELD2? Also, I assume you want each row of your HTML table to have the FIELD1, TOTAL and SUB fields in them, right? --Dan -- PHP classes that make web design easier SQL Solution | Layout Solution | Form Solution sqlsolution.info | layoutsolution.info | formsolution.info T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y 4015 7 Av #4AJ, Brooklyn NY v: 718-854-0335 f: 718-854-0409 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
