[SOLVED] Linking Tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
CyberGarage
Forum Newbie
Posts: 8
Joined: Sun Jul 25, 2004 8:25 pm

Linking Tables

Post 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
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'
CyberGarage
Forum Newbie
Posts: 8
Joined: Sun Jul 25, 2004 8:25 pm

Post 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
CyberGarage
Forum Newbie
Posts: 8
Joined: Sun Jul 25, 2004 8:25 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
CyberGarage
Forum Newbie
Posts: 8
Joined: Sun Jul 25, 2004 8:25 pm

Post 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
CyberGarage
Forum Newbie
Posts: 8
Joined: Sun Jul 25, 2004 8:25 pm

Post 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
Post Reply