CodingForums.com - Querying two tables help?

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Querying two tables help? (http://www.codingforums.com/showthread.php?t=287639)

Querying two tables help?

Hello, hope you are all well

I have a question. On my website I have a search facility, and when a user enters their search term it can either be interpreted as a category (held in my categories table) or a name of a title of a story (held in my stories table).

Now in the stories table each story belongs to a category from the categories table, which is storied as a foreign key in the stories table.

Now say the term entered is "racing" on my website for example, what I do is this currently is

(1) run a like query on the categories table to find all the catid's where term like '%racing%' to hold those id values in an array to use them in my later query like so

Code:

Select catid from categories where name like '%racing%'

(2) Then run a search on my stories table to find all the stories where the term is like '%racing%' as well as imploding on the results of the previous query like so

Code:

Select * from stories where name like '%racing%' OR catid IN ('".implode("','",$array)."')

Then echo the results of this query on my page.

So currently I run two queries, which works OK, but I'd love to get it into one query. Is this possible?

Thank You

See the manual for JOINs.

Code:

SELECT stories.give, stories.list, stories.of, stories.fields, categories.wanted

FROM stories INNER JOIN categories

ON stories.catid = categories.catid

WHERE categories.name LIKE '%racing%'

  AND stories.name LIKE '%racint%'

Or use an "implicit join", thus:

Code:

SELECT stories.give, stories.list, stories.of, stories.fields, categories.wanted

FROM stories, categories

WHERE stories.catid = categories.catid

  AND categories.name LIKE '%racing%'

  AND stories.name LIKE '%racint%'

And/or use aliases on your table names to make shorter queries, thus:

Code:

SELECT S.give, S.list, S.of, S.fields, C.wanted

FROM stories AS S, categories AS C

WHERE S.catid = C.catid

  AND C.name LIKE '%racing%'

  AND S.name LIKE '%racint%'

Or or or or ...