SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    retreiving data from a MULTI- SELECT query

    Hi everyone.

    i am a little confused about this issue. i draft a query in which i used numrous select statements, within the same query; i then join each of the select statements together with a UNION ALL.

    I tested the query in the MySQL and it works. the only problem is that i dont know how to extract this information in my loop i.e
    PHP Code:
    while  ($row mysqli_fetch_array ($general_searchMYSQLI_ASSOC)) 
    it might be easier at this point if i show you what i am referring to;


    PHP Code:

    function workersmessages (  )
        
         {
                 global 
    $dbc;
            
               
    select =    " SELECT  
                
                         COUNT( message)AS how_many
                         FROM massages
                         WHERE   (receiver_id = '7')
                         
           union all
                                              
                         SELECT  
                
                         COUNT( message)AS how_many_sent
                         FROM massages
                         WHERE   ( sender_id = '7')
                         
          union all
                                                              
                        SELECT  
                
                         COUNT( message)AS how_many_unread
                         FROM massages
                         WHERE   (receiver_id = '7')
                         AND     (is_read = 'U')
                         
                  "
    ;                     
                      
                
                             
    $query $select  ;
             
    $result = @mysqli_query ($dbc$query);
           
            return  
    $result;
         } 
    the above is my MySQL function to retrive the data; below is PHP fuction to retrive the data from the function;

    PHP Code:
    $general_search    workersmessages ( );

        while  (
    $row mysqli_fetch_array ($general_searchMYSQLI_ASSOC)) 
                
            {    
                          
               
    $how_many =safe_output(trim$row['how_many'])) ;



            } 

    you will note that the mysql query doesn't switch the column name half way through the query;accordingly the data in the SELECT statements ( after the inital one) do not have names . i therefore dont know how to call them in my loop.

    i would really appriciate any advice/reccomendations.

    warm regards

    Andreea 115

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i already showed you how to do it -- http://www.dbforums.com/mysql/168475...sql-query.html

    my advice to you is stick to one site to ask your questions on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •