...

View Full Version : Querying two tables help?



Oatley
02-14-2013, 03:51 PM
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



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



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

guelphdad
02-14-2013, 04:56 PM
See the manual for JOINs.

Old Pedant
02-14-2013, 08:03 PM
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:


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:


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 ...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum