Page 1 of 1
Linking Tables
Posted: Sun Jul 25, 2004 8:25 pm
by CyberGarage
I have a site that I have developed using MySQL and PHP. On a particular page, I have two table that I would like to link. First table, resources, contains a field with a list of counties. The second table, sale, contains a field with a particular county. I have created several different recordsets for different lists based on the "resources" field called category. When a user goes to a detail page, I would like to list the items that match the resource category then narrow that list by the county. If the "sale.county" in listed in the "resources.counties" I would like to list that item. Below is what I have that works without matching counties.
SELECT resources.id, resources.name, resources.company, resources.phone, resources.category, resources.counties, sale.county
FROM resources, sale
WHERE resources.category = 'title'
Every time I try to add to this to narrow it down to resources in the sale county I either get an error or nothing at all. The resources field with the counties is a list of all counties covered with a comma and space between each. This has been driving me nuts so any help anyone could give on this would be greatly appreciated.
Thanks,
Mark
Posted: Sun Jul 25, 2004 8:47 pm
by ol4pr0
Code: Select all
#to my understanding this should work
SELECT resources.id, resources.name, resources.company, resources.phone, resources.category, resources.counties, sale.county
FROM resources, sale
WHERE resources.category=title
#however you could add an AND
AND resources.id=????????
Resources
Mysql dev Joins
w3schools joins
Posted: Sun Jul 25, 2004 9:00 pm
by feyd
stab in the dark:
Code: Select all
SELECT r.* FROM `resources` r
INNER JOIN `sale` s ON s.`county` = r.`counties`
WHERE r.`category` = 'title'
Posted: Sun Jul 25, 2004 9:03 pm
by CyberGarage
ol4pr0,
Thanks for the input. Maybe I am missing something but the resource.id I don't beleive will help me with the county part. I had tried something like this.
SELECT resources.id, resources.name, resources.company, resources.phone, resources.category, resources.counties, sale.county
FROM resources, sale
WHERE resources.category = 'title' AND resouce.counties LIKE '%sale.county%'
This way any item with the category "title" and where the sale.county matches a county in the resources.counties list would appear in my repeat region.
I am guessing either I missed something in your post or didn't explain myself in my original post.
Thanks,
Mark
Posted: Sun Jul 25, 2004 9:13 pm
by CyberGarage
feyd,
Thanks for the input. I tried the code you posted. Unfortunately no items were listed in my repeat region. In the resource table the field 'counties' is a text field with the counties list like "county1, county2, county3, ....". Could this be part of my problem? This is just driving me nuts. I am fairly new to the whole dynamic site thing and am working with Dreamweaver MX if this help at all. I do appreciate everyone's help.
Here is a link to the page I am testing if anyone wants to take a look. You can try different ID numbers to get different listing with different counties. I believe this is one of the highest IDs right now so you might try numbers below this.
http://www.propertybbs.com/sale_detailb.php?id=1988
Thanks,
Mark
Posted: Sun Jul 25, 2004 9:40 pm
by feyd
ooooooh, that changes some things. Yes, having counties being a text field with a list internally changes a lot of things.. I was thinking each county would be it's own record..
Unforunately I can't come up with a solution that works 100% of the time..
Posted: Sun Jul 25, 2004 9:51 pm
by CyberGarage
feyd,
Thanks for the help. I would rather not have each county in its own field but have been thinking about doing it just to save some hassle.
Thanks,
Mark
Posted: Mon Jul 26, 2004 10:42 pm
by CyberGarage
Thanks guys for the help. I ended up passing the county when linking to the page and it is finally working. Thanks again for your help.
Mark