PHP/SQL search field help

Discussion in 'Web Design and Development' started by Poeben, Jul 8, 2008.

  1. Poeben macrumors 6502

    Joined:
    Jul 29, 2004
    #1
    I'm trying to get a rather simple search function working but I'm having a bit of trouble. Basically I have a simple form with one text field. The input is then passed as a POST variable, exploded into an array and then checked for any included operators (+/-). I would like to then construct SQL queries based on the input, e.g.,:

    Code:
    mac iphone leopard => SELECT * FROM * WHERE * LIKE '%mac%' OR * LIKE '%iphone%' OR * LIKE '%leopard%'
    mac +iphone -leopard =>SELECT * FROM * WHERE * LIKE '%mac%' AND * LIKE '%iphone%' NOT * LIKE '%leopard%'
    
    I've been able to construct and echo the queries ok as strings (using for loops and substr functons) but I don't know how to construct a single query that depends on a loop. It seems like the for() loop needs to be smack in the middle of the query, or i need to 'echo' the query somewhere as a single variable? Any ideas? Thanks.

    edit: Just to clarify, the following code I cleaned up very quickly, so there may be syntax or minor errors....It's really the construction of the actual SQL query from the array/for() I'm having trouble with.

    Code:
    $search = $_POST['search']; //variable from form
    $searchArr = explode(' ' , $search); //delimit to array using 'space'
    $searchCount = count($searchArr); //count number of search terms
    
    //Connect to database(assume I know how to do this)
    
    //Create query and echo output
    $i = 0;
    if ($i==0) {
    	$queryPrefix = "mysql_query('SELECT `name` FROM `sfx_song` WHERE `name` LIKE '%" . $searchArr[$i] . "%'";
    }
    
    for ($i=1; $i < $searchCount; $i++) {
    
    	
    	if (substr($searchArr[$i], 0, 1) == "+") {
    		echo " AND " . substr($searchArr[$i], 1);
    	}
    	else if (substr($searchArr[$i], 0, 1) == "-") {
    		echo " NOT " . substr($searchArr[$i], 1);
    	}
    	else {
    		echo " OR " . $searchArr[$i];
    	}
    }
    
    
    
    
    $query = mysql_query(); //Assume I know what to do once I get a working query
    
     
  2. cmuench macrumors member

    Joined:
    May 15, 2007
    #2
    Afraid I can't bee too much of a help for you. However whenever I have php or sql questions I post in the PHP forums on http://sitepoint.com
     
  3. notnek macrumors 6502

    notnek

    Joined:
    Oct 25, 2007
    #3
    for one of my sites i setup a pretty simple yet effect search form. i use a title search and then a tags search. both are basic queries:

    Code:
    $query= "SELECT * FROM table WHERE titlerow LIKE '%$search%' OR titlerow LIKE '%$search' OR titlerow LIKE '$search%' OR titlerow = '$title';
    
    and then before returning results, i do a run through the tags.

    Code:
    $query = "SELECT * FROM table WHERE tags LIKE '%$search%' OR tags LIKE '%$search' OR tags LIKE '$search%' OR tags = '$title';
    
    maybe this will help some.
     
  4. Poeben thread starter macrumors 6502

    Joined:
    Jul 29, 2004
    #4
    Thanks for the replies. I figured out the first problem...secret was in the concatenating assignment operator ( .= ) within a for().

    Next task is getting multiple words within quotes to be passed as a single search term (more substr fun.)
     

Share This Page