SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with PHP/MySQL Search

    Hi

    I'm trying to build a a search facility for my site. I have found some good tutorials and developed a simple keyword search.

    The problem is it only works for single keywords(not so good!).

    Can anyone explain what I need to change to enable searching of several keywords with the following code:

    PHP Code:
     if( $_POST['keyword'] ) 

       
    /* Connect to the database: */ 
       
    mysql_pconnect("localhost","username","password"
           or die(
    "ERROR: Could not connect to database!"); 
       
    mysql_select_db("search"); 

       
    /* Get timestamp before executing the query: */ 
       
    $start_time getmicrotime(); 

       
    /* Set $keyword and $results, and use addslashes() to 
        *  minimize the risk of executing unwanted SQL commands: */ 
       
    $keyword addslashes$_POST['keyword'] ); 
       
    $results addslashes$_POST['results'] ); 

       
    /* Execute the query that performs the actual search in the DB:*/ 
      
    $result mysql_query(" SELECT p.page_url AS url, 
                               COUNT(*) AS occurrences 
                               FROM page p, word w, occurrence o 
                               WHERE p.page_id = o.page_id AND 
                               w.word_id = o.word_id AND 
                               w.word_word = \"
    $keyword\" 
                               GROUP BY p.page_id 
                               ORDER BY occurrences DESC 
                               LIMIT 
    $results); 

       
    /* Get timestamp when the query is finished: */ 
       
    $end_time getmicrotime(); 

       
    /* Present the search-results: */ 
       
    print "<h2>Search results for '".$_POST['keyword[1]']."':</h2>\n"
       for( 
    $i 1$row mysql_fetch_array($result); $i++ ) 
       { 
          print 
    "$i. <a href='".$row['url']."'>".$row['url']."</a>\n"
          print 
    "(occurrences: ".$row['occurrences'].")<br><br>\n"
       } 

       
    /* Present how long it took the execute the query: */ 
       
    print "query executed in ".(substr($end_time-$start_time,0,5))." seconds."

    else 

       
    /* If no keyword is defined, present the search page instead: */ 
       
    print "<form method='post'> Keyword: 
              <input type='text' size='20' name='keyword'>\n"

       print 
    "Results: <select name='results'><option value='5'>5</option>\n"
       print 
    "<option value='10'>10</option><option value='15'>15</option>\n"
       print 
    "<option value='20'>20</option></select>\n"

       print 
    "<input type='submit' value='Search'></form>\n"


    print 
    "</body></html>\n"
    /* Simple function for retrieving the current timestamp in microseconds: */ 
    function getmicrotime() 

       list(
    $usec$sec) = explode(" ",microtime()); 
       return ((float)
    $usec + (float)$sec); 


    ?> 

    I think it must be simple but i've spent two days reading up on SQL and every time I try to hack the code to enable multiple keywords I just turn it in to a BIG mess of errors.

    Any help would be great

    Thanks

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So far I understand, you use occurrence(word_id, page_id) as an index table (populated elsewhere), right? I've used something like this in my own projects, although the same may be much simpler with mysql fulltext search.

    For the sake of simplicity and speed I prefer to split the whole process into steps. First, let's translate words to their ids:
    PHP Code:
    $words = array(list of keywords to search);
    $word_list implode(" ' , ' "array_map('addslashes'$words));
    $rc mysql_query("SELECT word_id FROM word WHERE word_word IN ('$word_list')");
    while(
    $w mysql_fetch_row($rc))
       
    $word_ids[] = $w[0]; 
    Now we have, say, (1, 10, 20) instead of ('foo', 'bar', 'baz'). Obviously, if $word_ids is empty we can interrupt the search right now and redirect user to "nothing found" page.

    Now let's find pages with keywords. The sql statement will look like
    PHP Code:
    $ids implode(","$word_ids);
    $rc mysql_query("
          SELECT o.page_id, COUNT(o.page_id) AS relevance, p.url
               FROM occurrence o, page p
               WHERE word_id IN (
    $ids) AND o.page_id = p.page_id
               GROUP BY o.page_id
               ORDER BY relevance DESC"
    ); 
    while(
    $w mysql_fetch_array($rc))
       print 
    pagesordered by relevance 
    "Relevance" is quite primitive here: document that contains N keywords has a relevance N. BTW, if you're performing "all words" search, you can simply restrict it by adding "HAVING relevance=$n" to sql above, where $n = count($word_ids).

    HTH...

    PS All code untested! Handle with care.

  3. #3
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Always
    PHP Code:
    mysql_query(...) or die('...'.mysql_error().'....'); 
    I can understand what you want, not sure any SQL does:
    Code:
    SELECT p.page_url AS url,
                               COUNT(*) AS occurrences
                               FROM page p, word w, occurrence o
                               WHERE p.page_id = o.page_id AND
                               w.word_id = o.word_id AND
                               w.word_word = \"$keyword\"
                               GROUP BY p.page_id
                               ORDER BY occurrences DESC
                               LIMIT $results
    Give more info on tables
    that COUNT(*), is it developed over all occurences X occurences X ...
    Should that go on SQL forum?
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  4. #4
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi stereofrog,
    We just collided but questions shoud be the same.
    Just:
    PHP Code:
    $word_list implode(" ' , ' "array_map('addslashes'$words)); 
    I would say "' and not " ', '" and not ' ",
    do we need trailing spaces around words ?
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  5. #5
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, normally this shoud look like
    PHP Code:
    $sql $db->prepare("SELECT word_id FROM word WHERE word_word IN (?)");
    $res $db->execute($sql$word_list);
    // etc 
    I never write this "addslahes mysql_query or die" stuff in my projects, that's why I'm not so sure about syntax...

  6. #6
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    He really,
    " IN (?) " meaninq all choices ?
    Is it general SQL ?
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  7. #7
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ? is an "sql placeholder", commonly used in sql libraries.

  8. #8
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ooops, I missed it was a 'prepare'
    Then should $word_list be "'a','b','c'" not "' a ',' b ',' c '" ? Empty ?
    That ? in my last sentence is not a "sql placeholder"
    +
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  9. #9
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with PHP/MySQL Search

    Hi Guys,

    I just popped out for something to eat and seamed to have bad timing!!

    The tables for this project were created as follows:
    Code:
    CREATE TABLE page (
       page_id int(10) unsigned NOT NULL auto_increment,
       page_url varchar(200) NOT NULL default '',
       PRIMARY KEY (page_id)
    ) TYPE=MyISAM;
    
    CREATE TABLE word (
       word_id int(10) unsigned NOT NULL auto_increment,
       word_word varchar(50) NOT NULL default '',
       PRIMARY KEY (word_id)
    ) TYPE=MyISAM;
    
    CREATE TABLE occurrence (
       occurrence_id int(10) unsigned NOT NULL auto_increment,
       word_id int(10) unsigned NOT NULL default '0',
       page_id int(10) unsigned NOT NULL default '0',
       PRIMARY KEY (occurrence_id)
    ) TYPE=MyISAM;
    I have a populate file used to fill the tables from a given URL.

    The search function is as my first post.

    As I explained before the scrip works but only for one keyword, i need more really.

    I really appreciate the help.

    Please be aware that I am not to hot with MySQL so simple terms are cool. Aslo I appologise if I should have posted this in MySQL forum I was unsure.

    Thanks

  10. #10
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $result mysql_query(" SELECT p.page_url AS url,
                               COUNT(o.occurrence_id) AS occurrences  // forgive *
                               FROM page p, word w, occurrence o
                               WHERE p.page_id = o.page_id AND
                               w.word_id = o.word_id AND
                               w.word_word IN (\"
    $word_list\") // as we disscussed as you got your foof ( ' or " )?
                               
    GROUP BY p.page_id
                               ORDER BY occurrences DESC
                               LIMIT $results
    " ); 
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  11. #11
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with PHP/MySQL Search

    Okay I made the changes from the previos post and understand that I need to create a word_list array from the keywords entered in the FORM. But I don't understand how to do this.

    Now when I run the search from the web page I get;

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
    this is the line 67 in code:

    Code:
     for( $i = 1; $row = mysql_fetch_array($result); $i++ )
    I think I need to keep reading php and MySQL manuals!!

    Thanks for working with me on this its driving me mad! So close yet Sooo far.

  12. #12
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Take your whole;
    $result = mysql_query(" SELECT p.page_url AS url,
    COUNT(o.occurrence_id) AS occurrences // forgive *
    FROM page p, word w, occurrence o
    WHERE p.page_id = o.page_id AND
    w.word_id = o.word_id AND
    w.word_word IN (\"$word_list\") // as we disscussed as you got your foof ( ' or " )?
    GROUP BY p.page_id
    ORDER BY occurrences DESC
    LIMIT $results" );
    and copy it just before replacing
    $result = mysql_query(...
    by echo(....
    So we can actually see what this SQL should eat !
    Thus die on mysql_error()
    +
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  13. #13
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with PHP/MySQL Search

    I did as you said and I get error as in my last post, is the problem is with fetch _array? I don't know.

    anyway its 2am here and I need some sleep i'll try some more in the morning.

    Thanks for all your help.

  14. #14
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bye, you're right better dreaming
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source


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
  •