Search database with one to many relationship

[wendystewart80]

Hi there,
Can you help?
I am using php to search a mysql database. I can search by table columns where the relationship is one to one, e.g. where keywords in a search are to match certain fields in a table.
I want to search by the category type of a project. Each project has more than one category so I have a table for projects and a separate table for categories.
To display results from this one to many relationship I have 2 queries. I have tried to search by category in the second query($result2), where the categories are called, but if I select a category (e.g cat1) from the drop down list (cats) on my form, the error message is returned:

Error - Unknown column 'cat1' in 'where clause'

Any suggestions are to what I am doing wrong would be greatly appreciated.

[edited by: ergophobe at 4:22 pm (utc) on July 22, 2005]
[edit reason] code dump snipped - please see forum charter [/edit]

[jd01]

Throwing darts, because I don't know your DB structure, but my guess is it is this line:

WHERE g.catid = d.catid AND p.id = g.id AND p.id = ".$r["id"]." AND $cat=0

The best I can come up with is you meant:

WHERE g.catid = d.catid AND p.id = g.id AND p.id = ".$r["id"]." AND something=$cat

More like the OR clause of the same connection. (Of course the obvious answer is because the query is looking for a column that does not exist...)

Justin

[wendystewart80]

Yes I checked $cat is an actual column in the database.

I removed the line containing $cat=0 which was a step forward as the results then only displayed the category I was looking for, but all the projects were still returned, whether they are linked to that category or not.
I need to link $result2 to $result 1 so that only the projects which are related to the category I am searching for are returned in the results.

Any ideas?

[jd01]

Is the col in the DB actually: $cat or cat1 the value held in the php variable?

Justin

[wendystewart80]

$cat is the variable entered to search the database for a category type.
Cat1 is the category searched for in this instance.

The relevant column is catid.

[jd01]

WHERE g.catid = d.catid AND p.id = g.id AND p.id = ".$r["id"]." AND catid='".$cat."'

Should be close...

Justin