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.
#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=????????
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 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.
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..