Page 1 of 1

Linking Tables

Posted: Sun Jul 25, 2004 8:25 pm
by CyberGarage
:x

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
:D :D

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