-
MySql: Count number of rows from multiple tables in a single query
Hi all.
I am trying to count the number of rows from multiple tables in a single query based on criteria. For example, say I have 2 tables, books & bags but based on certain type of a book & certain type of a bag. Now I want to count the number of rows in books table & the number of rows in bags table, all in the same query. I tried some queries like the following:
PHP Code:
$sql = "SELECT count(*) FROM $books AS books_rows, (SELECT count(*) FROM $bags AS bags_rows) WHERE books_rows.book_id = '$book_id' AND bags_rows.bag_id = '$bag_id' AND bags_rows.available = 'y' AND (bags_rows.in_stock = 'y' OR bags_rows.color_available = 'y' OR bags_rows.sold_out = 'n') GROUP BY books_rows.on_id, bags_rows.post_id ";
$result = mysql_query($sql) ;
// I tried different ways of query, like:
$rows = mysql_fetch_array($result);
$sql_data = mysql_fetch_row($result);
Nothing really seems to get me the count from the 2 tables in the same query depending on the criteria. Can some one please suggest a solution?
Thank you in advance.
-
AKA [ Paul Bainbridge]
try this
PHP Code:
$sql = "SELECT count(*) FROM $books AS books_rows, (SELECT count(*) FROM $bags AS bags_rows) WHERE books_rows.book_id = '$book_id' AND bags_rows.bag_id = '$bag_id' AND bags_rows.available = 'y' AND (bags_rows.in_stock = 'y' OR bags_rows.color_available = 'y' OR bags_rows.sold_out = 'n') GROUP BY books_rows.on_id, bags_rows.post_id ";
$result = mysql_query($sql) ; $count = mysql_num_rows($result);
hope it helps
-
Hi,
Thank you for your response. I tried $count = mysql_num_rows($result); but it gives me an error on that line. I have 0 entries in books table & 1 entry in bags table for testing & would like to get the results separately, even though I run only 1 query.
Kindly let me know if you have any possible solution for this.
Thank you.
-
OOP is one letter from OOPS
Never mind, You where clause is going to make what I did a little complicated.
Last edited by kortex; 10-08-2008 at 09:56 AM.
-
Hi, I don't know too much PHP, but the select might have a problem. I think that what you need is using a nested or imbricated select. Something like:
SELECT (SELECT count(*) FROM $books) AS books_rows, (SELECT count(*) FROM $bags) AS bags_rows WHERE books_rows.book_id = '$book_id' AND bags_rows.bag_id = '$bag_id' AND bags_rows.available = 'y' AND (bags_rows.in_stock = 'y' OR bags_rows.color_available = 'y' OR bags_rows.sold_out = 'n') GROUP BY books_rows.on_id, bags_rows.post_id
The select statements are ok, I've used them from some time, but the criteria part, maybe you should use the in in statement. Something like: SELECT * FROM mytable WHERE Empid in(SELECT Empid FROM employee);
Good luck, I hope that's what you looking for!
-
AKA [ Paul Bainbridge]
This thread is over 4 years old. Do you really think help is still need after all this time?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|