How to get the results one property on two tables

Question

residential_projects(table name)

id    City   Project_name

1      1     Residential Property 1

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

Result i need like this

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

I writen the query like this but i am not getting the correct answer ,how can do pleaase i spend more time on this please tell me solution My query

  $unitType = trim($_GET['unitType']);
  $first_second_tables = "SELECT * FROM residential_projects res_project JOIN residential_units_details res_unit ON res_project.id = res_unit.residential_project_id WHERE City='1'";
  if($unitType!='')
        {

            $first_second_tables .=" AND (unitType='$unitType')";
        } 

 $sql=mysql_query($first_second_tables);
 while($res = mysql_fetch_assoc($sql)){
     echo "Proprty ID=".$res['id'].'<br>';
     echo "Unittype=" . $res['unitType'].'<br>';
     echo "Price=" . $res['price'].'<br>';
 }

i am getting answer

Proprty ID=1
Unittype=1
Price=50000
Proprty ID=2
Unittype=2
Price=100000
Proprty ID=3
Unittype=3
Price=150000

i think here printing all datas in second tables i don't know how to get my requirement answer anyone help me

Updated code var_dump($res);

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(4) "4343"
  ["Project_name"]=>
  string(21) "Residential Project 2"
}
array(3) {
  ["unitType"]=>
  string(1) "4"
  ["price"]=>
  string(4) "5353"
  ["Project_name"]=>
  string(21) "Residential Project 2"
}

Please explain more because your column create confusion & what is `res_project` in `select` query. Don't use `mysql*` function ti's is deprecated in php 5.5 & removed from php 7 ,prefer `mysqli` or `PDO`. see this [link](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — gaurav, Feb 06 '17 at 12:25
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 2 BHK means what is the price — kanniyappan r, Feb 06 '17 at 12:31
than why are you select all & get input from user which is unitType , try `SELECT unitType , price FROM esidential_units_details LEFT JOIN residential_projects ON residential_units_details.residential_project_id = residential_projects.id WHERE City = '1' ` — gaurav, Feb 06 '17 at 13:47
Now i am getting unitType and price and also i want to display property name,it is in residential_projects tables how can display — kanniyappan r, Feb 06 '17 at 13:55
Property id =1 or propertyname =Residential Property 1, 1 BHK = 50000 (price), 2 BHK = 100000 (price), 3 BHK = 150000 (price). i need answer like this — kanniyappan r, Feb 06 '17 at 13:58
`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'` try this — gaurav, Feb 06 '17 at 14:03
Ya i am getting but propertyname also coming in loop,while display the front end propertyname is heading and remaining details in loop like this i want to display,how can do this one please update your answer it will very help full for me — kanniyappan r, Feb 06 '17 at 14:12
i am facing problem how to differentiate `Residential Property 1` & `Residential Property 2` in loop — gaurav, Feb 06 '17 at 14:53

score 0 · Answer 1 · answered Feb 07 '17 at 08:29

SQL query for output is

 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'

because you want only unitType ,price,project_name so no need to use * for this you have to use left Join .

As you comment you want poject_name on heading without repeating so you can try

$str = '' ; /* storing balnk string for matching */

while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
      if(strcmp($row['project_name'],$str) === 0){  /* if the project_name already store it will return 0 */ 

        echo $row['unittype']," = ",$row['price'],"<br>" ;

      } else {
        echo $row['project_name'],"<br>";
        echo $row['unittype']," = ",$row['price'],"<br>" ;
        $str = $row['project_name']; /* here we are storing the project_name */
      }
    }

How to get the results one property on two tables

1 Answers1