SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    php - mysql count from morethan one table

    Hai folks,

    table1

    empno|salary
    -------------
    10 | 1500
    20 | 2750
    21 | 4000

    table2

    empno|department
    -------------
    19 | a
    24 | b
    31 | x

    i want a query to get the total records in both tables.
    so the result should be 6 records.

    my try :

    Code:
    $query="SELECT COUNT(empno) as tot
            FROM table1
    		
    		UNION ALL // and tried with UNION too
            
    		SELECT COUNT(empno) as tot
            FROM table2";
    
    if ($result=mysql_query($query) or die (mysql_error()));
    $row=mysql_fetch_array($result);
    echo "<br>" . $row['tot'];
    shows incorect row count

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    the union gives you two rows
    loop through them in PHP and sum the 'tot' values.

  3. #3
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    the union gives you two rows
    loop through them in PHP and sum the 'tot' values.
    Thank u for the instant reply.
    if there is no native way, then ill do the looping

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You can do this if you don't want to loop:
    Code:
    SELECT SUM(tot) AS tot
    FROM
      (SELECT 
           COUNT(empno) AS tot 
       FROM table1
       UNION ALL
       SELECT 
           COUNT(empno) AS tot
       FROM table2
      ) AS a

  5. #5
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    You can do this if you don't want to loop:
    Code:
    SELECT SUM(tot) AS tot
    FROM
      (SELECT 
           COUNT(empno) AS tot 
       FROM table1
       UNION ALL
       SELECT 
           COUNT(empno) AS tot
       FROM table2
      ) AS a
    Thanks so much for your answer. today only noticed.
    ill chk this solution!


Bookmarks

Posting Permissions

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