Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2010
    Posts
    20

    Unanswered: search keywords on multiple tables

    Hello everyone,

    I'm new in this forum and I hope someone can help me...

    I'm trying to do a little search engine for my website.
    I've different tables on my db and I'm looking for a query that allows me to search the keywords on all the tables.

    This is the part of code that interests my problem. It works but I need to search in more tables than one.

    PHP Code:
    for ($x 0$x count($keys); $x++) {
    $querystr "SELECT img,link,numclick,description,ordine FROM $table1 WHERE description = \"$keys[$x]\" OR description LIKE \"%$keys[$x]%\" OR ordine = \"$keys[$x]\" OR ordine LIKE \"%$keys[$x]%\"";
    $result mysql_query($querystr);
    (...) 
    I've tried with this method that was working in another situation but not now. Maybe because it is inside a 'for cycle'?

    (SELECT *
    FROM $table1)
    UNION ALL
    (SELECT *
    FROM $table2)
    UNION ALL
    etc.

    What can I do??

    If it was necessary post more code to understand, i do.
    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do it inside a for loop, executing one query after another (each on a separate table)

    alternatively, you can use the for loop to construct the UNION query, one SELECT for each table, and then execute it after it has been completely built

    note that once the database starts executing the UNION query, it basically has to execute the SELECTs one at a time, and then it returns the results of all of them in one result set

    with the for loop executing one query at a time, the overall response time might be slower, as there is the accumulated back-and-forth communication overhead to consider
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is it just me, or why would you have mulitple keywords spread across different tables
    just wondering what you table design is?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2010
    Posts
    20
    is it just me, or why would you have mulitple keywords spread across different tables
    just wondering what you table design is?
    Every table represent a different category and in every table I've the same fields. Now what I'm doing is more an exercise to learn, so is not very important. Just I would like to understand how I should do to do right, for the next, real time.

    I've done something like this:
    In all my tables I've these columns:
    id, img, link, numclick, description, date

    and i try to search the keywords in the description of all tables...

    Is this not a senseful way, mh?

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    why not add a category column and just have one table.
    Dave

  6. #6
    Join Date
    Jul 2010
    Posts
    20
    but this is the point that is still not very clear to me...

    if for example I do a category column, I should repeat the category name for every product I have in that category?

    for example:
    category column | name | description
    category1 | a1 | bla,bla
    category1 | b1 | bla,bla
    category1 | c1 | bla,bla
    category2 | a2 | bla,bla
    etc.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by supadema View Post
    if for example I do a category column, I should repeat the category name for every product I have in that category?
    assuming that "id, img, link, numclick, description, date" describes a product, then yes, you would repeat the category for every product in that category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    So you waste a few bytes with the category name on each row. Depending on the length of your category names, you might consider an additional column in your category table that can contain an abbreviated version of the name, say 3 - 4 bytes, then put that abbreviation into this table.
    I'd rather do that than manage n+ category tables.
    Dave

  9. #9
    Join Date
    Aug 2010
    Posts
    2

    Smile

    hello

    this is a design problem, why you separated categories on different tables?
    you should use one table with a reference to a category table, so you can make only a query like you are doing now, but with a condition more...


    WHERE cat_id=<id> AND etc...

    make sense?

    Best regards

  10. #10
    Join Date
    Aug 2010
    Posts
    1
    Great forum!! I have learned a lot here. Thanks for sharing guys!!

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Supadema, you shouldn't ask the same question in different forums (ie here).

Posting Permissions

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