user2855148 user2855148 - 3 months ago 14
MySQL Question

mysqli select distinct 2 column in one table and distinct one column from two table

I have 3 tables

table user

id db_fname db_lname

1 xxx yyyy

2 zzz zzzz


tbl_supplier

id db_companyname

1 xxxxxxxxx


tbl_subcontractor

id db_companyname

1 zzzzzzzzzz

2 zzzzzzzzzz


The results should be

xxx yyy
zzz zzz
zzzzzzz
xxxxxxx


fname and lname should together

<?php
$q=mysqli_query($conn,"SELECT db_fname as fname from tbl_user
UNION
SELECT db_lname as lname from tbl_user
UNION
SELECT db_CompanyName as scn from tbl_supplier
UNION
SELECT db_CompanyName as sucn from tbl_subcontractor

")or die(mysqli_error($conn));
echo'<select name="txt_transferredto" class="form-control inpu-md">';

while($row=mysqli_fetch_array($q)){
$fname=$row['fname'];
//$lname=$row['lname'];
$companyname=$row['scn'];
$subcompanyname=$row['sucn'];
$name=$fname.' '.$lname;
if($fname!=""){
echo"<option value='$fname'>";echo $fname;echo"</option>";}
else if($subcompanyname!=""){
echo"<option value='$subcompanyname'>";echo $subcompanyname;echo"</option>";}
else if($companyname!=""){
echo"<option value='$companyname'>";echo $companyname;echo"</option>";}
}

echo'</select>';
?>


The code above give the result but I can't print together
and I have this problem

( ! ) Notice: Undefined index: db_CompanyName in C:\wamp\www\order\projectmanagment\addactivities.php on line 147 Call Stack #TimeMemoryFunctionLocation 10.0026298808{main}( )..\addactivities.php:0 ( ! ) Notice: Undefined index: db_CompanyName in C:\wamp\www\order\projectmanagment\addactivities.php on line 148 Call Stack #TimeMemoryFunctionLocation 10.0026298808{main}( )..\addactivities.php:0 ( ! ) Notice: Undefined variable: lname in C:\wamp\www\order\projectmanagment\addactivities.php on line 149 Call Stack #TimeMemoryFunctionLocation 10.0026298808{main}( )..\addactivities.php:0

Answer

Use concat for first table

 SELECT concat(db_fname,' ' , db_lname)  from tbl_user
 UNION 
 SELECT db_CompanyName from tbl_supplier 
 UNION 
 SELECT db_CompanyName from tbl_subcontractor