Edit mysql_query based on advanced search input

[perky416]

Hi everyone me again.

 

Im trying to write an advanced search for a website to sell my domain names.

The problem im having is with the mysql query.

How would i go about editing the query based on say if a keyword is entered, depending on what tld is selected, what length domains is selected etc....

 

I have done a basic keyword search using the following:

 

$query = mysql_query("SELECT * FROM domains WHERE domain LIKE '%" . $search . "%' ORDER BY domain);

 

However it does not account whether additional options have been selected or not.

 

Any ideas?

 

Thanks

[mikosiko]

the basic idea is to construct your WHERE clause with whatever criteria(s) you are capturing in your search page and only then execute it with mysql_query()

[perky416]

Hi mikosiko

 

How would I go about alter the where clause depending on what options have been selected?

 

Thanks

[mikosiko]

assuming that you capture your search criteria/fields in a form the process should be something along this lines (pseudo-code):

- Capture your criteria/fields in a form (apply any validation that you need).. submit the form for processing.

- In your processing script:

 

  
            //start with a NULL where clause           
            $where_sql = NULL;
            // and a base sql
             $base_sql = "SELECT * (better if your specify the columns) FROM <your_table> "; 
              // validate if you have at least one POSTed criteria
             if (// validate if nothing was posted...) {
                          // do here what ever you need
             } else {
                 // you have at least one POSTed criteria
                 $where_sql =" WHERE ";
                 // now  validate and clean any POST value coming from the form and incorporate it to the WHERE clause pe. (very..very simplified):
                 if (isset($_POST['domainlength'])) {
                      $where_sql .= "tblfield_domainlength = {$_POST['domainlength']}"  // adjust the operator that you need and sanitize the POST value
                 }
                 // etc.. etc.. etc


                // validate if the original WHERE was changed
               if ($where_sql != " WHERE ") {
                    $final_sql = $sql . $where_sql;
                    $query = mysql_query($final_sql);
                    //etc.. etc
               }

         }

 

the rest is for you to work on... lot of examples around

           

[Pikachu2000]

Check the incoming form data for the presence of valid values in the criteria fields. If the field has valid data, start building the query's where clause using an array. At the end of that process, if the array isn't empty, implode the values and concatenate the string to the query string. Example follows . . .

 

if( strtolower($_SERVER['REQUEST_METHOD']) == 'post' ) {
$criteria = array(); // initialize an empty array for the criteria
$_POST = array_map('trim', $_POST); // trim() the values in the $_POST array

// Validate the criteria fields
if( !empty($_POST['tld']) ) {
	$criteria[] = "tld = '{$_POST['tld']}'";
}
if( !empty($_POST['expired']) ) {
	$criteria[] = "expired = '{$_POST['expired']}'";
}
if( !empty($_POST['keyword']) ) {
	$criteria[] = "keyword = {$_POST['keyword']}'";
}
$query = "SELECT field1, field2, field3 FROM table"; // start with the basic query string
if( !empty($criteria) ) {
	$query .= "WHERE " . implode( ' AND ', $criteria );
}
}