Hai folks,
$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 
You need to add that information as a constant in the select list.
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.
$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'";
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.
Hai swamp
$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 
sorry, in my prvious post, the second table query after union is little messed up. here is the correct one
$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' [B]as tbl1[/B]
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' [B]as tbl2 [/B]
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!
@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 
I am sorry, i am totally messed up today 
Now as per #2 i have modified my query
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?
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.
Ah, that’s interesting. Thanks.
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.
Thanks for the valuable tips!!
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
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!
small note : with and without union it works in my case.tx.
last updated 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'";
Slightly off topic but please try and refrain from using the mysql_ libraries:
From http://www.php.net/manual/en/function.mysql-fetch-row.php
Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.
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?