kanniyappan r kanniyappan r - 4 years ago 117
MySQL Question

How to get the results two tables and how to display one field value in heading

residential_projects(table name)

id City Project_name

1 1 Residential Property 1
2 1 Residential Project 2


residential_units_details (table name)

id residential_project_id cityId unitType price
1 1 1 1 BHK 50000
2 1 1 2 BHK 100000
3 1 1 3 BHK 150000
4 2 1 4 BHK 43430
5 2 1 5 BHK 53530


Result i need like this

Property name= Residential Property 1
1 BHK = 50000 (price)
2 BHK = 100000 (price)
3 BHK = 150000 (price)

Property name= Residential Project 2
4 BHK = 43430(price)
5 BHK = 53530 (price)


I writen the query like this

$first_second_tables = "
SELECT d.unitType
, d.price
, p.Project_name
FROM residential_units_details d
LEFT
JOIN residential_projects p
ON d.residential_project_id = p.id
WHERE City = 1
";

$sql=mysql_query($first_second_tables);
while($res = mysql_fetch_assoc($sql)){
echo "<pre>";
var_dump($res);
echo "</pre>";
}



i am getting answer


array(3) {
["unitType"]=>
string(1) "1"
["price"]=>
string(5) "50000"
["Project_name"]=>
string(22) "Residential Property 1"
}
array(3) {
["unitType"]=>
string(1) "2"
["price"]=>
string(6) "100000"
["Project_name"]=>
string(22) "Residential Property 1"
}
array(3) {
["unitType"]=>
string(1) "3"
["price"]=>
string(6) "150000"
["Project_name"]=>
string(22) "Residential Property 1"
}
array(3) {
["unitType"]=>
string(1) "4"
["price"]=>
string(5) "43430"
["Project_name"]=>
string(21) "Residential Project 2"
}
array(3) {
["unitType"]=>
string(1) "5"
["price"]=>
string(5) "53530"
["Project_name"]=>
string(21) "Residential Project 2"
}


I have two table,proprty name stored in one table (residential_projects),and property price and unit type stored in another tables (residential_units_details ), i have one property and same property i have different unit type (BHK), now i want to display the results like **


1 BHK means what is the price and property name ,and 2 BHK means what
is the price and property name


**.
here i am getting the correct answer but i am facing one problem property name also coming from loop.while display the front end property type in one heading and remaining details is loop

Answer Source

If you only Want the Name to output if its a new name then you can run a test on each trip through the loop

     $first_second_tables = "SELECT residential_units_details.unitType , residential_units_details.price ,  residential_projects.Project_name FROM residential_units_details LEFT JOIN residential_projects ON residential_units_details.residential_project_id = residential_projects.id WHERE City = '1'";


     $sql=mysql_query($first_second_tables);
     $projectName = ''; //initialize our test variable


     while($res = mysql_fetch_assoc($sql)){
         // if the project name is different output it now
         if( $projectName != $res['Project_name'] ){
             echo $res['Project_name']   ;
         }else {
             // don't echo anything
         }
         echo "<pre>";
         var_dump($res);
         echo "</pre>";
         $projectName = $res[0]['Project_name'];
     }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download