modify what database fields are searched

[erinod]

Hi all,

I'm brand new to php / mysql and I'm trying to modify this script which currently runs a search on two fields "product_number" and "product_name". I also want the search to consider a third field "product_type". When I remove either product_name or product number from the following part of the code, it removes that field from being considered in the search:

 $search_map = array($orderby, 'product_number','product_name',); 

 

So shouldn't I be able to just add the third field's name to this array and have it be considered too? I'm a complete beginner so I'm hoping this is a simple fix and I just dont get it.

 

Here's the rest of what I think is the relevant code:

function load_all_products()
{
    global $AppUI;
    global $sorted_item_list;
    global $additional_pfilter;
    load_type_list();
    $orderby = 'product_id';
    $search_map = array($orderby, 'product_number','product_name',);
    $where = $AppUI->getState( 'QuoteIdxWhere' ) ? $AppUI->getState( 'QuoteIdxWhere' ) : '*';
    $not =" ";
    $op =  " OR";
    $check = substr(trim($where),0,4);
    if(stristr($check,"NOT")){
        $op ="AND";
        $not = " NOT ";
        $where = substr(trim($where),4);
        $where = trim($where);
    }

    // assemble the sql statement
    $q = new DBQuery;
    $q->addTable('products');
    $q->addJoin('companies', 'com', 'products.product_company_id = com.company_id');
    $q->addJoin('users', 'u', 'products.product_owner = u.user_id');
    $q->addQuery('products.*, com.company_name, u.user_username');
    $where_filter = " ";
    foreach($search_map as $search_name)
        $where_filter .=" $op $search_name $not REGEXP '$where'";
    $where_filter = substr($where_filter, 5);
//   echo $where_filter;
    if($where != "*")
       $q->addWhere("($where_filter)");
    $q->addOrder('product_id');

    $sql = $q->prepare();
    $q->clear();
    $sql_list = db_exec( $sql );
    if ($sql_list)
  $rn = db_num_rows( $sql_list );
    else {
   echo db_error();
   $rn = 0;
    }

$product_list = array();
    foreach ($sql_list as $item)
{

	$product_list[ $item[ 'product_id' ] ] = $item;

}

// sort the list

global $sort_state;
if ( !isset( $sort_state ) ) $sort_state = getProductSortState();

if ( isset( $sort_state[ 'sort_item1' ] ) )
{
	if ( isset( $sort_state[ 'sort_item2' ] ) )
	{
		$sorted_item_list = array_csort2( $product_list, $sort_state['sort_item1'], intval( $sort_state['sort_order1'] )
										 , $sort_state[ 'sort_item2' ], intval( $sort_state['sort_order2'] ) );
	}
	else
	{
		$sorted_item_list = array_csort2( $product_list, $sort_state['sort_item1'], intval($sort_state['sort_order1']) );
	}
}
else $sorted_item_list = array_csort2( $product_list, 'product_id' );

}

 

Thanks!

[erinod]

update: I realized that the problem was that I was adding my desired field 'product_type' to the search, but because the product types are set up in mysql to be 1 = product, 2 = service, 3 = custom, when I searched "not custom" the search didnt seem to be working, but when I search "not 3" it actually does work on my added field. Now I just need to figure out how to get custom to equal 3.

[jcbones]

Why you can use a switch()

 

Example

switch($where) {
case 'product':
    $where = 1;
    break;
  case 'service':
    $where = 2;
    break;
  case 'custom':
    $where = 3;
    break;
}

 

Now I'm about 99% sure that will work for you, if you drop it between these two lines:

 

}

// assemble the sql statement

 

But, if for some reason you run into troubles, post back and I'll get it sorted.

 

PS. don't forget to add your field to the array you specified.

[erinod]

Hiii,

Thanks for your help. Adding the switch didn't work, and I don't quite understand how switch statements work yet (reading up on it now), but I feel like the problem is that although the system was able to recognize custom = 3 and respond accordingly when returning search results, it seemed to mess up the search on the other two fields (product_name, product_number). Seems like I need unknown =0, product=1, service=2, custom = 3 for only that one field (product_type) while the other fields stay the same.

[jcbones]

It can be done, but will require extensive re-working of your code.  Give me a bit, and I will try to decipher it further.  Have you thought about adding this in as a filter for the results?

[erinod]

I have had it suggested to me that I do something like this:

if ($exclude_custom)){
                                 $where_filter .= "AND PRODUCT_TYPE != '3'";
                        }

 

And use a checkbox so that when its checked this if statement works and filters out all the product types that equal 3. I like this idea so I'm going to try it out.