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 4 of 4
  1. #1
    New Coder
    Join Date
    Oct 2007
    Location
    Irwin, PA
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Creating a paginated search engine-need help with query using LIKE function

    I see someone recently posted something on creating a search in PHP, but it's different from what I am doing and I will provide my code for referance.

    I am building a search engine for my clan's website for video games and stuff. All the pages are stored in a database. The table is called pages, and I have various fields in there such as title, description, keywords, time, page content, page views, and more.
    When people write their own reviews or anyone adds a page to the site, they can choose to add META keywords, just like in HTML ex.
    PHP Code:
    <meta name="keywords" content="this, that, other, keywords, two things" /> 
    Well, the keywords column is what I am conducting the search results from, and they are seperated just like that, using commas ex.
    game review, halo 3, halo, some more stuff

    When I conduct my search in a test, I find that no results come up when I type in exact words that exist in the keywords column in the database, yet, I used the like function and I am not sure why. Here is the code so far (and I know some of the pagnation functions aren't right yet):
    PHP Code:
    <?php
    /************************************************************/
    /********************* Pagnated Search Results***************/
    /***
    Author: Edward Boot Sedativechunk@comcast.net

    This script is a search engine that returns pagnated page
    results for the user for whatever their searching for.
    This was originally created for use with the review system only
    but now it can work with the entire site because of the normal
    and reviews pages system.
    /************************************************************/
    /************************************************************/


    //collect the post data
    $keywords $_POST['search'];


    //variables
    $table "pages";
    $redirect "http://paperfectalliancepa.com";

    //get what page they are viewing
    $page $_GET['page'];

    //if they aren't viewing a specific number of page results, they are on page 1
    if(empty($page)) {
    $page "1";
    }



    if(!
    $keywords) {
    echo 
    '<div class="bold">Please enter a term to search for in the database. You are now being 
    redirected...</div>'
    ;
     echo 
    "<meta http-equiv=\"refresh\" content=\"3;url=$redirect\">";
    exit();
    }
    else {
    //perform the search!!

    //first, we need to count how many rows exist in the database
    $pages_count=mysql_query("SELECT * FROM $table WHERE keywords LIKE '%$keywords%'")
     or die(
    'Query Failed:' mysql_error());


    $count mysql_num_rows($pages_count) or die('Query Failed:' mysql_error());



    //break up the results per page if there is a boat load of results
    if($count >= 50) {
    $results_per_page "10";
    }
    else
    if(
    $count >= 100) {
    $results_per_page "12";
    }
    else
    if(
    $count >= 200) {
    $results_per_page "14";
    }
    else {
    //it's below 50
    $results_per_page "8";
    }


    //set the page start and page end depending on what page they are viewing
    $page_limit_start $page $results_per_page;

    $page_limit_end $page $results_per_page $results_per_page;




    //lets get the total number of pages there will be for the search results
    $total_pages $total $results_per_page;



    /*****************************************************************/
    /********************* WHERE THE OUTPUT BEGINS*****************************/


    echo '<h1>Search Results</h1>';

    //tell them what results page they are on
    echo "<div>page <span class=\"bold\">$page</span> of <span class=\"bold\">$total_pages</span></div>";
    echo 
    '<br />';


    //lets finally conduct our search with the paganted results!
    $pag_results=mysql_query("SELECT * FROM $table WHERE keywords LIKE '%$keywords%' ORDER BY Id ASC LIMIT
    $page_limit_start, $page_limit_end"
    )
     or die(
    'Query Failed:' mysql_error());

    if(
    mysql_num_rows($pag_results)==0) {
      echo 
    '<div class="bold">Your search returned no results.</div>';
    }


    while(
    $results=mysql_fetch_array($pag_results)) {

      echo 
    '<h2>' $results['title']; '</h2>';

    }




    /*********************** OUTPUT END**************************/
    /*****************************************************************/





    //MAJOR ELSE BRACKET FOR IF THE SEARCH WAS CONDUCTED
    }
    //****

    ?>

    The main thing I am having trouble with is the queries, so if alot of it seems like jibberish, ignore it. I can't understand why I don't get no search results when I look for things and that is what I need help with.

    An example is, on the site, there is a review for clive barkers jericho for xbox 360, and in the column keywords, the word jericho deffinetly exist in the keywords column, but for some reason when I do the search, it finds no results. Why is this so? Are the commas that seperate all the keywords interfering somehow or is my syntax for the query wrong, such as using the %% signs??

    Note, I also searched on using the SOUNDS LIKE function, or SOUNDEX, but IDK if that's the way to approach it though.


    I would be happy if anyone took a look at my code so far and gave me some pointers on whats wrong with it and/or if I should approach things differently. By the way, this is a pagnated search engine script and I wanted to do something of my own because:
    1. I know how it works
    2. I find it impossible to implicate other people's code on any of my sites, I like making my own material and implying it on my websites
    ... That is, if you meant to ask me why I don't use someone else coded or google one.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New Coder
    Join Date
    Oct 2007
    Location
    Irwin, PA
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I changed my code to use the full text searching here:

    PHP Code:
    <?php
    /************************************************************/
    /********************* Pagnated Search Results***************/
    /***
    Author: Edward Boot Sedativechunk@comcast.net

    This script is a search engine that returns pagnated page
    results for the user for whatever their searching for.
    This was originally created for use with the review system only
    but now it can work with the entire site because of the normal
    and reviews pages system.
    /************************************************************/
    /************************************************************/


    //collect the post data
    $keywords $_POST['search'];


    //variables
    $table "pages";
    $redirect "http://paperfectalliancepa.com";

    //get what page they are viewing
    $page $_GET['page'];

    //if they aren't viewing a specific number of page results, they are on page 1
    if(empty($page)) {
    $page "1";
    }



    if(!
    $keywords) {
    echo 
    '<div class="bold">Please enter a term to search for in the database. You are now being 
    redirected...</div>'
    ;
     echo 
    "<meta http-equiv=\"refresh\" content=\"3;url=$redirect\">";
    exit();
    }
    else {
    //perform the search!!

    //first, we need to count how many rows exist in the database
    $pages_count=mysql_query("SELECT *, MATCH(title, keywords) AGAINST('$keywords' IN BOOLEAN MODE) AS score FROM $table WHERE MATCH(title, keywords) AGAINST('$keywords' IN BOOLEAN MODE)"
     or die(
    'Query Failed:' mysql_error());


    $count mysql_num_rows($pages_count) or die('Query Failed:' mysql_error());



    //break up the results per page if there is a boat load of results
    if($count >= 50) {
    $results_per_page "10";
    }
    else
    if(
    $count >= 100) {
    $results_per_page "12";
    }
    else
    if(
    $count >= 200) {
    $results_per_page "14";
    }
    else {
    //it's below 50
    $results_per_page "8";
    }


    //set the page start and page end depending on what page they are viewing
    $page_limit_start $page $results_per_page;

    $page_limit_end $page $results_per_page $results_per_page;




    //lets get the total number of pages there will be for the search results
    $total_pages $total $results_per_page;



    /*****************************************************************/
    /********************* WHERE THE OUTPUT BEGINS*****************************/


    echo '<h1>Search Results</h1>';

    //tell them what results page they are on
    echo "<div>page <span class=\"bold\">$page</span> of <span class=\"bold\">$total_pages</span></div>";
    echo 
    '<br />';


    //lets finally conduct our search with the paganted results!
    $pag_results=mysql_query("SELECT *, MATCH(title, keywords) AGAINST('$keywords' IN BOOLEAN MODE) AS score FROM $table WHERE MATCH(title, keywords) AGAINST('$keywords' IN BOOLEAN MODE) ORDER BY Id ASC LIMIT
    $page_limit_start, $page_limit_end"
    )
     or die(
    'Query Failed:' mysql_error());

    if(
    mysql_num_rows($pag_results)==0) {
      echo 
    '<div class="bold">Your search returned no results.</div>';
    }


    while(
    $results=mysql_fetch_array($pag_results)) {

      echo 
    '<h2>' $results['title']; '</h2>';

    }




    /*********************** OUTPUT END**************************/
    /*****************************************************************/





    //MAJOR ELSE BRACKET FOR IF THE SEARCH WAS CONDUCTED
    }
    //****

    ?>
    I did as the tutorial said to find full text strings in the database, and I even changed the rows title and keywords to full text successfully, but I am getting 0 results when I search the database... any solutions? I'm stumped. Seems like a cool feature but I am getting 0 search results.

  • #4
    New to the CF scene
    Join Date
    Aug 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    found on a web that fulltext has the following restrictions

    A few restrictions affect MySQL FULLTEXT indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the SET command.
    FULLTEXT indices are NOT supported in InnoDB tables.
    MySQL requires that you have at least three rows of data in your result set before it will return any results.
    By default, if a search term appears in more than 50&#37; of the rows then MySQL will not return any results.
    By default, your search query must be at least four characters long and may not exceed 254 characters.
    MySQL has a default stopwords file that has a list of common words (i.e., the, that, has) which are not returned in your search. In other words, searching for the will return zero rows.
    According to MySQL's manual, the argument to AGAINST() must be a constant string. In other words, you cannot search for values returned within the query.
    found in http://www.onlamp.com/pub/a/onlamp/2.../fulltext.html

    after doing a bit more of research, in the info of mysql, I found this page: http://dev.mysql.com/doc/refman/5.0/...ne-tuning.html
    Last edited by Lumbendil; 08-26-2008 at 02:38 PM.


  •  

    Posting Permissions

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