Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25

    Unanswered: Query - General - Multiple counts within a single MySQL query

    hi everyone.

    i am trying to do a multiple count in the same mysql query. i am trying to perform the count on two diffrent tables.
    i.e

    to get the total number of messages received by a member

    to get the total number of favourite added by a member.

    i have draft a a fucntion below.

    the query is able to give an accurate report for the first select query but does not recognise the second one.

    is there anything i am doing wrong, or is there a better way to go around this.
    PHP Code:
    function workersmessages (  )
        
         {
                 global 
    $dbc;
                
        
    $select =    " SELECT  
                
                         COUNT( message)AS how_many
                         FROM massages
                         WHERE   (receiver_id = '7')
                         
                         union all
                         
                       SELECT
                
                         COUNT(sender_id)AS no_fav  
                         FROM favourites
                         WHERE  ( sender_id = '7')

                         "
    ;                     
              
                
                             
    $query $select  ;
               
             
    $result = @mysqli_query ($dbc$query);
            
        
    //    $number     =  fetch_numberarray ($result ); 
                
            
    return $result;
         } 

    warm regards

    andreea

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look again, i'm sure you received two rows, not one

    the query does indeed return results for the second SELECT

    unfortunately it doesn't switch the column name half way through the results (this would be impossible anyway)

    column names for the result set of a UNION query always come only from the first SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello again R937

    thank you for your response. you are indeed correct. i double checked the result, (within the MySQL database) and it did indeed produce both reports .

    the question then is this; how do i call the second query;

    below is my attempt to call it;


    PHP Code:


    $general_search    
    workersmessages ( );

            
        while  (
    $row mysqli_fetch_array ($general_searchMYSQLI_ASSOC)) 
                
            {    
              
    //the first query. 
             
    $how_many =safe_output(trim$row['how_many'])) ;
             
                      
    //the second query. 
                      
    $no_fav =safe_output(trim$row['no_fav '])) ;
        
    //        
              
               
            

    you will note that i was able to extract the first query but the system did not recongise the second one " no_fav " .

    warm regards

    Andreea 115

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't do php

    but your second call is wrong -- there is no column called 'no_fav'

    there is only one result set in a UNION query, and you need only one call

    try it this way --
    Code:
    SELECT 'how many' AS which
         , COUNT(*) AS total 
      FROM massages 
     WHERE receiver_id = 7 
    UNION ALL
    SELECT 'no_fav'
         , COUNT(*) 
      FROM favourites 
     WHERE sender_id = 7
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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