如何显示两个表中引用id的数据

Question

-----------------------------------------
      Table Name: product_cat
-----------------------------------------
id         - int(11)     - auto_increment
           -             -
cat_name   - varchar(30) -
           -             -
branch_code- varchar(30) -
           -             -
date       - datetime    -



-----------------------------------------
      Table Name: product_brand
-----------------------------------------
id          - int(11)     - auto_increment
            -             -
parent_id   - int(11)     -
            -             -
branch_name - varchar(40) -
            -             -
category    - varchar(40) -

我通过这个查询(alter table product_brand add constraint ta1 foreign key(parent_id) references product_cat(id))在product_brand表中应用了外键

并通过以下方式在product_brand表中插入数据

<table>
         <form action="index.php?tanzil=brand_added" method="post">        
     <tr>
       <td>Brand Name:</td>
       <td>
         <input type="text" name="brand_name" />
       </td>
     </tr>
     <tr>
      <td>Category:</td>
      <td>
        <select name="cat_name">

<?php
db_connect();
$q="select * from product_cat order by cat_name";
$rs=mysql_query($q);
for($i=0;$i<mysql_num_rows($rs);$i++){

$rd=mysql_fetch_object($rs);
echo"<option value='$rd->id'>$rd->cat_name</option>";

}
?>

        </select>
      </td>
    </tr>
    <tr>
     <td>
       <input type="submit" value="submit" />
     </td>
   </tr>
      </form>
 </table>

通过这种方式在数据库中插入数据

db_connect();
$name_of_cat = $_POST['cat_name'];
$name_of_brand = $_POST['brand_name'];
$g ="insert into product_brand (parent_id,brand_name,category) values ('$name_of_cat','$name_of_brand','$name_of_cat')";
if(mysql_query($g)){
echo "Done";    
}
else{   
echo"Some error occured";
}

并以这种方式显示数据

$query= "select * from product_brand";
$result= mysql_query($query);
while($row=mysql_fetch_object($result))
{
echo $row['brand_name'];
}

但是我没有从parent_id的product_cat表引用中获取cat_name名称

我怎么才能得到它呢？

Answer 1

这两个表：

SELECT
  p.id,
  p.branch_name,
  c.cat_name
FROM product_Brand     AS p
INNER JOIN Product_cat AS c ON c.id = c.parent_id;

Answer 2

由于结果必须包含"Product_brand“表中所有行，因此尝试使用左连接，如下所示，

$query= "SELECT
      pb.*,
      pc.cat_name
    FROM product_Brand  pb
    LEFT JOIN Product_cat pc ON pb.id = pc.parent_id";
$result= mysql_query($query);
while($row=mysql_fetch_object($result))
{
echo $row['brand_name'];
}