[PHP-DB] QUERY question (group by) - please help
Hi. Maybe this isn't the correct list for asking the below question (if so sorry ;) ). I want to calculate how many rows my MySQL query which uses 'GROUP BY' returns. The query: SELECT something FROM test WHERE (kid=1) OR (kid=2) OR (kid=4) GROUP BY cid,aid; Is it posible to get number of rows with such a query from MySQL v3.23.49? If it isn't posible ... is it posible in MySQL 4.x? I think a lot about this problem and I realize only the following solution (to avoid returning a lot of rows in PHP): CREATE TEMPORARY TABLE t (i INT); INSERT INTO t (i) SELECT aid FROM the_query_above SELECT COUNT(*) FROM t; DROP TABLE t; But this isn't so elegant. I want to do so on data generated by: CREATE TABLE test ( kid INT, aid INT, cid INT ); INSERT INTO test (kid, aid, cid) VALUES ( 1, 0, 1), ( 2, 2, 2), ( 1, 3, 2), ( 2, 3, 2), ( 4, 4, 2), ( 4, 0, 3), ( 3, 3, 4), ( 4, 3, 4); Regards, Dezo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] QUERY question (group by) - please help
Just a side note in case it's important, mysql_num_rows() is a PHP function, not a native MySQL function. Not being nitpicky, but it might be relevant if, for some reason, Damir cannot use PHP to obtain the number of rows. From the tone of the original post, it seems that might be a possibility. -Original Message- From: Matthew Moldvan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 1:57 PM To: 'Damir Dezeljin'; PHP-db list Subject: RE: [PHP-DB] QUERY question (group by) - please help Try mysql_num_rows() ... http://www.php.net/manual/en/function.mysql-num-rows.php for the documentation. Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Damir Dezeljin [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 1:01 PM To: PHP-db list Subject: [PHP-DB] QUERY question (group by) - please help Hi. Maybe this isn't the correct list for asking the below question (if so sorry ;) ). I want to calculate how many rows my MySQL query which uses 'GROUP BY' returns. The query: SELECT something FROM test WHERE (kid=1) OR (kid=2) OR (kid=4) GROUP BY cid,aid; Is it posible to get number of rows with such a query from MySQL v3.23.49? If it isn't posible ... is it posible in MySQL 4.x? I think a lot about this problem and I realize only the following solution (to avoid returning a lot of rows in PHP): CREATE TEMPORARY TABLE t (i INT); INSERT INTO t (i) SELECT aid FROM the_query_above SELECT COUNT(*) FROM t; DROP TABLE t; But this isn't so elegant. I want to do so on data generated by: CREATE TABLE test ( kid INT, aid INT, cid INT ); INSERT INTO test (kid, aid, cid) VALUES ( 1, 0, 1), ( 2, 2, 2), ( 1, 3, 2), ( 2, 3, 2), ( 4, 4, 2), ( 4, 0, 3), ( 3, 3, 4), ( 4, 3, 4); Regards, Dezo -- 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] QUERY question (group by) - please help
Well, I just assumed he was using PHP since this is a PHP list ... :) Otherwise, he'd have to use SELECT count(something) FROM ... Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Hutchins, Richard [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 2:07 PM To: 'Matthew Moldvan'; 'Damir Dezeljin'; PHP-db list Subject: RE: [PHP-DB] QUERY question (group by) - please help Just a side note in case it's important, mysql_num_rows() is a PHP function, not a native MySQL function. Not being nitpicky, but it might be relevant if, for some reason, Damir cannot use PHP to obtain the number of rows. From the tone of the original post, it seems that might be a possibility. -Original Message- From: Matthew Moldvan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 1:57 PM To: 'Damir Dezeljin'; PHP-db list Subject: RE: [PHP-DB] QUERY question (group by) - please help Try mysql_num_rows() ... http://www.php.net/manual/en/function.mysql-num-rows.php for the documentation. Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Damir Dezeljin [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 1:01 PM To: PHP-db list Subject: [PHP-DB] QUERY question (group by) - please help Hi. Maybe this isn't the correct list for asking the below question (if so sorry ;) ). I want to calculate how many rows my MySQL query which uses 'GROUP BY' returns. The query: SELECT something FROM test WHERE (kid=1) OR (kid=2) OR (kid=4) GROUP BY cid,aid; Is it posible to get number of rows with such a query from MySQL v3.23.49? If it isn't posible ... is it posible in MySQL 4.x? I think a lot about this problem and I realize only the following solution (to avoid returning a lot of rows in PHP): CREATE TEMPORARY TABLE t (i INT); INSERT INTO t (i) SELECT aid FROM the_query_above SELECT COUNT(*) FROM t; DROP TABLE t; But this isn't so elegant. I want to do so on data generated by: CREATE TABLE test ( kid INT, aid INT, cid INT ); INSERT INTO test (kid, aid, cid) VALUES ( 1, 0, 1), ( 2, 2, 2), ( 1, 3, 2), ( 2, 3, 2), ( 4, 4, 2), ( 4, 0, 3), ( 3, 3, 4), ( 4, 3, 4); Regards, Dezo -- 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
