multiple filter on recordset-by dropdowns

[mojito]

Hi guys

Im a bit new to sql but have some basics enough to get me here!

I have a recordset and Its to do with listings, which i want the user to be able to sort by maybe 5 maybe more (and why not more) criteria which get presented at the top of the list in a dropdown box. The user then clicks submit and i need a mega sql statement to sort on multiple field criteria.

If someone could tell me if this is possible would be a great help, otherwise I would have to do a big recordset and then filter one at a time having a new sql statement each time.

I have seen that it might be possible to loop around for each criteria and append to the main query string each loop creating a long statement.

I like the idea of it being extensible too.

thanks

mojito

[craygo]

I assume you will be using php to do this. You way want to move this to the php/mysql help section up top.

If you have a form made up you may want to post that so we can see what you are doing.

Ray

[mojito]

I dont have much built yet, it is in php. I need to know if it is possible first and the best methodology.

I believe its more an mysql question than anything else, i dont need to know how the php will work so much, i want to know what i can do in one foul swoop with mysql.

thanks

[jworisek]

I have something similiar I think... I have a search page to allow users to search orders using any combination of information and order the data in different ways.

What I do is take the data that they want to use in the search and sort it into different arrays.

$table_array[] contains all the tables that are used to join together to get the information for that one item.

$search_array[] describes how items are linked together

$order_array[] describes how to order them.

I then use array_unique on each and implode them into strings and put them into a sql statement:

SELECT * from $table_string where $search string order by $order_string

EX:

[code]
// Customers
$search_array[]="O.customer_id=C.customer_id";
$search_array[]="C.customer_id='$var_array[requiredcustomer]'";
    
$table_array[]=('orders O');
$table_array[]=('customers C');

$order_array[]=('customer_name ASC');

// Shapes
$search_array[]="O.order_id=LI.order_id";
$search_array[]="LI.size_id=X.size_id";
$search_array[]="X.shape_id=S.shape_id";
$search_array[]="S.shape='$var_array[shape]'";
$table_array[]=('orders O');
$table_array[]=('line_info LI');
$table_array[]=('sizes X');
$table_array[]=('shapes S');

//Date sort
$order_array[]=('O.date_in ASC');

$searches=array_unique($search_array);
if(!$searches){
        $search_sql="";
}
else{
        $search_sql=implode(" AND ",$searches);
        $search_sql="WHERE $search_sql ";
}

$tables=array_unique($table_array);
if(count($tables)==0){
        $tables_sql="";
}
else{
        $tables_sql=implode(",",$tables);
}

$order=array_unique($order_array);
if(count($tables)==0){
        $order_sql="";
}
else{
        $order_sql=implode(",",$order);
}
gives me:

$sql="SELECT * FROM Orders O, Customers C, line_info LI, sizes X, shapes S WHERE O.customer_id=C.customer_id AND "C.customer_id='$var_array[requiredcustomer]' AND O.order_id=LI.order_id AND LI.size_id=X.size_id AND X.shape_id=S.shape_id AND S.shape='$var_array[shape]' ORDER BY customer_name ASC, O.date_in ASC";
[/code]

Thats just a small portion of the option I use, I was too lazy to stick them all on here ;)

[mojito]

[!--quoteo(post=361250:date=Apr 3 2006, 04:04 PM:name=jworisek)--][div class=\'quotetop\']QUOTE(jworisek @ Apr 3 2006, 04:04 PM) [snapback]361250[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Thats just a small portion of the option I use, I was too lazy to stick them all on here ;)
[/quote]

not at all thanks for that help, it will be something really like that. It was the sql AND....AND...AND all the time reducing the recordset that was real handy.

Do you think its
good practise to make a temporary table for the initial recordset and then run the sql on that after making it indexable..

I guess I'm just after a best practise method for this stuff.

but thanks again

m

[wickning1]

"Do you think its
good practise to make a temporary table for the initial recordset and then run the sql on that after making it indexable.."

No, just put all the conditions in the WHERE clause and link them with AND. MySQL is good at optimizing the exact method of retrieval.