Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts

    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    See the manual for JOINs.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,986
    Thanks
    79
    Thanked 4,432 Times in 4,397 Posts
    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 ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Oatley (02-15-2013)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •