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 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    custom mysql database search assistance

    I have a working search script, but I need to modify it to search better.

    Example: "Faith Hill" returns only "Faith Hill". I need both "Faith Hill" and Hill, Faith" to be returned no matter which way the user inputs the string.

    Here's my code, any ideas?
    PHP Code:
    <html>
    <head>
    <title>karaoke search script</title>
    </head>
    <body onLoad="form.q.focus()">

    <form name="form" action="searcho.php" method="get">
      <input type="text" name="q" />
      <input type="submit" name="Submit" value="Search" />
    </form>

    <?php

      
    // Get the search variable from URL

      
    $var = @$_GET['q'] ;
      
    $trimmed trim($var); //trim whitespace from the stored variable
      
    $trimmed trim($trimmed",");

      
    // rows to return
    $limit=100

    // check for an empty string and display a message.
    if ($trimmed == "")
      {
      echo 
    "<p>Please enter a search...</p>";
      exit;
      }

    // check for a search parameter
    if (!isset($var))
      {
      echo 
    "<p>We dont seem to have a search parameter!</p>";
      exit;
      }

    //connect to your database ** EDIT REQUIRED HERE **
    mysql_connect("localhost","username","password"); //(host, username, password)

    //specify database ** EDIT REQUIRED HERE **
    mysql_select_db("songsdb") or die("Unable to select database"); //select which database we're using

    // Build SQL Query  
    $query "select * from songs where Title like \"%$trimmed%\" or Artist like \"%$trimmed%\"  
      order by Title"
    // EDIT HERE and specify your table and field names for the SQL query

     
    $numresults=mysql_query($query);
     
    $numrows=mysql_num_rows($numresults);

    // If we have no results, offer a google search as an alternative

    if ($numrows == 0)
      {
      echo 
    "<h4>Results</h4>";
      echo 
    "<p>Sorry, your search: &quot;" $trimmed "&quot; returned zero results</p>";
      }

    // next determine if s has been passed to script, if not use 0
      
    if (empty($s)) {
      
    $s=0;
      }

    // get results
      
    $query .= " limit $s,$limit";
      
    $result mysql_query($query) or die("Couldn't execute query");
      
    // display what the person searched for
    echo "<br>You searched for: &quot;" $var "&quot;";

    // begin to show results set
    // echo "Results";
    $count $s ;

    // now you can display the results returned
    echo "<table border=1><TR><td>&nbsp;</TD><td><b>Title</b></TD><td><b>Artist</b></TD><td><b>Number</b></TD></TR>";
      while (
    $rowmysql_fetch_array($result)) {
      
    $title $row["Title"];
      
    $artist $row["Artist"];
      
    $number $row["Number"];

      echo 
    "<tr><td>$count.)</td><td>$title</td><td>$artist</td><td>$number</td></tr>" ;
      
    $count++ ;
      }

      echo 
    "</table>";
    $currPage = (($s/$limit) + 1);

    //break before paging
      
    echo "";

      
    // next we need to do the links to other results
      
    if ($s>=1) { // bypass PREV link if s is 0
      
    $prevs=($s-$limit);
      print 
    "";
      }

    // calculate number of pages needing links
      
    $pages=intval($numrows/$limit);

    // $pages now contains int of pages needed unless there is a remainder from division

      
    if ($numrows%$limit) {
      
    // has remainder so add one page
      
    $pages++;
      }

    // check to see if last page
      
    if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

      
    // not last page so give NEXT link
      
    $news=$s+$limit;

      echo 
    "";
      }

    $a $s + ($limit) ;
      if (
    $a $numrows) { $a $numrows ; }
      
    $b $s ;
      echo 
    "<br>Showing results $b to $a of $numrows";
      
    ?>

    </body>
    </html>

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,801
    Thanks
    160
    Thanked 2,216 Times in 2,203 Posts
    Blog Entries
    1
    You'd need to use mysql's Fulltext search instead of LIKE keyword.

    See http://devzone.zend.com/article/1304
    http://dev.mysql.com/doc/refman/5.5/...xt-search.html
    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 to the CF scene
    Join Date
    Dec 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Good info

    Thanks for the info! I didn't know mysql had added fulltext functionality. Took care of that on my mysql server.

    I still don't know how to create that sort of search, however. Simple searches work fine, ex "Faith Hill" returns anything with "Faith Hill" in the string. What doesn't work, however, is when the information in the database is "Hill, Faith". The people searching in the database are only going to type it one way. I need the search to return BOTH sets of results.

    Any idea how to do that?

  • #4
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,801
    Thanks
    160
    Thanked 2,216 Times in 2,203 Posts
    Blog Entries
    1
    Any idea how to do that?
    The idea is already given. What you need is to follow it.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #5
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The first one you sent me to has good ideas, but the example code he gave and SAID should work has more holes in it than a brick of swiss cheese. see for yourself:
    PHP Code:
    <?php
        
    /* call this script "this.php" */
        
    if ($c != 1) {
    ?>
    <form action="this.php?c=1">
    <input type="text" name="keyword">
    <input type="submit" value="Search!">
    </form>
    <?php
        
    } else if ($c==1) {
            
    MySQL_connect("hostname""username""password");
            
    MySQL_select_db("database");
            
    $sql "
                SELECT *,
                    MATCH(title, body) AGAINST('$keyword') AS score
                    FROM articles
                WHERE MATCH(title, body) AGAINST('$keyword')
                ORDER BY score DESC
            "
    ;
            
    $res MySQL_query($sql);
    ?>
    <table>
    <tr><td>SCORE</td><td>TITLE</td><td>ID#</td></tr>
    <?php
            
    while($row MySQL_fetch_array($rest)) {
                echo 
    "<tr><td>{$sql2['score']}</td>";
                echo 
    "<td>{$sql2['title']}</td>";
                echo 
    "<td>{$sql2['id']}</td></tr>";
            }
            echo 
    "</table>";
        }
    ?>
    Notice most specifically $rest and $sql2 which are not strings anywhere else in the code and have no content. One tends to lose their trust in such rather quickly, but no matter.
    After I fixed it I tried to run it. Results, nada. The only thing it will do is to return to it's starting point.

    I'm running php 5.2.9 and MySQL 5.0.91 so a natural language search like this one should work. My database is indexed to fulltext in both of the fields I am searching through.

    I looked at the page and implemented the code myself using one of my working scripts. The results were the same as if I had never used it. I did the usual MATCH AGAINST WHERE ORDER BY....
    My syntax was good and no errors were thrown, but the same results.

    What I'm beginning to understand is that MySQL DOES care about word order. What I need is a php script that searches for each of the words in the input string individually, then selects the only results that have all of the words in them to be returned back to the client. According to the pages you gave me, that should have happened. It didn't.
    Last edited by xXandarXx; 12-27-2010 at 07:51 PM. Reason: Additional information.

  • #6
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I can and have done this same thing is asp. The server I am currently on has no asp support, only php.
    I'm learning, but I do need some help. As you can see from my reply, I DID read what was given to me and tried very hard to implement it. I was not able to achieve any results.
    I'm beginning to think that a different approach to the problem is needed.

  • #7
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Solved it.

    fulltext didn't help me as mysql STILL returns only what's IN ORDER.
    soooo....
    split()

    That worked. No fulltext needed no matter how many people pointed me towards it.

    If you are interested I'll post the code.

  • #8
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts

    Smile

    Quote Originally Posted by xXandarXx View Post
    If you are interested I'll post the code.
    Yes please!

    Interested in learning from ways of those that tried & tested!
    I almost always use ...LIKE %$searchedterm%..., haven't been sold on that fulltext stuff yet.
    Last edited by student101; 12-28-2010 at 12:09 PM.
    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:


  •  

    Posting Permissions

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