display value of another table

[OldWest]

i have a stupid simple problem here, but ive never done this exactly this way before and im having a tough time working it out. was looking for any suggestions.

 

my script is working fine:

 

$table_name = "plan";
   
$sql = "SELECT id, plan_name FROM $table_name ORDER BY plan_name";

$query = mysql_query($sql);

if(mysql_num_rows($query) > 0)
{

echo "<table>";
while($row = mysql_fetch_array($query))
{
  echo "<tr><td>" . $row['plan_name'] ."</tr></td>";
}
echo "</table>"; ... 

 

but i have another table  company that are related to plan_name

 

i have the company_id field in my plan table for the relationship. and that id is related to the company id field of course.

 

all i am trying to do is display the company_name next to $row['plan_name'], so i know which plans are related to which company.

 

i think i need to create two seperate queries, but is there a way to include everyone in one query? is there a better way?

 

here is my sql:

 

plan table:

`id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `plan_name` varchar(255) NOT NULL, ... 

 

company table

`id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL,
  `company_name` varchar(255) NOT NULL,...

 

it also seemed to be overkill to have two while loops running.. i am just thinking out loud on the best approach to this.

[phpian]

Hi,

 

Just use a JOIN in your SQL:

 

SELECT plan.id, plan.plan_name, company.company_name  
FROM plan 
JOIN company ON company.id = plan.company_id 
ORDER BY plan.plan_name