SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry This is killing me!! please help me guys

    Hi guys
    im dying because of this problem: i have a search script in a forum
    im facing a big problem in that which is: i have 2 tables, 1st has the title of the topics which i gotta search in, it also has some other info. about the topic, and the 2nd has the body of the message, which i need to search in too!
    i read too much about JOIN in MySQL, but no help
    im gonna paste the query im currently using, it may help a little:
    PHP Code:
    $sql_search "select *, t2.body from
    forum_topics as t1, forum_messages as t2 WHERE
    (t1.title LIKE '%
    $text%' AND t2.body LIKE '%$text%')
    ORDER BY '
    $sort$method"
    Please tell me how to do this, this is the first time i write a search script, thanks alot..
    I really appreciate ur help
    Last edited by falfool; Jul 24, 2003 at 04:37.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know much about SQL or what the error is, but maybe adding some more paranthesis helps?
    PHP Code:
    $sql_search "select *, t2.body from
    forum_topics as t1, forum_messages as t2 WHERE
    ((t1.title LIKE '%
    $text%') AND (t2.body LIKE '%$text%'))
    ORDER BY '
    $sort$method"

  3. #3
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are missing some kind of a key that links the two tables. Something like
    Code:
    WHERE t1.post_id = t2.post_id
    and you probably want
    Code:
    (t1.title LIKE '%$text%' OR t2.body LIKE '%$text%')
    keyword appears in title OR body of search. AND will force the keyword to appear in both to return results.
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  4. #4
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thanks alot, ill try it and see what happens

  5. #5
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, i guess i found the problem! i have a topic called: "ghj" and it has 4 replies, so when i search for it it gives me 4 results! thats because when u execute the query it will give u 4 rows, 4 for the body which are the topic and its replies, and the 4 are the name of the topic, theyre repeated! so plz tell me how to do this... thanks alot

  6. #6
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    something like:
    Code:
    SELECT DISTINCT t1.topic_id FROM ...
    ?

  7. #7
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DISTINCT? im not familiar with that, can u plz tell me what does it do, and why and how to use it

  8. #8
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    narrows multiple results down to a single unique rows

  9. #9
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    , and how would i use it?

  10. #10
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just like I showed in Post #6. For example, if post_id 4 came up 5 times in your query, you would only get one instance of post_id 4 in your result set.

    The MySQL doc should have something on it.

  11. #11
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks alot.. but plz stay online with me ill try it and come back here... wait

  12. #12
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nope, it didnt work! i executed the following query:
    PHP Code:
    $sql_search "select DISTINCT *, t2.body from forum_topics as t1, forum_messages as t2 WHERE
    (t1.topic_id = t2.topic_id) AND
    (t1.title LIKE '%
    $text%' OR t2.body LIKE '%$text%')
    ORDER BY '
    $sort$method"

  13. #13
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the * is probably makeing every row unique. try limiting what you select to just what you will need to use

  14. #14
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, probably, i need all the rows lol ... and if i LIMIT the results, it wont be a search anymore aint i right? i want u guys to help me help me help me....... :'(

  15. #15
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I probably do not understand what you want sufficiently.

    What I would suggest is describing you issue in much greater detail. What you have including PHP code and Table definitions, and the result you are trying to achive. I find that most of the time when I try to organize my thought to intelligently post a qestion, sometimes the solution will come to me just from that excersize (or more likely, 20 seconds after I push the submit button )

  16. #16
    SitePoint Guru
    Join Date
    Aug 2001
    Location
    Amsterdam
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what you also should do is use
    select distinct t1.*, t2.field from

    or just select distinct *

    atleast thats what I thoughed..
    the neigbours (free) WIFI makes it just a little more fun

  17. #17
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    These are the tables, i got them from the mysql command:
    forum_topics:
    PHP Code:
    +-------------------+--------------+
    forum_id          int(11)      |
    topic_id          int(11)      |
    title             varchar(100) |
    author            varchar(255) |
    reply_count       int(11)      |
    last_reply_author varchar(255) |
    last_reply_time   time         |
    +-------------------+--------------+ 
    forum_messages:
    PHP Code:
    +------------+--------------+
    body       text         |
    author     varchar(255) |
    topic_id   int(11)      |
    post_time  time         |
    message_id int(11)      |
    reply_id   int(11)      |
    +------------+--------------+ 
    What im trying to do, specificly, i have a search script, in a forum. the user enters the text in the text field, then the script searches through the forum_topics.title, forum_messages.body.. then i print the results as the following:
    PHP Code:
                while ($row mysql_fetch_array($results)) {
                      
    $forum_q mysql_query("select max(views) from views where forum_id = $row[forum_id]
                      and topic_id = 
    $row[topic_id]");
                      
    $views mysql_result($forum_q,0,"MAX(views)");

                      echo 
    "
                            <TR>
                            <TD>
                            <a href='topic_show.php?forum_id=
    $row[forum_id]&topic_id=$row[topic_id]'>
                            
    $row[title]</a></TD>
                            <TD>
    $row[reply_count]</TD>
                            <TD>
    $views</TD>
                            <TD>
    $row[author]</TD>
                            <TD>
    $row[last_reply_author]</TD>
                            <TD>
    $row[last_reply_time]</TD>
                            </TR>
                      "
    ;
                }
          } 
    But there is a problem, when i output the results, as much as the topic contains replies, its printed! i mean, if the topic has 3 replies, it will be print 3 times in the results... thats the problem.. sorry for the long message

  18. #18
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All the easy ways I can think of to accomplish your task require sub-selects, which, IIRC, MySQL does not support.

    I might suggest a two step approach. Use a SELECT DISTINCT t1.topic_id ... query to get a list of the topics you want to display, and then run a second query for each topic_id in the first result, picking up the information from t2's last post or something.

    Also, the thought occured to me, maybe you really did want to have the keywork required in both the topic and the body. If that is the case, perhaps switching the OR back to and AND in your WHERE clause might give results more along the lines of what you are looking for.

  19. #19
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by falfool
    well, probably, i need all the rows lol ... and if i LIMIT the results, it wont be a search anymore aint i right? i want u guys to help me help me help me....... :'(
    What sweatje meant was to limit the no. of fields (columns) using the DISTINCT operator - it doesn't limit the results in the way you think.

    So you'd have something like this query:
    Code:
    SELECT DISTINCT t1.topic_id, t1.this_field_i_want, t2.this_too, t2.body
    FROM forum_topics AS t1 INNER JOIN forum_messages AS t2 ON 
      t1.topic_id = t2.topic_id
    WHERE
      t1.title LIKE '%$text%' OR t2.body LIKE '%$text%'
    ORDER BY $sort $method

  20. #20
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aah.... after all, i guess im gonna put 2 choices for the user: 1- search the title of the posts, 2- search the body of the messages, that would hepl me alot anyway.. thanks alot for ur help, im really grateful.. Thanks

  21. #21
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, thanks alot redemption.. ill check it and tell u what happens


Bookmarks

Posting Permissions

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