[SOLVED] PHP Mysql query????

[stevehart808]

Hi everyone,

 

Just wondering if someone would be so kind as to fill me in on if what I wold like to attempt is possible please?

 

I have 2 tables,

 

1 has (projects)

id

provider_id,

project_title,

date.

 

the 2nd is (providers)

id

name

 

Now the provider_id in the same as the id in the providers table so the name will match up. All I want is to output the providers name instead of the providers_id like I have at the moment. Some sort of linking is needed I guess?

 

$result = mysql_query("SELECT * from projects ORDER BY date");

echo "<table border='2' cellpadding='3' cellspacing='2'style='border-top: 1px solid #0092F2; border-right: 1px solid #0092F2; border-left: 1px solid #0092F2;'>
<tr class='grey_link'>
<th>PROVIDER</th>
<th>TITLE OF PROJECT</th>
<th>DATE</th>
</tr>";


while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td class='grey_link'>" . $row['provider_id'] . "</td>";
  echo "<td class='grey_link'>" . $row['project_title'] . "</td>";
  echo "<td class='grey_link'>" . $row['date'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

 

Thanks

 

Steve

[gevans]

try changing your query to this;

 

$result = mysql_query("SELECT a.project_title, a.date, b.name 
FROM projects a, providers b 
WHERE a.provider_id = b.id
ORDER BY date");

 

Not tested but pretty straight forward

 

**EDIT**

 

don't forget to change the output to this;

 

  echo "<tr>";
  echo "<td class='grey_link'>" . $row['name'] . "</td>";
  echo "<td class='grey_link'>" . $row['project_title'] . "</td>";
  echo "<td class='grey_link'>" . $row['date'] . "</td>";
  echo "</tr>";

[Adam]

$result = mysql_query("SELECT t1.*, t2.* from projects AS t1, providers AS t2 WHERE projects.provider_id = provider.id ORDER BY date");

 

Then..

 

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td class='grey_link'>" . $row['t2.name'] . "</td>";
  echo "<td class='grey_link'>" . $row['t1.project_title'] . "</td>";
  echo "<td class='grey_link'>" . $row['t1.date'] . "</td>";
  echo "</tr>";
  }

 

This isn't checked...

 

EDIT: gevans beat me too it with a pretty similar solution..

 

A

[stevehart808]

That's absolutely brilliant! Thank you both for your help, I've been trying to do this for ages. I went with Gevans example and it works great. I'll give the other a go as well. Cheers again! Steve