I was just recently assigned the task of making a web application for work that keeps track of the different projects that we have open and all the materials (products) that we have used on each job. (For example: I open a detail page on a particular lamp, I want to see at the bottom that I used this lamp on Job1 Job2 Job3 and Job5. And eventually the opposite. When I open the detail Page of Job1 I want to see that I used a gold lamp, green lamp, silver lamp, etc...)
I have followed 3 different tutorials online and cannot get any to work. I found one online that was half way decent and I thought I understood it, but it doesn't seem to work either. The tutorial I was trying to follow was here http://www.tonymarston.net/php-mysql/many-to-many.html
This is what my DB Looks like (the important fields atleast):
products
Id
name
projects
Id
name
I also read to make a DB for the joins so I made:
join
product_id
project_id
So I think I made this correctly. Now I think my PHP code isn't right somewhere.
Code: Select all
<?
$username="username";
$password="password";
$database="specifier";
//this is being called in from a previous database pull that works perfectly
$product_id=mysql_result($result,$i,"Id");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");
$query="SELECT join.product_id, join.project_id, projects.name FROM join, projects WHERE (join.product_id = '$product_id') AND (projects.Id = join.project_id)";
$result=mysql_query($query) or die(mysql_error());
// Counts the number of entries in the Database
$num=mysql_numrows($result);
mysql_close();
$i=0;
// Loops writing this the same number of times as entries
while ($i < $num){
$productname=mysql_result($result,$i,"name");
echo "<a href=\"name.php\">$productname </a>";
$i++;
}
?>If anyone can guide me here it would be greatly appreciated. I don't think I'm doing too bad though, Just opened a few tutorials online and have just about all the app done in 3 days. Leaving the hardest part for last, joining the two tables together.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join, projects WHERE (join.product_id = '1') AND (projects.Id = join.project_id)' at line 1