New to PHP need some help joining Tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
elitegoodguy
Forum Newbie
Posts: 9
Joined: Mon Oct 02, 2006 1:00 pm

New to PHP need some help joining Tables

Post by elitegoodguy »

Hi,

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++;
}
?>
I get this as a result
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
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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Hello elitegoodguy welcome to the forum.

Real quick. Are you trying to do a one-to-many or a many-to-many join?
elitegoodguy
Forum Newbie
Posts: 9
Joined: Mon Oct 02, 2006 1:00 pm

Post by elitegoodguy »

I'm guessing it would be many to many... Because a product can be used by 3 different projects and projects can have 100's of products on 1 project
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Correct it is a many-to-many

You should be able to use something like this:

Code: Select all

//get all products by project id
//untested
$project_id = 500;

$qry = "SELECT b.name as product_name ,c.name as project_name 

FROM tbl_join_table AS a 
LEFT JOIN tbl_products as b on a.product_id = b.id 
LEFT JOIN tbl_projects as c on a.project_id = c.id 
WHERE a.project_id = $project_id";
elitegoodguy
Forum Newbie
Posts: 9
Joined: Mon Oct 02, 2006 1:00 pm

Post by elitegoodguy »

Great... I know I'm closer and your code works, however how would I modify this to make it work to get all projects by product id.

I tried modifying it to

Code: Select all

//get all projects by product id 
//untested 
$product_id = 1; 

$qry = "SELECT b.name as product_name ,c.name as project_name 

FROM join AS a 
LEFT JOIN products as b on a.product_id = b.id 
LEFT JOIN projects as c on a.project_id = c.id 
WHERE a.product_id = $product_id";
And it seems to work pulling all the products from the join table

Thanks for your help. I've been sitting here for about 4 hours trying to figure it out.
elitegoodguy
Forum Newbie
Posts: 9
Joined: Mon Oct 02, 2006 1:00 pm

Post by elitegoodguy »

See next post
Last edited by elitegoodguy on Mon Oct 02, 2006 5:42 pm, edited 1 time in total.
elitegoodguy
Forum Newbie
Posts: 9
Joined: Mon Oct 02, 2006 1:00 pm

Post by elitegoodguy »

Correction:

That didn't work... I noticed a few things wrong with the code (due to duplicate variable names, etc...) So I corrected them and changed back to your origional code. but still getting the same problem as before.

[EDIT]
After playing with it some more I finally got it working. Here's the query that I used

Code: Select all

$qry = "SELECT b.name as product_name ,c.name as project_name 
FROM joins AS a 
LEFT JOIN products as b on a.product_id = b.Id 
LEFT JOIN projects as c on a.project_id = c.Id 
WHERE a.product_id = $product_id";
Thanks for your help. So far so good. I think I can also change this to make it work for the projects too. I know I'll be having issues with getting the form to put the data in. I'll post if I have problems then.

Thanks
Post Reply