How to query multiple tables from a form

[scuzzo84]

My site is a database of skateboarders that tells thier sponsors and when they started and ended.

I have these 3 tables:

person[ID,fname,lname] <--ID is primary key unique
companies[ID, name] <--ID is primary key unique
companies_for_person[person_ID, company_ID, date_sponsorship_started, date_sponsorship_ended]

person.ID matches up with companies_for_person.person_ID and companies.ID matches up with companies_for_person.company_ID

My search page has a form with

Name: [drop down menu of names populated from database] <--- these names have thier ID as thier value

So say someone picks "Jimmy Smith" from the menu, it will search I guess.. companies_for_person for his ID , for example 3, and print out

Jimmy Smith's Sponsors

Company: Powell Skateboards
Date: 2003-12-12 to 2004-12-12

Company: Baker Skateboards
Date: 2005-12-12 to 2006-01-01

Now my problem is getting this query working. I cant figure out if I am supposed to use a join or not. And if I am this is the closest I have got which doesnt even work fully because it doest not do that date or even work actually....

The dropdown menus variable name is "ID"

 
<?php

include 'connect.php';

$ID = $_POST['ID'];

$sql = "SELECT * FROM `person` LEFT JOIN (`companies_for_person`) ON (`ID` =
`person_ID`);
$result = mysql_query($sql);
$numrows = mysql_num_rows($result);

if ($numrows < 1){
print "no records";
}else{
print "Name: {$row['fname']} {$row['lname']}'s sponsors<br />";
while ($row = mysql_fetch_assoc($result)) {
            print "Company: {$row['companies.name']}<br />";
            print "Date: {row['companies_for_person.date_sponsorship_started]} to {row['companies_for_person.date_sponsorship_ended]}
            print "<BR />";
        }
}
?>

[Roger_Ramjet]

To get the full output you show you need to join all 3 tables thus

$query = "SELECT * FROM person p INNER JOIN company_for_person cp ON (p.ID=cp.person_ID) INNER JOIN company c ON (cp.company_ID=c.ID)
               WHERE p.ID=" . $_POST['ID'] . " ORDER BY cp.date_sponsorship_started";

That will give you the person's name, the company names and the dates etc.

[bpat1434]

Use this:

SELECT a.date_sponsorship_started AS start_date, a.date_sponsorship_ended AS end_date, CONCAT(b.fname, ' ', b.lname) AS p_name, c.name AS company
FROM companies_for_person AS a
INNER JOIN person AS b
  ON a.person_id = b.ID
INNER JOIN companies AS c
  ON a.company_id = c.ID
ORDER BY start_date ASC

Then, just do the regular mysql_fetch_array loop:

$name = mysql_result($result, 0, 'p_name');
echo $name."'s Sponsors:<br>";
while($row = mysql_fetch_array($result))
{
  echo 'Company: '.$row['company'].'<br>';
  echo 'Started: '.$row['start_date'].'<br>';
  echo 'Ending: '.$row['end_date'].'<br><br>';
}