Thread: searching mysql

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    456
    Rep Power
    15
    I've got a mysql datbase. I want users to my website to be able to search two columns called "artist" and "song". They can type either an artist or song or both in the search box and the script should return matches.

    My problem is that I don't really understand the syntax and logic for doing this. The things people search for will not exactly match the data in the database. If someone searched for "small things" and one row in the database had "Blink 182" for artist and "All the Small Things" for the song this should be returned as a match.

    Can someone give a basic explanation of how this is done?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2000
    Posts
    669
    Rep Power
    15
    This one will search for each word in the searchbox:
    $searchwords = explode(" ", $searchbox);
    $sql .= sprintf("SELECT * from tablename WHERE (");
    for ($i=0;$i<count($searchwords);$i++) {
    $sql .= sprintf("((artist LIKE '%s') OR (song LIKE '%s')) OR ", '%'.$searchwords[$i].'%', '%'.$searchwords[$i].'%');
    }
    $sql .= sprintf("%s", substr($sql, 0, 4));
    $sql .= sprintf(") order by artist, song");
    $result = mysql_query($sql);


    This one will search for the phrase in the searchbox:
    $sql .= sprintf("SELECT * from tablename WHERE ((artist LIKE '%s') OR (song LIKE '%s')) ORDER by artist, song", '%'.$searchbox.'%', '%'.$searchbox.'%');
    $result = mysql_query($sql);

Similar Threads

  1. Mysql problem.
    By Nik in forum MySQL Help
    Replies: 54
    Last Post: December 21st, 2006, 06:02 PM
  2. searching a mysql table problem
    By z3phir in forum PHP Development
    Replies: 1
    Last Post: September 27th, 2004, 06:51 PM
  3. searching mysql
    By LTLhawk in forum Development Articles
    Replies: 0
    Last Post: January 24th, 2004, 06:14 PM
  4. Searching for records in MySQL
    By TomGillespie in forum PHP Development
    Replies: 5
    Last Post: January 2nd, 2004, 07:23 PM
  5. Upgrade from access to mysql
    By karib1 in forum MySQL Help
    Replies: 1
    Last Post: December 16th, 2003, 12:27 AM

IMN logo majestic logo threadwatch logo seochat tools logo