Invision Power Services > Filter search?

Invision Power Services > Community Forums > Community Web Design and Coding

Kenny Pollock

Apr 22 2006, 03:50 PM

I've never done this one before, although I'm sure someone here has!

I'm trying to create a search page that will filter the database to show what the user wants.

The page I have coded is http://everglademedia.com/clients/directco...rch_orders.html - they check the box next to the field(s) they want to search by.

The problem is, how can I make a query, because there are so many combinations!

A user can select to search by status and origin_state, user can select to search by status and destination_state, user can select to search by status and transporter, user can select to search by status alone, user can select to search by status and pickup_date, and user can select to search by status and delivery_date. All of that is just for status!

What I have so far is:

CODE

    if ( $_POST['searchby_status'] == 'on' )
    {
        $status = " status = '" . $_POST['status'] . "'";
    }

    if ( $_POST['searchby_origin_state'] == 'on' )
    {
        $origin_state = " origin_state = '" . $_POST['origin_state'] . "'";
    }

    if ( $_POST['searchby_destination_state'] == 'on' )
    {
        $destination_state = " destination_state = '" . $_POST['destination_state'] . "'";
    }

    $query = "SELECT * FROM direct_orders WHERE " . $status . "

But I'm not sure how I'd make use of it, because of the different combinations, I would have to know when to use an AND in the query. Any ideas?

Starnox

Apr 22 2006, 04:14 PM

If you are using MyISAM then you can use this function with fulltext searching.

SQL

MATCH(field1, field2, field3) AGAINST('value1* value2*' IN BOOLEAN MODE)

Kenny Pollock

Apr 23 2006, 03:08 AM

I still don't understand how I'd make use of it... like, how would I have the fields in the MATCH()? I don't want to lookup a field if it's not checked... and I don't want to have an if statement for every single combination.

Starnox

Apr 23 2006, 08:58 AM

Ok I am guessing you have different table columns containing all the information for e.g. one for origin state, one for desistantion state, status etc.

Well call all those fields the name of the columns. Then if it's set your put it's name in a $match variable, and then you put it's value in a $conditions variable.

So you have

CODE

MATCH($match) AGAINST($conditions IN BOOLEAN MODE)

This gets rid of the need for any AND and OR, since it searches every column in MATCH() for every condition in AGAINST.

Rikki

Apr 23 2006, 11:58 AM

Build an array of each bit of the query you're going to need to use, then implode it for the query.

CODE

if( $_POST['var1'] )
{
$query[] = "var1 = '" . $_POST['var1'] . "'";
}

if( $_POST['var2'] )
{
$query[] = "var2 = '" . $_POST['var2'] . "'";
}

//...etc.

mysql_query( "SELECT * FROM table WHERE " . implode(" AND ", $query ) );

Kenny Pollock

Apr 23 2006, 02:46 PM

Works great Rikki, thanks a lot! Learned a new function thanks to you... I never really got into arrays, so I didn't know about implode().

Rikki

Apr 23 2006, 02:52 PM

Arrays are a great way of keeping your code tidy. For example, instead of having $first_name, $last_name, $address1 etc. etc. all over your code, I think it's much neater to use $data['firstname'], $data['lastname'] etc. instead. There's lots of useful array functions too, you could then trim all the whitespace by just doing array_walk( $data, 'trim' );

Phil Mossop

Apr 23 2006, 03:02 PM

Are you allowed to use built-in functions with array_walk()?

Rikki

Apr 23 2006, 03:06 PM

Yep

[edit]Sorry, I'm thinking of array_map, which can.

elj

Apr 23 2006, 07:54 PM

QUOTE(Rikki @ Apr 23 2006, 03:52 PM)

That's pretty impressive.

Does that mean you could also do something like?

CODE

array_walk( $_POST, 'trim' );
array_walk( $_POST, 'htmlspecialchars' );

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.