searching a mysql db... need a little help

Discussion in 'OT Technology' started by brds, Jun 14, 2007.

  1. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,650
    Likes Received:
    15
    Location:
    Atlanta
    Created a site that uses a db to store a list of links (www.ihaswebsite.com/downloads/index.php if you want to see what i'm talking about). The search i have now works fine for single words or exact matches with multiple words, but what i want it to do is:

    * if the user types in: a b
    result would be anything with 'a' and anything with 'b'

    however, if they type in "300 dvd", i dont want anything with 300 and anything with dvd (because a lot of things might have 'dvd') but if the title in the db is '300 movie dvd rip', a query of "300 dvd" would be fine.

    Pretty much, i want to search for a db entry that contains each specified word.

    The code i currently have is:

    Code:
    $query = trim($_POST['searchquery']); /* post query from search box */
    $result = mysql_query("SELECT * FROM `downloads` WHERE `Name` LIKE '%" . $query . "%' ORDER  BY 'Name'")
                or die(mysql_error()); /* return results */ 

    anybody know what i should do to get the desired effect? :dunno:
     
  2. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    WHERE NAME LIKE %foo% AND NAME LIKE %bar% AND ...
     
  3. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    please tell me you're doing more input validation than what you show there....
     
  4. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    Prolly should use OR not AND
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    ibsqlinjection :mamoru:
     
  6. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    No, I think AND is correct. He wants to show results that have BOTH "300" and "dvd"; not simply results that have either -- because "dvd" would probably be in many unrelated queries, as well.
     
  7. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    Example:

    Code:
    <?
    	
    	// query from the request
    	$query = "my test query";
    	
    	// split the query by spaces
    	$queryArray = explode(' ', $query);
    	
    	// reiterate through the resulting array
    	foreach($queryArray as $value) {
    		
    		// create a temporary array
    		$tempArray[] = "`Name` LIKE '%$value%'";
    	
    	}
    	
    	// add in the 'AND'
    	$queryStr = implode(' AND ', $tempArray);
    	
    	// baum!
    	echo "SELECT * FROM `downloads` WHERE $queryStr ORDER  BY 'Name'";
    	
    	/*
    	Outputs:
    	SELECT * FROM `downloads` WHERE `Name` LIKE '%my%' AND `Name` LIKE '%test%' AND `Name` LIKE '%query%' ORDER BY 'Name'
    	*/
    
    ?>
     
  8. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    And with your setup:

    Code:
    <?
    
    	$query = trim($_POST['searchquery']); /* post query from search box */
    	
    	// split the query by spaces
    	$queryArray = explode(' ', $query);
    	
    	// reiterate through the resulting array
    	foreach($queryArray as $value) {
    		
    		// create a temporary array
    		$tempArray[] = "`Name` LIKE '%$value%'";
    	
    	}
    	
    	// add in the 'AND'
    	$queryStr = implode(' AND ', $tempArray);
    	
    	$result = mysql_query("SELECT * FROM `downloads` WHERE $queryStr ORDER  BY 'Name'")
    	            or die(mysql_error()); /* return results */
    	            
    ?>
     
  9. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    doh....looks like I misread the OP
     

Share This Page