SitePoint Sponsor

User Tag List

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

    UNION and Fetch Row

    Hai folks,

    Code:
    	
    $query="SELECT * FROM candidates INNER JOIN approvals 
    			ON candidates.cid = approvals.cid 
    			WHERE agency_code='$agency_code'
    			
    			UNION
    			
                SELECT * FROM candidates_ans INNER JOIN approvals_ans 
    			ON candidates_ans.cid = approvals_ans.cid 
    			WHERE agency_code='$agency_code'";
    
    if ($result=mysql_query($query) or die (mysql_error()));
    while ($row=mysql_fetch_array($result)){
    
    xxxxxx
    
    }
    I want to know the table name currently the while loop is fetching records

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    You need to add that information as a constant in the select list.

    Code:
    select <columnsOfInterest>,
           'Candidates' as origin
      from candidates
      join approvals
        on candidates.cid = approvals.cid 
     where agency_code = '$agency_code'
     union all
    select <columnsOfInterest>,
           'Candidates_ans'
      from candidates_ans 
      join approvals_ans
        on candidates_ans.cid = approvals_ans.cid 
     where agency_code = '$agency_code
    You can use union all which avoids the need for attempting to eliminate any duplicates in the result set.

  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 swampBoogie View Post
    You need to add that information as a constant in the select list.

    Code:
    select <columnsOfInterest>,
           'Candidates' as origin
      from candidates
      join approvals
        on candidates.cid = approvals.cid 
     where agency_code = '$agency_code'
     union all
    select <columnsOfInterest>,
           'Candidates_ans'
      from candidates_ans 
      join approvals_ans
        on candidates_ans.cid = approvals_ans.cid 
     where agency_code = '$agency_code
    You can use union all which avoids the need for attempting to eliminate any duplicates in the result set.
    Hai Swampboogi,
    thanks for the reply, ill look at ur solution.
    mean time on google i found a similler tem table solution. but know idea how to extract the table name while fetching.

    Code:
    	$query="SELECT * FROM candidates as tbl_candidates INNER JOIN approvals 
    			ON tbl_candidates.cid = approvals.cid 
    			WHERE agency_code='$agency_code'
    			
    			UNION
    			
                SELECT * FROM candidates_ans as tbl_candidates_ans INNER JOIN approvals_ph_ans 
    			ON tbl_candidates_ans.cid = approvals_ph_ans.cid 
    			WHERE agency_code='$agency_code'";

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    That rewrite will not help. The table names are not part of the result set, that's why you need to explicitly add a column with that information.

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

    Code:
    	$query="SELECT 'cid' as cid1,'position' as position1,'name' as name1,'dob' as dob1,'phone' as phone1,'education' as education1,'experience' as experience1,'salary' as salary1,'date' as date1  
    	        FROM candidates INNER JOIN approvals 
    			ON candidates.cid = approvals.cid 
    			WHERE agency_code='$agency_code'
    			
    			UNION
    			
                SELECT 'cid' as cid2,'position' as position2,'name' as name2,'dob' as dob2,'phone' as phone2,'education' as education2,'experience' as experience2,'salary' as salary2,'date' as date2 
    			FROM candidates_ans as tbl_candidates_ans INNER JOIN approvals_ans 
    			ON tbl_candidates_ans.cid = approvals_ans.cid 
    			WHERE agency_code='$agency_code'";
    
    if ($result=mysql_query($query) or die (mysql_error()));
    $tot=mysql_num_rows($result);
    while ($row=mysql_fetch_array($result)){
    
       echo $row['cid1'];
       echo $row['cid2'];
       echo "<br>";
    }
    its not working, no looping happening it seem

  6. #6
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sorry, in my prvious post, the second table query after union is little messed up. here is the correct one

    Code:
    	$query="SELECT 'cid' as cid1,'position' as position1,'name' as name1,'dob' as dob1,'phone' as phone1,'education' as education1,'experience' as experience1,'salary' as salary1,'date' as date1,'candidates' as tbl1  
    	        FROM candidates INNER JOIN approvals 
    			ON candidates.cid = approvals.cid 
    			WHERE agency_code='$agency_code'
    			
    			UNION
    			
                SELECT 'cid' as cid2,'position' as position2,'name' as name2,'dob' as dob2,'phone' as phone2,'education' as education2,'experience' as experience2,'salary' as salary2,'date' as date2,'candidates_ans' as tbl2 
    			FROM candidates_ans INNER JOIN approvals_ph_ans 
    			ON candidates_ans.cid = approvals_ph_ans.cid 
    			WHERE agency_code='$agency_code'";
    			
    }


    Thanks swamboogi,
    now it works fine!!! great lesson!

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    1) Get rid of the quotes around the column names, or use backticks instead. Using quotes makes that 'cid' (for example) is considered a string instead of a column name.
    2) The columns in query result will have the names from the first select. So it's useless to give them other names in the select after the UNION. For example, the first column will be called cid, and not cid2 (a column can't have two names).

    @swampBoogie ; in this case UNION ALL isn't necessary, because adding the table name in a column, no rows from the two tables will be identical

  8. #8
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I am sorry, i am totally messed up today

    Now as per #2 i have modified my query

    Code:
    			SELECT 'cid' as cid1,'candidates' as tbl1
    	        FROM candidates
    			INNER JOIN approvals 
    			ON candidates.cid = approvals.cid 
    			WHERE agency_code='$agency_code'
    			
    			UNION
    			
    			SELECT 'cid' as cid1,'candidates_ans' as tbl2
    	        FROM candidates_ans 
    			INNER JOIN approvals_ans 
    			ON candidates_ans.cid = approvals_ans.cid 
    			WHERE agency_code='$agency_code'";
    mysql num rows shows only 1 matching record for above.This is false.
    because, if i executecute the above query with only the first SELECT (without union and below that), it produces 12 results which is correct. whats wrong?

  9. #9
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    @guido

    I know that there will be no duplicates in the result. The union all is intended as a hint to the optimizer that there is no need to eliminate duplicates.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    The union all is intended as a hint to the optimizer that there is no need to eliminate duplicates.
    Ah, that's interesting. Thanks.
    Quote Originally Posted by afridy View Post
    mysql num rows shows only 1 matching record for above.This is false.
    because, if i executecute the above query with only the first SELECT (without union and below that), it produces 12 results which is correct. whats wrong?
    Echo out the query, and run it in PHPMyAdmin to see the result. But my guess is it's because of #1 in my post above.

  11. #11
    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
    1) Get rid of the quotes around the column names, or use backticks instead. Using quotes makes that 'cid' (for example) is considered a string instead of a column name.
    2) The columns in query result will have the names from the first select. So it's useless to give them other names in the select after the UNION. For example, the first column will be called cid, and not cid2 (a column can't have two names).

    @swampBoogie ; in this case UNION ALL isn't necessary, because adding the table name in a column, no rows from the two tables will be identical
    Thanks for the valuable tips!!

  12. #12
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hai folks,

    i am now able to see the correct totals by fixing the following.

    Ur suggestions

    *remove quotes from tables name
    and
    *union all are helped to solve the problem.

    Thank you for the great help

    Code:
                SELECT candidates.cid as cid1,'candidates' as tblname
                FROM candidates
                INNER JOIN approvals
                ON candidates.cid = approvals.cid
                WHERE agency_code='$agency_code'
               
                UNION ALL
               
                SELECT candidates_ans.cid as cid1,'candidates_ans' as tblname
                FROM candidates_ans
                INNER JOIN approvals_ph_ans
                ON candidates_ans.cid = approvals_ph_ans.cid
                WHERE agency_code='$agency_code'";
    Have a nice day!

  13. #13
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    small note : with and without union it works in my case.tx.

    last updated code

    Code:
                SELECT candidates.cid as cid1,'candidates' as tblname
                FROM candidates
                INNER JOIN approvals
                ON candidates.cid = approvals.cid
                WHERE agency_code='$agency_code'
               
                UNION
               
                SELECT candidates_ans.cid as cid1,'candidates_ans' as tblname
                FROM candidates_ans
                INNER JOIN approvals_ph_ans
                ON candidates_ans.cid = approvals_ph_ans.cid
                WHERE agency_code='$agency_code'";

  14. #14
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Slightly off topic but please try and refrain from using the mysql_ libraries:

    From http://www.php.net/manual/en/functio...-fetch-row.php

    Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.
    Richard
    Resell SSL Certificates - API / WHMCS / HostBill / ClientExec
    ServerTastic - RapidSSL, Geotrust, Thawte, Symantec, SmarterTools and more

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afridy View Post
    small note : with and without union it works in my case.tx.
    UNION (without ALL) requires a complete sort of the entire result set on all columns, as part of the task of detecting duplicate rows

    UNION ALL is preferred in this case because we already know there won't be any duplicate rows

    skipping the unnecessary sort is better, wouldn't you say?
    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
  •