SitePoint Sponsor

User Tag List

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

    Unhappy search problem.. help!

    Hi,
    i have a search script, which searches through a forum topics and the query is fine.. but the results are bad ... the query is as the following:
    PHP Code:
    select from forum_topics as topicforum_messages as msg where topic.title LIKE 'hi' OR msg.body LIKE 'hi' ORDER BY 'title' 'asc' 
    the problem is in mysql and in the browser, it gives me 5 rows! while there is 1 topic and of course they are all the same.... plz help me !!
    any help is appreciated in advance. thank you

  2. #2
    public static void brain Gybbyl's Avatar
    Join Date
    Jun 2002
    Location
    Montana, USA
    Posts
    647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't need quotes around the 'asc' on your order by clause -- That's an sql builtin. I don't know if that's your problem or not, but it's a start.

    Can we see the php code you are using to iterate over the database results?
    Ryan

  3. #3
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well its too big but here it is:
    PHP Code:
          if ($action == "Search!") {
                if (!
    $text && !$by_author) {
                      echo 
    "<P>Sorry, you have entered an invalid search criteria, please try again...</P>";
                      exit;
                }
                if (
    $type == "any") {
                      
    $sql_search "select * from forum_topics as topic,
                      forum_messages as msg where "
    ;
                      if (
    $where == "title_msg") {
                            
    $sql_search .= " topic.title LIKE '$text'
                            OR msg.body LIKE '
    $text' ORDER BY '$sort$method";
                      } else {

                            
    $sql_search .= " msg.body LIKE '$text' ORDER BY 'topic.$sort$method";
                      }
                } else {
                      
    $sql_search "select * from forum_topics as topic,
                      forum_messages as msg where "
    ;
                      if (
    $where == "title_msg") {
                            
    $sql_search .= " topic.title = '$text'
                            OR msg.body = '
    $text' ORDER BY '$sort$method";
                      } else {
                            
    $sql_search .= " msg.body = '$text' ORDER BY '$sort$method";
                      }
                }
                
                
                
    $results mysql_query($sql_search) or die (mysql_error() . "here"); 

  4. #4
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, the above code is still only showing us the execution of your query, can you show us some additional code just beneath this, in which you atually output content to the browser?
    My name is Steve, and I'm a super-villian.

  5. #5
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh sorry, oki
    PHP Code:
                $results mysql_query($sql_search) or die (mysql_error() . "here");
                echo 
    "$sql_search";
                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 
    "<table width=100%>
                            <TR>
                            <TH width=30% align=left>Topic Name</TH>
                            <TH width=10% align=left>Replies</TH>
                            <TH align=left>Views</TH>
                            <TH width=15% align=left>Author</TH>
                            <TH width=20% align=left>Last Reply Author</TH>
                            <TH align=left>Last Reply Time</TH></TR>"
    ;
                      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>
                      "
    ;
                }
          } 

  6. #6
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by FalFool[/quote
    while there is 1 topic and of course they are all the same
    I'm not sure I understand this comment -- are you sure that there aren't realy 5 database entries that match your SQL search? According to your code, it ough to return any and all matches, not just one.

    What's in your database right now?
    My name is Steve, and I'm a super-villian.

  7. #7
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    forum_topics:
    PHP Code:
    mysqlselect from forum_topics;
    +----------+----------+-------+---------+-------------+-------------------+-----
    ------------+
    forum_id topic_id title author  reply_count last_reply_author last
    _reply_time 
    |
    +----------+----------+-------+---------+-------------+-------------------+-----
    ------------+
    |        
    |        Hi    falfool |           tumbak            04:4
    1
    :29        |
    |        
    |        Hala  tumbak  |           tumbak            04:4
    5
    :48        |
    |        
    |        ghj   falfool |           falfool           08:1
    9
    :18        |
    +----------+----------+-------+---------+-------------+-------------------+-----
    ------------+
    3 rows in set (0.03 sec
    forum_messages:

    PHP Code:
    mysqlselect from forum_messages;
    +--------------------------------+---------+----------+-----------+------------+
    ----------+
    body                           author  topic_id post_time message_id |
     
    reply_id |
    +--------------------------------+---------+----------+-----------+------------+
    ----------+
    Hello,
    This is a nice site :) | falfool |        04:34:47  |      10478 |        1
    |
    DUH!                           | tumbak  |        04:41:29  |      12229 |
            
    |
    Kill
    die                      | tumbak  |        04:43:26  |       6614 |
    |
    me
    li
    ve                     
    tumbak  |        04:45:05  |       3916 |        2
    |
    nya333                         tumbak  |        04:45:48  |      31917 |
            
    |
    Type your message in here      falfool |        08:19:18  |       9465 |
            
    |
    +--------------------------------+---------+----------+-----------+------------+
    ----------+
    6 rows in set (0.03 sec

  8. #8
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem, indeed, is with the SQL query.

    Your combination of the two tables results in a merge of both result sets, and often a duplication of one table's data... I'd recommend doing two separate queries, one for forum topics, and one for forum messages.
    My name is Steve, and I'm a super-villian.

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

  10. #10
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a Q, i need to search through the 2 tables! the body of the message is in the forum_messages table, and the title is in the forum_topics table! and i cant rewrite all my scripts and alter my tables.. so help me in this

  11. #11
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, and if you execute that exact query (substituting the php values yourself), into mysql, you'll see what I mean....

    Sometimes this can be solved using JOINS, though I'm no expert on SQL...

    Cheers!
    My name is Steve, and I'm a super-villian.

  12. #12
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what? i didnt get the last thing u said! can u plz explain to me how would i solve my problem?
    Thank You...

  13. #13
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your posts above show that you are SSH'ing into your server, or executing mysql from your localhost's command line, and are familiar with logging in (i.e. mysql -u root -p password...)

    If you execute your first query as posted on this page, into mysql (or through something like PHPMyAdmin), you will see 5 rows as your HTML output also reveals.

    Your best bet (recinding my JOIN post above, I know) would be to go with separate queries, if not for clarity, just for simplicity. Then, loop through those results, and output the HTML.

    Cheers!
    My name is Steve, and I'm a super-villian.

  14. #14
    SitePoint Enthusiast falfool's Avatar
    Join Date
    Jun 2003
    Location
    Amman
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    first of all, i just printed the values in the tables from mysql to clear the point a little, and second of all that in my search engine there are 2 choices:
    search subject AND message
    search message only
    so if i make 2 queries, 1st has LIKE title and 2nd hsa LIKE message, it wont work! i would have 2 different criteria... plz understand my problem and answer me

  15. #15
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then you will need to use JOINS, a MySQL mechanism for returning results from two tables.

    See http://www.devshed.com/Server_Side/M...ins/page1.html
    for more details
    My name is Steve, and I'm a super-villian.


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
  •