Jump to content

Archived

This topic is now archived and is closed to further replies.

mojito

multiple filter on recordset-by dropdowns

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 ;)

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
"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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.