MySql: Count number of rows from multiple tables in a single query
A Flash Developer Resource Site

Results 1 to 6 of 6

Thread: MySql: Count number of rows from multiple tables in a single query

  1. #1
    Member
    Join Date
    Jan 2007
    Posts
    79

    Question 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.

  2. #2
    AKA [ Paul Bainbridge] webdreamer's Avatar
    Join Date
    Aug 2001
    Location
    Glasgow, Scotland, UK
    Posts
    3,318
    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
    .: To me AS is like LEGO, Only for the big Kids :.
    - Site - Blog - Twitter - Linkedin
    bringmadeleinehome.com

  3. #3
    Member
    Join Date
    Jan 2007
    Posts
    79
    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.

  4. #4
    OOP is one letter from OOPS kortex's Avatar
    Join Date
    Aug 2005
    Location
    New Hope, PA
    Posts
    2,668
    Never mind, You where clause is going to make what I did a little complicated.
    Last edited by kortex; 10-08-2008 at 10:56 AM.
    Jeremy Wischusen
    Flash - Flex - LAMP - Web Developer Purple Inc
    AS OOP FAQ-Best Practices Thread | Flashkit OOP Tutorials | Purple Inc (day job) | Blog


  5. #5
    Junior Member
    Join Date
    Jun 2012
    Posts
    1
    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!

  6. #6
    AKA [ Paul Bainbridge] webdreamer's Avatar
    Join Date
    Aug 2001
    Location
    Glasgow, Scotland, UK
    Posts
    3,318
    This thread is over 4 years old. Do you really think help is still need after all this time?
    .: To me AS is like LEGO, Only for the big Kids :.
    - Site - Blog - Twitter - Linkedin
    bringmadeleinehome.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  




Click Here to Expand Forum to Full Width


HTML5 Development Center

Poll by Flashkit.com