Jump to content

multiple filter on recordset-by dropdowns


mojito

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.